ORACLE数据库的参数调整
概述
ORACLE数据库参数主要包括磁盘I/O，回滚段、RODO日志、SGA参数，适当调整ORACLE数据库的参数可以提高系统性能，但不恰当的调整也会降低系统的性能。ORACLE数据库的参数，一部分可以通过执行SQL语句或ORACLE的工具DBASTUDIO调整，另外一部分则必须通过修改服务器上ORACLE的安装目录下的文件INIT.ORA来调整，INIT.ORA文件一般放在ORACLE安装目录\admin\ORACLE服务名\pfile下。
优化数据库磁盘I/O
使用分布I/O减少磁盘竞争
-
将数据文件和redo log文件分开
-
减少与ORACLE无关的磁盘I/O
避免动态空间管理
如果数据库操作引起数据增加并超出了分配的表空间，ORACLE会自动扩展表空间，动态扩展会降低系统性能。
确定一段比较长的时期内数据的最大大小；
选择存储参数值，使ORACLE分配足够大的分区，避免频繁自动扩展；
调整Checkpoints
一个checkpoint是ORACLE自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。Checkpoint频繁发生会加快数据库的恢复，但是增加了I/O次数，会降低系统的性能。
修改INIT.ORA文件中的参数LOG_CHECKPOINT_TIMEOUT和LOG_CHECKPOINT_INTERVAL，增大这两个参数会减少I/O次数，提高系统性能。
调整LGWR和DBWn I/O
调整LGWR I/O
每次I/O写的大小依赖于LOG缓冲区的大小，该大小由LOG BUFFER 所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的I/O操作。如果I/O操作的平均大小很大，那么LOG文件就会成为瓶颈，可以使用STRIPE REDO LOG文件避免这个问题。
调整INIT.ORA中的参数LOG_BUFFER以调节LOG缓冲区大小，把REDO LOG文件分为几个文件，放在不同的磁盘上。
调整DBWN I/O
使用初始参数DB_WRITER_PROCESSES，可以创建多个数据库写进程。
调整INIT.ORA中的参数DB_WRITER_PROCESSES。
优化回滚段
检测回滚段争用：
select class,countfrom v$waitstat
where class in('undo header','undo block','system undo header','system undo bolck')
CLASS COUNT
------------------ ----------
system undo header 0
undo header 0
undo block 0
select sum(value)from v$sysstat where name in('consistent gets','db block gets')
SUM(VALUE)
----------
20589
通过以下公式计算等待比率：
system header waits= system undo header / total
reads systemblock waits = system block / total
readsrollback header waits = undo header / total reads
rollback block waits = undo block / total reads
若任何一个的比率大于1％则建议再创一个回滚段：
create rollback segment rbs21 tablespace rbs storage(inittial 10k optimal 20k next 10k maxextents 8) ;
避免动态分配空间
用以下语句检查回滚段的动态分配：
select name,shrinks from v$rollstat,v$rollname wherev$rollstat.usn=v$roll ;
NAME SHRINKS
---------------- ----------
SYSTEM 0
RBS0 100
RBS1 1
若动态分配次数较多可增大回滚段的初始容量。
ALTER ROLLBACK SEGMENT RBS0
STORAGE (inittial20k optimal 40k next 10k maxextents 8) ;
优化Redo日志
检测Redo日志缓冲区锁存：
select name,valuefrom v$sysstat
where name = 'radolog space requests' ;
value值应接近0若较大则应加大INIT.ORA中的LOG_BUFFER项的值。
减少Redo日志转储I/O争用：
将Redo日志分布在不同磁盘中。
优化系统全局区（SGA）
调整操作系统的内存需求
-
减少页的换入换出；
-
将SGA置于主存之中
使用SGA的主要目的是为了在内存中存储数据，以利于快速访问。通过设置初始化参数PRE_PAGE_SGA=YES，在数据库启动时，可以将整个SGA读入内存，这样会减少在启动后ORACLE达到全部性能的总的时间。
使用如下命令可以查看SGA所分配的内存以及其内部的结构：
SVRMGR> SHOW SGA
Total System Global Area 107720688 bytes
Fixed Size 69616bytes
Variable Size 90701824bytes
Database Buffers 16777216 bytes
Redo Buffers 172032bytes
-
为单个用户分配足够的内存
调整redo log缓冲区
参数LOG_BUFFER指定了REDO LOG的缓冲区的保留大小。LOG写进程（LGWR）在该缓冲区被填充时总是运行的，在新的LOG进入缓冲区时，原来的LOG应已经写入磁盘。
调整共享池
数据字典或库快存的没有命中，其开销大大多于缓冲快存的没有命中，因此，首先应该为共享池分配足够的空间。
使用如下语句可以确定库快存和数据字典快存的命中率：
select（sum（pins-reloads））/sum（pins）“Lib Cache”fromv$librarycache ；
select （sum（gets-getmisses-usage-fixed））/sum（gets）“Row Cache”from v$librarycache;
共享池中的自由内存可以查看：
select * from v$sgastat where name=’free memory’;
当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小；
如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。
-
调整Library Cache
-
检查库快存的活动
select sum(pins) “Executions”,sum(reloads)”CacheMisses while Executing” from v$librarycache ;
“Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”Cache Misses while Executing”指明其中没有命中的次数。
-
减少库快存的非命中
-
分配更多的库快存（可以增加初始化参数shared pool size的值；为了利用增加的共享sql区，增加初始化参数open cursors的值）；
-
尽可能使用标准的sql语句（sql语句或pl/sql块必须一致，包括字符和空格；sql语句或pl/sql块中对schema实体的引用必须解析到同一schema的同一对象；sql语句中试用的变量的名字和数据类型必须匹配；sql语句必须使用相同的优化方法和优化目标）；
尽可能使用标准的sql语句，策略：
-
语句中尽量使用变量而不要使用常量
-
确保应用用户不会改变优化方法和目标
-
标准化的变量命名和空格转换
-
尽可能使用存储过程
-
使用CURSOR_SPACE_FOR_TIME加速共享sql区的访问：该参数指定是否共享sql区可以释放，如果为false（默认值），一个共享sql区就可以被释放；如果为true，一个共享的sql区只存在所有与其相关的游标关闭后才可以被释放。
如果库快存在语句执行时有非命中，不要将其设置为true，否则对新的sql语句将没有空间。
-
调整Data Dictionary Cache
-
监视数据字典快存的活动
select sum(gets) “DataDictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;
-
减少数据字典快存的非命中
对频繁访问的数据字典快存没命中和命中比应少于10％－15％。
要增加数据字典快存可用的内存数，需要增加初始参数SHARED_POOL_SIZE的值。
-
调整多线程服务器的共享池
查询动态表v$sesstat可以确定会话信息的大小：
select sum(value)||‘bytes’”Total memory for all sessions” fromv$sesstat, v$statname where name=’session uga memort’ and v$sesstatstatistic#=v$statname.statistic#;
显示信息如下：
Total memory for all sessions
--------------------------------------
157125 bytes
结果指明当前分配给所有会话的内存。
-
调整共享池的保留空间
SHARED_POOL_RESERVED_SIZE为大的存储保留的SHARED_POOL_SIZE总量；
SHARED_POOL_RESERVED_MIN_ALLOC－控制保留内存的分配；
要创建一个保留列表，SHARED_POOL_RESERVED_SIZE必须大于SHARED_POOL_RESERVED_MIN_ALLOC。
优化数据库缓冲区高速缓存:
select name,valuefrom v$sysstat where name in ('db block gets','consistent gets','physicalreads') ;
NAME VALUE
------------------------- ---------
db block gets 3700
consistent gets 17603
physical reads 523
计算公式：
Hit Ratio =1-(physical reads / ( db block gets + consisten gets))
若命中率低可以加大INIT.ORA中DB_BLOCK_BUFFERS的值。
DB_BLOCK_BUFFERS的单位是块，每块大小由DB_BLOCK_SIZE确定，DB_BLOCK_SIZE的单位是字节，高速缓冲区的大小=DB_BLOCK_BUFFERS×DB_BLOCK_SIZE；
调整多缓冲池
-
多缓冲池特性概述
可以使用keep缓冲池来维护缓存中的实体，使用recycle缓冲池来防止实体占用缓存中不必要的空间；
可以将大的数据段置于独立的recycle缓存中，将小的数据段置于独立的keep缓存中。
-
何时使用多缓冲池
-
查找ORACLE内部实体数据段的数目：
select data_object_id,object_type from user_objectswhere object_name=’<segment_name>’ ;
-
查找对应该段名的缓存中的缓冲区数：
select count(*) buffers from x$bh whereobj=<data_object_id> ;
-
查找实例中的缓冲区总数：
select value “total buffers ” from v$parameter wherename=’db_block_buffers’ ;
-
计算当前有指定段使用的缓存所占的比率:
%cache used by segment_name=buffers(step 2)/buffers(step3);
-
使用多缓冲池调节缓存
减少I/O操作；
隔离缓存中的实体；
限制实体在缓存中的一个部分
-
使多缓冲池生效
定义新缓冲池：使用初始参数BUFFER_POOL_NAME来定义缓冲池，每个缓冲池可以指定两个属性：缓冲池中的缓冲区数；分配该缓冲池的LRU latches数。
用于定义缓冲池的初始参数有：
BUFFER_POOL_KEEP－定义keep缓冲池
BUFFER_POOL_RECYCLE－定义recycle缓冲池
DB_BLOCK_BUFFERS－定义数据库实例的缓冲区数
DB_BLOCK_LRU_LATCHES－定义数据库实例的LRU latches数
ORACLE8定义了三个缓冲池：keep，recycle和default。其中default缓冲池是已经存在的。
-
使用多缓冲池
为一个实体建立一个默认缓冲池，语法为：
BUFFER_POOL{KEEP | RECYCLE | DEFAULT}
-
如何为各缓冲池设置大小
-
KEEP缓冲池
使用keep缓冲池的目的是将实体保留在内存，避免I/O操作
select physical_reads,block_gets,consistent_gets fromv$buffer_pool statistics where name=’KEEP’;
计算缓冲池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)
-
Recycle缓冲池
使用recycle缓冲池的目的是为了清除内存中的不再使用的数据块，如果“freebuffer waits”统计数总是很高，可以确定recycle缓冲池过小：
确定recycle缓冲池大小的办法：使recycle缓冲池失效运行系通到稳定状态，查看default缓冲池中由本来需要置于recycle缓冲池中数据段的缓冲区数，除以4，该结果就可以用作recycle缓冲池的大小。
-
确定数据段置于keep还是recycle缓冲池
对于至少两倍DEFAULT缓冲池大小，并且占用系统整个I/O的一定百分比的数据段，那么最好放置于recycle缓冲池；
对于大小小于DEFAULT缓冲池的10％，并且占用至少系统整个I/O的1％的数据段，那么最好放置于keep缓冲池；
如果对表空间超过一个段时，可以通过查询V$SESSION_WAIT确定每个段的I/O操作。
-
如何识别和减少LRU Latch竞争
LRU latches规划了那些缓存中最近最少使用的缓冲区列表，使用DB_BLOCK_LRU_LATCHES参数可以设置数据库实例中的latches总数。
可以通过如下语句确定系统中是否有latch竞争：
select child#,sleeps/gets ratio from v$latch_childrenwhere name=’cache buffers lru chain’ ;
每个LRU latch的非命中率应少于1％，任何大于1％的latch说明存在竞争，通过如下语句查找出来：
select name from v$buffer_pool_statistics wherelo_setid<=child_latch_number and hi_setid>=child_latch_numbers ;
通过增加系统的LATCHES总数和相关缓冲池的LATCHES数可以减少LRU latch竞争。所允许的LATCHES最大值应少于：number_of_cpus*2*3和number_of_buffers/50
调整排序区
使用动态表V$SYSSTAT的信息反映排序
SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (‘SORTS(MEMORY)’,’SORTS(DISK)’) ;
SORTS(MEMORY)－不需要使用I/O操作而完全在内存完成的排序数；
SORTS(DISK)－需要使用I/O操作与磁盘临时段才能完成数据的排序数目。
增大SORT_AREA_SIZE以避免磁盘排序
修改INIT.ORA文件中SORT_AREA_SIZE和SORT_AREA_RETAINED_SIZE的值，单位字节
重新分配内存
在重新定义了ORACLE内存结构、调整了库快存、数据字典快存和快速缓存后，如果减少了这些结构中某一个结构的内存使用，就可能需要将内存分配到其他结构中；
在改变了ORACLE内存结构以后，ORACLE的内存需求也会改变。
减少整个内存使用
采用如下方法：
增加可用内存总数；
减少内存使用
优化数据字典高速缓存：
selectsum(gets)"Read Requests",sum(getmisses)"Reads not inMemory" from v$rowcache ;
Read Requests Reads not in Memory
------------- -------------------
4764 145
命中率＝ReadRequests/ Reads not in Memory
若低于85％ 应增加 SHARED_POOL_SIZE
优化游标：
可用以下语句检测游标的命中率：
select * from v$session_cursor_cache ;
select * from v$system_cursor_cache ;
优化游标应综合考虑，若打开的游标过多则应用程序需要的内存增大，可能发生共享池内存被换出到虚存从而影响性能。以下三个INIT.ORA中的参数可影响游标的性能：
CLOSED_CACHED_OPEN_CURSORS 指示ORACLE当执行commit或rollback语句时，是否显式的关闭游标。如果游标需要反复使用可以设为false，否则应为true从而减少内存占用。
CURSOR_SPACE_FOR_TIME若为true指示ORACLE只要在sql共享区中有打开的游标就一直保存该区域，若其值为false ORACLE可将其换出内存，即使游标仍打开。只有共享池能保存所有打开的游标时才将其设为true。
OPEN CURSORS设定一个会话可同时打开的游标数。
注意：以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低ORACLE性能。
1673
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
