如何估算oracle内存-SGA篇 (转)
一般情况下，在系统稳定之前，是不知道SGA和PGA该调整为多少。在这里根据经验，在调整系统的时候有个规则。SGA 通常情况下不能大于memory的50%, 另外oracle推荐得有一个分配规则，oracle推荐分配给oracle实例的内存为物理内存的80%。比如8GB的物理内存，按照Oracle的推荐，分配给Oracle实例的内存大约为6.4G(8*80%)，对于OLAP应用来说，分配给SGA的值即sga_max_size的值大约为5.12G(8*80%*80%),PGA的值即pga_aggregate_target的值大约为1.28G（8*80%*20%）；对于DSS来说，分配给SGA和PGA的值各位实例内存的50%即3.2G。当然上面都是针对新的数据库来说的初始值，需要在使用过程中进行调整。
下面简单用公式表示(oracle建议)：
对OLTP而言(新数据的初始参数)
SGA=memory*80%*80%
PGA=memory*80%*20%
对DSS而言(新数据的初始参数)
SGA=memory*80%*50%
PGA=memory*80%*50%
那么在使用过程中如何确定SGA的大小呢，我们如下展开讨论：
首先来看SGA是有哪些部分组成
SQL> show user
User is "sn_qudao"
SQL> select component,current_size/1024/1024 as current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool 912
large pool 16
java pool 16
streams pool 0
DEFAULT buffer cache 576
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
DEFAULT 32K buffer cache 0
ASM Buffer Cache 0
13 rows selected
SQL> show parameters shared_pool;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 17616076
shared_pool_size big integer 0
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及之前版本)
在上面公式中，对于SGA来说有两个非常重要的指标DB_CACHE_SIZE和SHARED_POOL_SIZE 下面我们分别来讨论如何取得这两个的值。
一般来说设置1GB以上的shared pool不会给性能带来明显的提高，相反，这将给Oracle管理shared pool以及监控shared pool的过程中带来较多的麻烦。而从目前的系统来说，我们没有单独分配shared_pool内存，而是通过内存自动管理来分配的，当前shared_pool_size为912M。如果我们硬性的要求设置shared_pool的值的话，建议在系统上线之时设置为SGA的10%,但是不要超过1G，然后通过顾问来判断设置是否合理，当然必须将初始化参数statistics_level设置为typical或者all，这样才能产生顾问。
SQL> 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 /
SP EL ELM ELT ELTS % ELMO
---------- ---------- ---------- ---------- ---------- ----------
432 98 4203 739431 0.979 175769954
528 191 7149 744591 0.9858 176184285
624 286 10082 750144 0.9932 176413511
720 381 16457 753856 0.9981 176552951
816 476 17949 754790 0.9993 176657863
912 571 20518 755308 1 176731921
1008 666 27638 755544 1.0003 176765699
1104 761 39851 755725 1.0006 176794037
1200 856 43813 755927 1.0008 176817793
1296 951 47191 756054 1.001 176837933
1392 1046 55562 756240 1.0012 176854191
1488 1141 60339 756317 1.0013 176866602
1584 1236 63715 756401 1.0014 176878638
1680 1330 64144 756455 1.0015 176889564
1776 1425 64660 756503 1.0016 176899694
1872 1520 64746 756561 1.0017 176910238
16 rows selected
ESTD_LC_SIZE:估计库高速缓存的使用数量（以兆字节为单位）
ESTD_LC_MEMORY_OBJECTS: 估算共享池中库缓存的内存对象数
ESTD_LC_TIME_SAVED: 估算将可以节省的解析时间
ESTD_LC_TIME_SAVED_FACTOR: 估算的节省的解析时间与当前节省解析时间的比
ESTD_LC_MEMORY_OBJECT_HITS: 估算可以直接从共享池中命中库缓存的内存对象的命中次数
我们主要关注estd_lc_time_saved_factor列的值，当该列的值为1时表示再增加shared pool的大小对性能的提高没有意义，对于上面例子来说，当shared pool为912MB时达到最佳。
如何设置buffer cache的大小，在oracle9i之后开始通过设置参数db_cache_advice来启动顾问，oracle会监控各种类型的buffer cache的使用，oracle会根据当前所监控到的物理读得速率，从而估算在不同大小尺寸下的buffer cache产生的可能的物理读的数量，将这些信息放入到v$db_cache_advice。
SQL> show parameters db_cache_advice;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
当然这个参数设置为on之后,CPU的usage会有所增加，因为会存在额外的记录，在文档中提到
When the advisory is enabled,there is a small increase in CPU usage, because additional bookkeeping is required.
SQL>
SQL> SELECT SIZE_FOR_ESTIMATE "target M",
2 BUFFERS_FOR_ESTIMATE,
3 ESTD_PHYSICAL_READ_FACTOR "physical_read %",
4 ESTD_PHYSICAL_READS
5 FROM V$DB_CACHE_ADVICE
6 WHERE NAME = 'DEFAULT' AND
7 BLOCK_SIZE =
8 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') AND
9 ADVICE_STATUS = 'ON';
target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS
---------- -------------------- --------------- -------------------
48 5937 14.2714 146488649
96 11874 7.9979 82094677
144 17811 6.4177 65874181
192 23748 5.6685 58183947
240 29685 5.0403 51735717
288 35622 4.3047 44185592
336 41559 3.4006 34905795
384 47496 2.4827 25483716
432 53433 1.8562 19052864
480 59370 1.5658 16071812
528 65307 1.2588 12921439
576 71244 1 10264507
624 77181 0.8152 8367972
672 83118 0.6549 6721931
720 89055 0.5826 5980169
768 94992 0.5423 5566815
816 100929 0.511 5245267
864 106866 0.4892 5021126
912 112803 0.4687 4811105
960 118740 0.4317 4431650
20 rows selected
size_for_estimate : 预测buffer cache的尺寸
buffers_for_estimate: 预测buffer Cache大小（缓冲块数）
estd_physical_read_factor: 这一缓冲大小时，物理读因子，它是如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数与当前实际物理读数的比率值。如果当前物理读数为0，这个值为空。
estd_physical_reads: 如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数。
上面输出中ESTD_PHYSICAL_READ_FACTOR为1时，表示当前cache的大小为576M(如下查询)，如果将cache大小设置为480M,那么建立的物理读因子会增加56.58%(1.5658-1)，如果将cache大小设置为672,那么那么建立的物理读因子会减少34.51%(1-0.6549),如果在系统级和SGA_MAX_SIZE允许增加的话，建议将buffer cache增加到672。当然Physical I/O 和 Buffer Cache Size并不是一个直线关系，会存在一个瓶颈，当buffer cache增大到一定值之后，Physical I/O不在减少或者减少得非常少 ，这个时候我们就没有必要在增加的必要，所以在增加cache大小的时候需要结合另外一个参数命中率(cache hit ratio)一起使用。
SQL> select name,value/1024/1024 value from v$sga;
NAME VALUE
-------------------- ----------
Fixed Size 1.99950408
Variable Size 944.000495
Database Buffers 576
Redo Buffers 14
SQL> SELECT NAME,
2 VALUE
3 FROM V$SYSSTAT
4 WHERE NAME IN ('db block gets from cache',
5 'consistent gets from cache',
6 'physical reads cache');
NAME VALUE
---------------------------------------------------------------- ----------
db block gets from cache 126050238
consistent gets from cache 4628694596
physical reads cache 10264507
SQL> select 1-10264507/(126050238+4628694596) from dual;
1-10264507/(126050238+46286945
------------------------------
0.997841207602435
此处说明一下三个参数的解释：
consistent gets from cache：Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache：Number of times a CURRENT block was requested from the buffercache
physical reads： cache Total number of data blocks read from disk into buffer cache.
SQL> show parameters log_buffer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_buffer integer 14234624
SQL> show parameters DB_KEEP_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size big integer 0
SQL> show parameters DB_RECYCLE_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recycle_cache_size big integer 0
上面整段可以用一条语句生成
SQL> SELECT 'SGA-EXP-BUFFER_CACHE-SHARED_POOL',
2 SUM(VALUE) VALUE
3 FROM (SELECT SUM(VALUE) VALUE
4 FROM V$PARAMETER
5 WHERE NAME IN ('db_keep_cache_size',
6 'db_recycle_cache_size',
7 'large_pool_size',
8 'java_pool_size',
9 'streams_pool_size')
10 UNION ALL
11 SELECT SUM(VALUE) VALUE
12 FROM V$PARAMETER
13 WHERE NAME LIKE 'db_%k_cache_size'
14 UNION ALL
15 SELECT (VALUE / 1024 + 11) / 1024 + 1
16 FROM V$PARAMETER
17 WHERE NAME = ('log_buffer'));
'SGA-EXP-BUFFER_CACHE-SHARED_P VALUE
-------------------------------- ----------
SGA-EXP-BUFFER_CACHE-SHARED_POOL 14.5859375
最后的SGA就是上面之和
SGA=576+912+（0+0+0+0+0+0+13.5751953125+1）+16=1518.5751953125M
所以该系统上的SGA大概为1560M。
SQL> show parameters sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1536M
