Oracle数据库参数优化

1、Oracle数据库参数优化许多开发维护人员并没有真正理解Oracle数据库相关参数在系统运行中的作用，导致数据库系统的执行效率低下。因此，如何设计合理的Oracle数据库初始化参数就显得非常重要。为了访问数据库中的数据，Oracle数据库为所有用户提供一组后台进程，并且，有一些存储结构专门用来存储最近的有关对数据库访问的数据。这些存储区域可以通过减少对数据库文件的I/O次数来改善数据库性能。数据库实例就是用来访问一个数据库文件集的一个存储结构以及后台进程的集合。它使一个单独的数据库可以被多个实例访问（这是Oracle的并行服务器方式 ）。决定实例的组成以及大小的参数存储在文件init.ora中

2、。这个文件在实例启动时候需要装载，也可以在运行中被DBA装载 。instance的init.ora文件通常包含instance 的名字，比如一个instance叫ORA9，其init.ora将被起名为initORA9.ora。本文结合笔者实际工作中的Oracle数据库使用经验讨论其相关参数的调整策略。通常需要设置的参数： DB_BLOCK_BUFFERS该参数决定了数据库缓冲区的大小，这部分内存的作用主要是在内存中缓存从数据库中读取的数据块，数据库缓冲区越大，为用户已经在内存里的共享数据提供的内存就越大，这样可以减少所需要的磁盘物理读写次数。在Oracle 9i以前版本数据库中，缓冲是由db_

3、block_buffers*db_block_size来决定，db_block_size参数是在创建数据库时设置的，它决定数据库里每个块的大小，9i以前不能改变已经存在的数据库的块大小，因此应该在最初创建数据库时就确保设置适当的数据库块大小。在创建数据库时设置为8k，db_block_buffers*db_block_size的乘积占物理内存的20%30%，物理内存在1G以内，可以占20%，1G2G之间可占25%，物理内存在2G以上，可以占30%，甚至更高。说明：Oracle 8i以后的数据缓冲区(data buffer)实际可由三部分组成，分别是默认池、保留池(buffer keep)和回收

4、池(buffer recycle)。保留池(buffer keep)可用于存放需要极快访问的小表，回收池(buffer recycle)可用于存放需要全表扫描的大表。保留池和回收池的大小分别由参数buffer_pool_keep和buffer_pool_recycle决定。这两个参数通常在做性能调整时才设置，因此不建议设置这两个参数。 shared_pool_sizeShared pool由三部分组成，分别是Dictionary cache(包括数据字典的定义，如表结构、权限等)、Library cache（包括共享的sql游标、sql原代码以及执行计划、存储过程和会话信息）和Control

5、structure。它的大小由初始化参数shared_pool_size控制，它的作用是缓存已经被解析过的SQL，使其能被重用，不用再解析。SQL的解析非常消耗CPU的资源，如果一条SQL在Shared pool中已经存在，则进行的仅是软解析（在Shared pool中寻找相同SQL），这将大大提高数据库的运行效率。当然，这部分内存也并非越大越好，太大的Shared pool，Oracle数据库为了维护共享结构，将付出更大的管理开销。这个参数的设置建议在150M500M之间。如果系统内存为1G，该值可设为150M200M;如果为2G，该值设为250M300M；每增加1G内存，该值增加100M；

6、但该值最大不应超过500M。（Shared pool不足，Oracle将报4031错）。 shared_pool_reserved_size它的作用是在shared pool中保留一块区域以放置一些大对象（如大型软件包），如不设置此参数，系统缺省保留5%的shared pool空间用于放置大对象，不建议设置该参数，让系统自动分配即可。 Sort_area_size该参数是当查询需要排序的时候，Oracle将使用这部分内存做排序，当内存不足时，使用临时表空间做排序。这个参数是针对会话(session)设置的，不是针对整个数据库。即如果应用有170个数据库连接（session），假设这些sessi

7、on都做排序操作，则Oracle会分配8×170等于1360M内存做排序，而这些内存是在Oracle的SGA区之外分配的，即如果SGA区分配了1.6G内存，Oracle还需要额外的1.3G内存做排序。建议该值设置不超过3M，当物理内存为1G时，该值宜设为1M或更低（如512K）；2G时可设为2M;但不论物理内存多大，该值也不应超过3M。 sort_area_retained_size这个参数的含义是当排序完成后至少为session继续保留的排序内存的最小值，该值最大可设为等于Sort_area_size。这样设置的好处是可以提高系统性能，因为下次再做排序操作时不需要再临时申请内存，缺

