名家集锦之--oracle 系统参数优化

这篇文章摘自玄机逸士为某上市公司ERP系统调优项目总结报告，希望对大家有所帮助。

在32位的操作系统上，Oracle系统理论上可以使用的内存上限是4G，即2^32字节，任何多出的部分，Oracle均不会使用；在64位的操作系统上，Oracle系统可以使用的内存理论上是2^64字节，在可以预见的未来的应用，这几乎和没有上限限制是一样的。因此在购买小型机服务器硬件设备时（一般都会自带操作系统）要考虑到使用的是多少位的操作系统，否则即使内存配得再多，也不会给性能带来提高。在使用32位的操作系统时，如果该服务器设备专门用作数据库服务器（即不在其上安装其它诸如应用服务器等软件）时，配置6G的内存应该是比较恰当的，除4G被Oracle使用外，2G内存用来运行操作系统本身的进程。

 注意1：如果是UNIX或Linux32位操作系统，还要设置操作系统级的参数shmmax。SGA的空间可以大于shmmax，具体情况请参考Oracle安装手册以及操作系统手册;

 注意2: 本小节（下面）中所列的操作可能需要一定的权限，在实际工作中，如果发现相关权限的问题，请及时给相关用户授权或以一个权限更大的用户的身份进行操作。

       Oracle总体内存结构如下图所示：

 从上图中，Oracle的内存结构主要有SGA和PGA组成，其中UGA的一部分为SGA所管理，而另一部分则为PGA所管理，CGA则不关UGA被SGA管理还是被PGA所管理，它总是处于UGA之内。

 

或许由于SGA太重要了，以致大家忽略了PGA、UGA和CGA的存在。比如很多人都搞不清楚PGA和UGA两者之间的区别，实际上两者之间的区别跟一个进程和一个会话之间的区别是类似的。尽管说进程和会话之间一般都是一对一的关系，但实际上比这个更复杂。一个很明显的情况是MTS配置，会话往往会比进程多得多。在这种配置下，每一个进程会有一个PGA，每一个会话会有一个UGA。PGA所包含的信息跟会话是无任何关联的，而UGA包含的信息是以特定的会话为基础的。现在我们对PGA、UGA和CGA做以下介绍：

 

进程全局区(PGA)即可以理解为Process Global Area，也可以理解为Program Global Area。它的内存段是在进程私有区(Process Private Memory)而不是在共享区(Shared Memory)。它是个全局区意味着它包含了所有代码有可能进入的全局变量和数据结构，但是它是不被所有进程共享的。每个Oracle的服务器进程都包含有属于自己的PGA，它只包含了本进程的相关特定信息。PGA中的结构不需要由latches来保护，因为其它的进程是不能进入到这里面来访问的。PGA包含的是有关进程正在使用的操作系统资源信息以及进程的状态信息，而其它的进程所使用的Oracle的共享资源是在SGA中。PGA是私有的而不是共享的，这个机制是有必要的，因为当进程死掉后可以把这些资源清除和释放掉。PGA包含两个主要区域：Fixed PGA和Variable PGA或称为PGA Heap。Fixed PGA的作用跟Fixed SGA是类似的，都包含原子变量(不可分的)，小的数据结构和指向Variable PGA的指针。Variable PGA是一个堆。它的Chunks可以从Fixed Table X$KSMPP查看得到，这个表的结构跟前面有提到的X$KSMSP是相同的。PGA HEAP包含了一些有关Fixed Table的永久性内存，它跟某些参数的设置有依赖关系。这些参数包含DB_FILES，LOG_FILES，CONTROL_FILES。

 

UGA(User Global Area)包含的是特定会话的信息，有如下一些:

       － 所打开游标的持续和运行时间内的区域

       － 包的状态信息，特定的变量

       － Java会话状态

       － 可以用的ROLES

       － 被ENABLE的跟踪事件

       － 起作用的NLS参数设置

       － 打开的DBLINK

       － 会话的入口控制

 跟PGA一样，UGA也由两区组成：Fixed UGA和Variable UGA，也称为UGA HEAP。Fixed UGA包含了大约70个原子变量，小的数据结构和指向Variable UGA的指针。UGA HEAP中的Chunks可以从它们自己的会话中通过查看表X$KSMUP获得相关信息，这个表的结构跟X$KSMSP是一样的。UGA HEAP包含了一些有关fixed tables的永久性内存段，跟一些参数的设置有依赖关系。这些参数有OPEN_CURSORS，OPEN_LINKS和MAX_ENABLE_ROLES。

 

