Oracle SGA参数调整
一. SGA的组成：
1 自动 SGA 管理后，Oracle 可以自动为我们调整以下内存池的大小: 2 shared pool 3 buffer cache 4 large pool 5 java pool 6 streams pool 7 需要手动调整的参数 8 log buffer 9 db_nk_cache_size 10 db_keep_cache_size 11 db_recycle_cache_size
1.1 shared pool调整
1.1.1查询SGA的大小
1 SQL> show parameter sga_target 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 sga_target big integer 276M 6 SQL>
1.1.2 SGA组件
通过以上语句可以看到 SGA 的大小是289406976.那么它如何分配的，通过以下语句可以看到:
SQL> select t.COMPONENT,t.CURRENT_SIZE/1024/1024 "CURRENT_SIZE (MB)"from v$sga_dynamic_components t; COMPONENT CURRENT_SIZE (MB) ------------------------------ ----------------- shared pool 76 large pool 4 java pool 4 streams pool 0 DEFAULT buffer cache 188 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 COMPONENT CURRENT_SIZE (MB) ------------------------------ ----------------- DEFAULT 32K buffer cache 0 ASM Buffer Cache 0 13 rows selected. SQL>
SGA 实际大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g 中的新内存池）+ LOG_BUFFERS+11K(Redo Log Buffer 的保护页) + 1MB + 16M(SGA 内部内存消耗，适合于 9i 及之前版本)
1.1.3 查看对shared pool设置的建议
一般来说，设置 1GB 以上的 shared pool 不会给性能带来明显的提高，相反，这将给 Oracle管理 shared pool 以及监控 shared pool 的过程中带来较多的麻烦。只要将初始化参数 statistics_level 设置为 typical 或者all 就启动对 shared pool 的建议，basic 为关闭该功能。可以通过视图v$shared_pool_advice 来查看对shared pool设置的建议。
1 select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP", 2 t.ESTD_LC_SIZE "EL", 3 t.ESTD_LC_MEMORY_OBJECTS "ELM", 4 t.ESTD_LC_TIME_SAVED "ELT", 5 t.ESTD_LC_TIME_SAVED_FACTOR "ELTS %", 6 t.ESTD_LC_MEMORY_OBJECT_HITS "ELMO" 7 from v$shared_pool_advice t 8 ; 9 10 SP EL ELM ELT ELTS % ELMO 11 ---------- ---------- ---------- ---------- ---------- ---------- 12 52 9 1136 3670 .9892 65132 13 60 15 2021 3700 .9973 65735 14 68 22 3147 3705 .9987 65859 15 76 29 3712 3710 1 65984 16 84 36 4359 3786 1.0205 66129 17 92 43 4828 3786 1.0205 66156 18 100 49 5785 3786 1.0205 66159 19 108 49 5785 3786 1.0205 66159 20 116 49 5785 3786 1.0205 66159 21 124 49 5785 3786 1.0205 66159 22 132 49 5785 3786 1.0205 66159 23 24 SP EL ELM ELT ELTS % ELMO 25 ---------- ---------- ---------- ---------- ---------- ---------- 26 140 49 5785 3786 1.0205 66159 27 148 49 5785 3786 1.0205 66159 28 156 49 5785 3786 1.0205 66159
我们主要关注 estd_lc_time_saved_factor 列的值，当该列的值为 1 时表示再增加 shared pool的大小对性能的提高没有意义，对于上面例子来说，当 shared pool 为 76MB 时达到最佳。
1 注： 2 --ESTD_LC_SIZE:估计库高速缓存的使用数量（以兆字节为单位） 3 --ESTD_LC_MEMORY_OBJECTS: 估算共享池中库缓存的内存对象数 4 --ESTD_LC_TIME_SAVED: 估算将可以节省的解析时间 5 --ESTD_LC_TIME_SAVED_FACTOR: 估算的节省的解析时间与当前节省解析时间的比 6 --ESTD_LC_MEMORY_OBJECT_HITS: 估算可以直接从共享池中命中库缓存的内存对象的命中次数
1.1.4 查看对buffer cache设置的建议
在如何设置 buffer cache 的大小上，从 Oracle 9i开始通过初始化参数 db_cache_advice来启动 buffer cache 顾问。Oracle 会监控 default 类型，keep 类型和 recycle 类型的 buffer cache的使用，以及其他 5 种不同数据库尺寸（2， 4， 8， 16 或 32KB）的 buffer cache 的使用。Oracle会根据当前所监控到的物理读的速率，估算出在不同大小尺寸的 buffer cache 下，所产生的可能的物理读的数量。Oracle将这些信息放入 v$db_cache_advice 中，每种类型的 buffer cache都会有相应的若干条记录来表示所建议的buffer cache 的大小。下面是对于默认数据块尺寸的 buffer cache 的建议大小应该是多少。
1 SELECT size_for_estimate "target M", buffers_for_estimate,estd_physical_read_factor "physical_read %", estd_physical_reads 2 FROM V$DB_CACHE_ADVICE 3 WHERE name = 'DEFAULT' 4 AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') 5 AND advice_status = 'ON'; 6 7 target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS 8 ---------- -------------------- --------------- ------------------- 9 16 1996 2.5056 30293 10 32 3992 1.5932 19262 11 48 5988 1.306 15790 12 64 7984 1.1789 14253 13 80 9980 1.0866 13137 14 96 11976 1.0254 12397 15 112 13972 1 12090 16 128 15968 1 12090 17 144 17964 1 12090 18 160 19960 1 12090 19 176 21956 1 12090 20 21 target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS 22 ---------- -------------------- --------------- ------------------- 23 188 23453 1 12090 24 192 23952 1 12090 25 208 25948 1 12090 26 224 27944 1 12090 27 240 29940 1 12090 28 256 31936 1 12090 29 272 33932 1 12090 30 288 35928 1 12090 31 304 37924 1 12090 32 320 39920 .8616 10417 33 34 21 rows selected. 35 36 SQL>
1 注： 2 --size_for_estimate : 预测性能数据的Cache大小 3 --buffers_for_estimate: 预测性能数据的Cache大小（缓冲块数） 4 --estd_physical_read_factor: 如果缓冲大小为SIZE_FOR_ESTIMATE 时，建议器预测物理读数与当前实际物理读数的比率值。如果当前物理读数为 0，这个值为空。 5 --estd_physical_reads: 如果缓冲大小为 SIZE_FOR_ESTIMATE 时，建议器预测物理读数。
当前我们的DB_CACHE_SIZE为112-304M，可以看到，它的物理读因子为1，物理读数为12090。那么如何根据这些数据调整DB_CACHE_SIZE呢？给出一个方法，找到变化率较平缓的点作为采用值。因为建议器做预测是，DB_CACHE_SIZE的预测值的增长步长是相同的，是16M。我们按照这一步长增加DB_CACHE_SIZE，如果每次增加物理读降低都很明显，就可以继续增加，直到物理读降低不明显，说明继续增加DB_CACHE_SIZE没有太大作用。当然，性能和可用资源是天平的两端，你需要根据自己系统的实际情况调整。