8、点是如果Sort_ara_retained_size设得过大并且session数很多时，将导致系统内存不足。建议该值设为Sort_area_size的10%20%左右，或者不设置（缺省为0）。 Log_bufferLog_buffer是重做日志缓冲区，对数据库的任何修改都按顺序被记录在该缓冲，然后由LGWR进程将它写入磁盘。LGWR的写入条件是：用户提交、有1/3重做日志缓冲区未被写入磁盘、有大于1M重做日志缓冲区未被写入磁盘、超时、DBWR需要写入的数据的SCN 号大于LGWR记录的SCN 号，DBWR 触发LGWR写入。从中可以看出，大于1M的log buffer值意义并不大，建议不论物理

9、内存多大，该值统一设为1M。 Large_pool_sizeOracle的large_pool用于MTS、并行查询和RMAN。如果使用了MTS或RMAN，large_pool特别有用，它可以降低用户对shared_pool的争用，这个参数不推荐设置。（或设为1M）。 Java_pool_sizeOracle 8i以后版本中，Oracle数据库内置了对Java的支持，短消息系统并未使用这些功能，因此java_pool_size的值可以减少到1M。但如果数据库安装时选择了JServer组件，则这个值可以设置为20M30M之间。（可以查询v$option动态视图，如果java那一项值为true，则表

10、示安装了JServer，为false，表示未安装）。 SESSION_CACHED_CURSOR该参数指定要高速缓存的会话游标的数量。对同一SQL语句进行多次语法分析后，它的会话游标将被移到该会话的游标高速缓存中。这样可以缩短语法分析的时间，因为游标被高速缓存，无需被重新打开。设置该参数有助于提高系统的运行效率，建议无论在任何平台都应被设为50。 re_page_sga该参数表示将把所有SGA装载到内存中，以便使该实例迅速达到最佳性能状态。这将增加例程启动和用户登录的时间，但在内存充足的系统上能减少缺页故障的出现。建议在2G以上（含2G）内存的系统都将该值设为true。 ML_LOCKS该参数

11、表示所有用户获取的表锁的最大数量。对每个表执行 DML 操作均需要一个 DML锁。例如，如果3个用户修改 2 个表，就要求该值为 6。该值过小可能会引起死锁问题。这个参数同transaction参数有关系，缺省为4倍的transaction大小。建议该参数不应该低于600，可以检查系统的当前值，若比600小，则将transaction参数改为150以上，dml_locks参数会自动变为transaction的4倍。 OB_QUEUE_PROCESS该参数指定每个例程的SNP作业队列进程的数量。当使用job或复制时，一定要设置该参数，推荐设为3或4。并行查询中需要设置的参数并行查询仅当在多CPU

12、处于空闲状态且数据分布在不同磁盘时才会对某些查询（有全表扫描操作）的性能产生有益的影响，而且即使在这种情况下，并行查询服务器的最大数量最好也不要超过CPU的数量。这里面应该修改的参数如下。 DB_FILE_MULTIBLOCK_READ_COUNT该参数主要同全表扫描有关。当Oracle在请求大量连续数据块的时候，该参数控制块的读入速率。DB_FILE_MULTIBLOCK_READ_COUNT参数能对系统性能产生较大的影响，它和DB_BLOCK_SIZE参数之间有重要关系。因为在Unix物理层上，Oracle总是以最小64K的数据库块进行读入，因此应该使这2个参数的乘积为64K。即如果DB_

13、BLOCK_SIZE为8192，则DB_FILE_MULTIBLOCK_READ_COUNT应设为8。 DB_BLOCK_LRU_LATCHES在多CPU机器上通过初始化参数DB_BLOCK_LRU_LATCHES允许多个LRU锁存器。当DBWR和服务器进程扫描数据块缓冲Cache时，它们需要获取LRU锁存器。这种锁存器对于避免缓冲区变脏以及避免被其他进程改变都是必要的，这也避免了扫描时返回不一致的结果。如果没有使用锁存器，某个扫描自由缓冲区的进程可能会发现一个自由缓冲区，但是立刻就可能被其他进程使用。每一个LRU 锁存器至少保护50个数据块缓冲区。所有缓冲区都被Oracle基于Hash算法分