调用全局区(CGA)跟其它的全局区不同，Call Global Area是短暂性存在的。它只有在调用数据期间存在，一般是在对实例的最低级别的调用时才需要CGA，如下：

       － 分析一个SQL语句

       － 执行一个SQL语句

       － 取出一个SELECT语句的输出

一个单独的CGA在递归调用时是需要的。在SQL语句的分析过程中，对数据字典信息的递归调用是需要的，因为要对SQL语句进行语法分析，还有在语句的优化期间要计算执行计划。执行PL/SQL块时在处理SQL语句的执行时也是需要递归调用的，在DML语句的执行时要处理触发器执行也是需要递归调用的。不管UGA是放在PGA中还是在SGA中，CGA都是PGA的一个子堆(Subheap)。这个事实的一个重要推论是在一个调用的期间会话必须是一个进程。对于在一个MTS的Oracle数据库进程应用开发时关于这一点的理解是很重要的。如果相应的调用较多，就得增加processes的数量以适应调用的增加。没有CGA中的数据结构，CALLS是没法工作的。而实际上跟一次CALL相关的数据结构一般都是放在UGA中，如SQL AREA，PL/SQL AREA和SORT AREA它们都必须在UGA中，因为它们要在各CALLS之间要一直存在并且可用。而CGA中所包含的数据结构是要在一次CALL结束后能够释放的。例如CGA包含了关于递归调用的信息，直接I/O BUFFER等还有其它的一些临时性的数据结构。Java Call Memory也是在CGA中。这一段内存比Oracle的其它内存段管理得更密集。它分成三个Space：Stack Space， New Space， Old Space。在New Space和Old Space中不再被参考使用的Chunks，根据它们在使用期间的长度及SIZE的不同，在调用的执行过程中将被当成不用的Chunks收集起来。New Space Chunks很多次的不用的Chunks的反复收集过程中没有被收集的Chunks将会被放到Old Space Chunks中。这是在Oracle内存管理中唯一的一个废物收集(garbage collection)，其它的Oracle内存段都是释放Dead Chunks。

 

正象前面提到的，Oracle数据库系统和信能相关最主要的就是SGA（System Global Area）了，它由SHARED POOL、DATABASE BUFFER CACHE和Redo LOG BUFFER三部分组成， 其中又以SHARED POOL、DATABASE BUFFER CACHE对于日常碰到的系统性能调整最为重要。现在我们假定Oracle运行在32位的操作系统上，并且系统有足够多的内存可以分配，那么我们可以修改sga_max_size，使其等于4G的60%（因为PGA也需要使用内存，并且它并未包含在SGA中），即：

       sga_max_size=2458M

 

① 调整SHARED POOL。在SHARED POOL中，又包含两个CACHE，即Library Cache和Data Dictionary Cache。

Library Cache用于存放共享SQL语句和PL/SQL语句，采用LRU（Least Recently Used）算法进行管理，Oracle可以用已经cache在其中的SQL语句，而不需要re-parsing，我们可以通过下面的SQL语句来查询Library Cache的命中率（Hit Ratio）：

SQL> select GETHITRATIO

from v$librarycache

where NAMESAPCE ＝'SQL AREA';

如果得到的结果小于90%，那么说明命中率不高，需要增大Library Cache了。如果想要将某个package放于Library Cache中，请使用以下命令：

SQL> execute dbms_shared_pool.keep('package_name');

要将某个package从Library Cache移出，请使用以下命令：

SQL> execute dbms_shared_pool.unkeep('package_name');

 

Data Dictionary Cache主要用来保持字典对象的相关信息。可以查看视图v$rowcache，该视图由以下各列组成：

列名
 说明
 
PARAMETERS
 数据字典项的分类
 
GETS
 对于此类字典项的信息获取次数
 
GETMISSES
 对于此类字典项的信息获取失败次数
 

如果GETMISSES/ GETS * 100% > 15%，那么就要考虑增大Data Dictionary Cache了。

 

