Oracle 数据库性能调整

qinren

Oracle 数据库性能调整
默认的init<SID>.ora是由系统自动安装在$ORACLE_BASE/admin/<SID>/pfile下的默认初始参数：
        共享池（Shared Pool Size(Bytes):共享池包括库高速缓存、数据字典高速缓存和服务器控制结构。必须将这个值设得足够大，以确保有足够的可用空间来装载和存储PL/SQL块和SQL语句。（根据数据库服务器的内存大小来设定此值，也可接受却省值）。越使用处存储过程和触发器，共享池要求更多。
查询库高速缓存遗漏率
select (sum(pins-reloads))/sum (pins)  “lib Cache “ from V$librarycache;
查询数据字典遗漏率(row cache行高速缓存)
select (sum(gets-getmisses-usage-fixed))/sum(gets) “Row Cache” from v$rowcache;
当比例接近1 ，不增加池大小。（pins—执行次数；reloads－遗漏数量；）
报告空闲的内存数量：
Select * from  v$sgastat where name=’free memory’;
Pl/sql DBMS_SHARE_POOL（dbmspool.sql）管理共享内存。
可修改InitSID.ora文件中的参数SHARED_POOL_SIZE 来设定此值。增加这个值，减少库缓存的遗漏率（miss rate）。
分配过多，会导致分页和交换，会较多地将共享SQL区的数据从磁盘读入内存，增加Shared Pool Size,要相应增加OPEN_CUSORS，以利用共享SQL区的额外内存。

繁忙系统设置保留共享池中的内存，确保有大块连续的内存空间
SHARED_POOL_RESERVED_SIZE
一般是SHARED_POOL _SIZE的10%
通过v$SHARED_POOL_RESERVED 统计，使REQUEST_MISSES＝0 ；REQUEST_FAILURES＝0或停止增长。

        数据库缓冲区高速缓存(Block Buffers &  Block Size(Bytes):
当缓冲区高速缓存命中率:
select name ,value from v$sysstat where name in(‘db block gets’,’consistent gets’,’physical reads’);
然后计算：
ratio=1- (physical reads/(db block gets+consistent gets));
physical reads -------存取磁盘文件的数据请求；
db block gets+consistent gets――――数据请求的总和

低于60％－70％，增加db_block_buffers的值；
db_block_buffers = 8192；
db_block_size = 8192
DB_BLOCK_SIZE & DB_BLOCK_BUFFERS 是决定缓冲区高速缓存大小的俩个初始化参数，DB_BLOCK_SIZE参数用于在数据库创建时，设置Oracle块大小。DB_BLOCK_BUFFERS参数决定分配给缓冲区高速缓存的块的数量。
用DB_BLOCK_SIZE乘以DB_BLOCK_BUFFERS就得出缓冲区高速缓存的内存总数。（根据数据库服务器的内存大小来设定此值，也可接受却省值）.
块大小选择8K，可以在一个I/O中将许多行放入缓冲区高速缓存。但对少量行做随机存取会浪费空间。
        重做日志缓冲区（Log Buffer Size(Bytes)）:用于在内存中存储未被刷新写入联机重做日志文件的重做信息。LOG_BUFFER初始化参数决定大小。此值不能过低，（根据数据库服务器的内存大小来设定此值，也可接受却省值）大小比SGA小，适当地增加可以提高吞吐量。
空间需求比例＝重做日志空间需求/重做条目 ＝1/5000;当比例大于此时，增加log buffer。
        large  pool ： oracle拥有的一个独立的池，从而可以请求大的内存分配，防止与其他系统竞争相同的内存。
       查看某对象占据内存的那个池。 Select  * from v$sgastat;
        进程数（Processes）：能与Oracle数据库连接的操作系统进程的最大数目，其中包括Oracle 的后台进程。（根据数据库服务器的内存大小以及可能的应用的用户连接数量来设定此值，也可接受却省值）(修改为大于300)（在AIX中运行chdev -l sys0 检查Maximum number of PROCESSES allowed per user）
        open_cursors：要想利用SQL区的额外可用内存，需增加打开游标数，可建立更多的私有SQL区。
        跟踪调试文件设置（Trace File）接受却省值)
重启机器后，ORACLE会自动使用该日期格式。
        oracle I/O优化调整：
在RAW设备上，读写在字符级操作，在块设备上，操作在块级。对于8k块大小的文件系，在原始（RAW）设备上连续写操作的用时少，速度快，读速度慢；而在unix文件系统上连续写操作的用时多，速度慢，读速度快近两倍。
        解决I/O磁盘竞争（disk contention）
分开数据库文件和重做日志文件；分开表和索引；包含数据库文件的磁盘上和oracle数据库不相关的i/o尽量消除。
经常使用的表和索引不必分开，因为I/O是连续发生的。
        建立足够大的回滚段，减少动态的扩展回滚段。建立许多回滚段，分配每个事务到它自己的回滚段。
        调整检查点 LOG_CHECKPOINT_INTERVAL和LOG_CHECKPOINT_TIMEOUT:
LOG_CHECKPOINT_INTERVAL――― 大于最大重做日志文件的大小，是物理块大小的倍数。
LOG_CHECKPOINT_TIMEOUT:――为0，消除基于时间的检查点。
增加重做日志的大小，一边不被快速地填满。
        解决竞争问题
1．        检测竞争
select * from v$resouce_limit;
提供关于当前合最大全局资源利用的信息；
select * from v$system_event
检查有最高平均等待时间的事件。
V$waitstat ―――查看哪个块类型有最高等待计数和最高等待时间。
Select class,count from v$waitstat where class in(‘system undo header’,’system undo block’,’undo header’,’undo block’);
2．        减少回滚段的竞争
检测动态性能表v$waitstat确定回滚段的竞争是否降低性能；
以下的统计反映不同类型的块的竞争：
        SYSTEM UNDO HEADER：对包含SYSTEM回滚段标题块的缓冲区的等待数量；
        SYSTEM UNDO BLOCK ：对包含SYSTEM回滚段（除标题块）的缓冲区的等待数量；
        Undo header: 包含回滚段（出system回滚段）标题块的缓冲区的等待数量。
        Undo block：包含回滚段（出system回滚段）块（除标题块）的缓冲区的等待数量。
        查询监控数据请求的总数：
select sum(value)  from v$sysstat where name in(‘dbblock gets’,’consistent gets’);
任何类的等待数量大于总数的1％，这考虑建立更多的回滚段。
        通常建立4个以上的回滚段（并发事务÷4）
3. 减少多进程服务器的进程竞争：
        减少调度程序竞争
select network “protocol”,sum(busy)/(sum(busy)+sum(idle)) “totobusy rate” from v$dispatcher group by network;
idle-----------空闲时间（1/100秒）
busy―――调度程序进程的忙时间（1/100秒）
v$queue ―――反映相应队列活动时间
        重做日志缓冲区的竞争：
select name ,Value from v$sysstat where name = ‘redo buffer allocation retries’;
该值应该接近于0，若该值稳定增长，则进程不得不等待缓冲区中的空间。该等待可能是因为日志空间缓冲区太小，或检查点、日志转换。修改LOG_BUFFER来增加日志缓冲区的大小。（必须是DB_BLOCK_SIZE 的倍数n×8192），改善检查点（调整LOG_CHECKPOINT_INTERVAL（到大于最大重做日志文件的大小）和LOG_CHECKPOINT_TIMEOUT）和存档进程。
        多CPU情况下有闩锁竞争；LOG_SIMULTANEOUT_COPIES（CPU数）参数决定数量。单CUP没有这种情况。
select ln.name,gets,misses,immediate_gets,immediate_misses from V$latch l, v$latchname ln where  ln.name in(‘redo allocation’,’redo copy’)and ln.latch#=l.latch#;
当isses 与GETS的比大于1％ 或immediate_misses与immediate_gets和immediate_misses之和的率大于1％，闩锁的竞争将影响性能。
降低LOG_SMALL_ENTRY_MAX_SIZE减少redo分配闩锁上重做日志的条目数量和大小，以减少单个进程保存闩锁的时间。
        减少空闲列表的竞争：
查询V$WAITSTAT，确定空闲列表竞争是否降低性能。
查找有竞争的段和列表
检查数据块―――v$waitstat；
缓冲区忙等待―――v$system_event；数量高说明存在竞争；
缓冲区忙等待―――v$session_wait;
        select segment_name,freelists from dba_segments where segment_name=’segment ‘  and segment_type = ‘type’
4.系统参数汇总：
1）        资源限制：（红色为建议值）
        LARGE_POOL_SIZE：为会话使用的大池分配的堆栈大小；
        SHARE_POOL_SIZE：共享池的大小（3×消息缓冲区大小）×（CPU数＋2）×parallel_max_servers）
2) 资源消耗：
        HASH_AREA_SIZE 大于1M，大的数据仓库，8MB—32MB
256kb ---4Mb 指定排序操作的每个查询服务器进程分配的内存数；
3) 影响并行DML、DDL资源
        rollback_segments 回滚段应该属于有空闲空间的表空间，无限制；Rollback_segments 为实例分配一个或多个回滚段。缺省使用公共的回滚段。
        log_buffer 检查V$SYSSTAT 中”redo buffer allcation reties”统计。select name ,Value from v$sysstat where name = ‘redo buffer allocation retries’; 如果值很大，增加LOG_BUFF
3－5M;
4）I/O相关
        DB_BLOCK_SIZE: 8K 或16K数据库块的大小（建库时指定）；
        DB_BLOCK_BUFFER ：设置缓冲区高速缓存中可用的数据库缓冲区数量；
        DB_FILE_MULTIBLOCK_READ_COUNT：对DB_BLOCK_SIZE为8K为8；16K为4决定单个操作系统读取多少数据库块。
        HASH_MUTIBLOCK_IO_COUNT :4 ;一个HASH连接一次读取多少块；
        DISK_ASYNCH_IO和TAPE_ASYNCH_IO ： ture；是否允许异步操作；
5）其他参数
        max_rollback_segments 30 ――指定SGA中回滚段高速缓存的大小；