14、配到特定的LRU锁存器之上。如果该值太小，在数据库活动量很大时就会潜在地导致竞争。在多CPU机器之上这种竞争会高一些，这是因为多个服务器进程可能排队以获取一个LRU Cache。该参数默认值为CPU个数的一半，在多CPU系统中，推荐设为等于CPU数目或CPU数目的2倍3倍（在Oracle 9i中，该参数已经变为一个隐含参数）。 OPEN_CURSORS指定一个会话一次可以打开的游标 (环境区域) 的最大数量，并且限制PL/SQL使用的PL/SQL游标高速缓存的大小，以避免用户再次执行语句时重新进行语法分析。请将该值设置得足够高，这样才能防止应用程序耗尽打开的游标。此值建议设置为250300。

15、LOG_CHECKPOINT_INTERVAL该参数同检查点有关，检查点由ckpt 进程执行，检查点发生时Oracle会同步数据文件、控制文件和redo文件。该参数指定当写入重做日志文件中的OS 块 (而不是数据库块) 的数量达到设定值时，强制执行一次检查点。该值较低可以缩短例程恢复所需的时间，但可能导致磁盘操作过量。在Oracle 8i中该值缺省为100000。当值为0时，表示此参数不起作用。该参数的设定需要考虑的因素较多，建议使用缺省值。 LOG_CHECKPOINT_TIMEOUT该参数仍然同检查点有关。它指定距下一个检查点出现的最大时间间隔(秒数)。将该时间值指定为0，将禁用以时间为基

16、础的检查点。较低的值可以缩短例程恢复的时间，但可能导致磁盘操作过量。在8i中该值缺省为1800。如果想强制某一时间段后执行检查点，则用此选项。不建议修改该值。Oracle 8i OPS的特殊参数设置OPS是指Oracle并行服务器（Oracle Parallel Server），它允许多个实例在同一时刻访问单一的数据库。OPS增强了数据库的可用性，单个节点的故障并不会使整个数据库不可用。而且如果应用分割合理，OPS也可以提高系统的处理性能。但由于OPS本身的复杂性，如果对并行操作的使用不当，经常会出现系统操作性能比单节点还慢的结果。在OPS中，Oracle通过集成分布式锁管理器（Integra

17、ted Distributed Lock Manager，IDLM）来协调资源的使用，防止发生冲突。一旦某个节点上的实例启动后，它就可以获取各种各样的DLM锁，这些锁主要划分为PCM（Parallel Cache Management，并行Cache管理）锁和非PCM锁。并行Cache管理是一种技术，这种技术允许多个实例同时访问共享资源。它允许一个节点访问位于另一个节点的缓冲区Cache中的数据。PCM锁用于锁住数据库块，非PCM锁用于锁住所有其他的共享DLM资源（例如共享池中的数据文件和对象）。Oracle 9i中主要参数设置 undo_managementOracle 9i中新引入了und

18、o tablespace，它可以自动管理Oracle的回滚，不再需要手工建立回滚段，但9i也支持继续使用手工建立的回滚段。由Undo_management参数决定是使用undo tablespace还是使用手工建立的回滚段。如果该参数为auto，则表示使用undo tablespace。为manual，表示使用手工管理回滚段，这时同8i一样，也需要建立回滚表空间，且要建立回滚段。在9i R2（9i R2指Oracle 9.2.X.X，下同）中，该参数缺省值为auto。建议继续使用该缺省值，即使用undo tablespace，也不用手工建立回滚段。 undo_retention该参数的单位为秒

19、，当undo_management参数为auto时，undo_retention表示在undo_tablespace中保留多长时间的回滚信息，Oracle 9i的flash_back功能同该值有关。该值设为多大同undo_tablespace的大小有关，在9i R2中缺省值为10800，一般情况下已经够了，不建议修改该参数。 sga_max_sizeOracle 9i中该值决定了SGA区的最大值。只要SGA区不大于该值，就可以动态调整数据库缓冲区和share pool的大小。建议该值取物理内存的50%。 db_cache_size在Oracle 9i中，数据库缓冲区的大小由db_cache_size决定，Oracle 8i中的db_block_buffers被取消。db_cache_size的单位是字节，它直接决定了数据库缓冲区的大小，而不再是块的数量。