很重要地，Library Cache和Data Dictionary Cache不能单独进行调整，要对它们进行调整，只能通过调整SHARED_POOL_SIZE来进行（Oracle会自动去分配Library Cache和Data Dictionary Cache的大小，如何分配这个过程不被人控制）。比如，如果想增加Library Cache，那么请增加SHARED_POOL_SIZE。下面是分配SHARED POOL大小的经验公式：

SHARED_POOL_SIZE = sga_max_size * 40%

       在前面我们已经得出sga_max_size＝2458M了，那么按照上面这个公式，

       SHARED_POOL_SIZE = 2458 * 40% ＝ 983M

 

② 调整DATABASE BUFFER CACHE。BUFFER CACHE也是SGA的一部分，它主要保存可以被所有用户共享的数据块。用下面的SQL语句来检查BUFFER CACHE的命中率：

    SQL> select 1 – (phys.value/(cur.value + con.value)) “CACHE HIT RATIO”

                from v$sysstat cur, v$sysstat con, v$sysstat phys

                where cur.name = ‘db block gets’ and

                            con.name = ‘consistent gets’ and

                            phys.name = ‘physical reads’;

如果上面的命令的数据结果小于80%，那么就需要考虑增大DATABASE BUFFER CACHE了。在Oracle9i以前的版本，需要在init<SID>.ora中增加DB_BLOCK_BUFFERS的数值（因为数据库系统安装完成后DB_BLOCK_SIZE就固定了），DATABASE BUFFER CACHE的容量＝DB_BLOCK_BUFFERS * DB_BLOCK_SIZE；在Oracle9i及其以后版本，则需要修改该DB_CACHE_SIZE。

 

       DATABASE BUFFER CACHE的大小计算的经验公式：

       DATABASE BUFFER CACHE的大小 = sga_max_size * 40%

       在前面我们已经得出sga_max_size＝2458M了，那么按照上面这个公式，

       DATABASE BUFFER CACHE的大小 = 2458 * 40% ＝ 983M

 

③ 调整PGA大小。调整PGA的大小是通过设置pga_aggregate_target这个参数来实现的。设置此参数时， 要将 SGA 从可用于 Oracle 例程的系统内存总量中减去。然后可将剩余内存量分配给 pga_aggregate_target。如果继续我们前面说到的情况，那么，

       pga_aggregate_target ＝ 4G － 2458M ＝ 1638M

PGA影响到的最主要是sort_area_size 和 hash_area_size这两个参数，它们分别根据排序操作的多少/大小，hash_join的多少/大小来决定的。在江钻现有的系统中，我们发现排序操作和jion的操作都有很多(感觉上join操作比排序操作更多也更复杂)，可以考虑以下的公式：

sort_area_size = pga_aggregate_target * 30%；

hash_area_size ＝ pga_aggregate_target * 40%；

 

④ 其它参数调整。

db_file_multiblock_read_count表明在涉及一个完全连续扫描的一次 I/O 操作过程中读取的块的最大数量，默认值是8。增加该值可以提高查询的速度，但取值到底是多大，要根据操作系统的情况来定，一般而言最好不要超过32（可在实际工作中反复试试，一遍确定一个最合理的值）。

 

java_pool_size以字节为单位， 指定 Java 存储池的大小， 它用于存储 Java 的方法和类定义在共享内存中的表示法， 以及在调用结束时移植到 Java 会话空间的 Java 对象。如果没有使用Oralce HTTP之类的服务，令java_pool_size = 64M即可

 

large_pool_size指定大型池的分配堆的大小，它可被共享服务器用作会话内存，用作并行执行的消息缓冲区以及用作 RMAN 备份和恢复的磁盘 I/O 缓冲区。一般可令large_pool_size ＝ pga_aggregate_target * 10%。

 

最后，我们必须要认识到系统参数调优的过程是一个比较复杂的过程，而且根据不同应用目的的系统各种参数的给定有非常大的差别，比如有些系统数据变化不是很频繁，主要用于查询，而另外一些系统主要用来插入数据，因此放之四海而皆准的方法或者经验公式是不存在的。尽管如此，一些经过许多人积累下来的方法或者经验公式还是提供了一个很好的出发点，在此基础上，通过反复试验，应该可以得到一组相对优化的参数取值。

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/pathuang68/archive/2009/04/16/4084139.aspx