首页 > 代码库 > 如何估算oracle内存-SGA篇 (转)

如何估算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

 

文章转自: <http://blog.csdn.net/colin_liu2009/article/details/6928284>

 

如何估算oracle内存-SGA篇 (转)