PGA SGA db_cache 大小如何设置调优

v$sga_target_advice;

 

V$SGA_TARGET_ADVICE provides information about the SGA_TARGET initialization parameter.

ColumnDatatypeDescriptionSGA_SIZENUMBERSize of the SGASGA_SIZE_FACTORNUMBERRatio between the SGA_SIZE and the current size of the SGAESTD_DB_TIMENUMBEREstimated DB_TIME for this SGA_SIZEESTD_DB_TIME_FACTORNUMBERRatio between ESTD_DB_TIME and DB_TIME for the current size of the SGAESTD_PHYSICAL_READSNUMBEREstimated number of physical reads

 

 

SQL> select * from v$sga_target_advice;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       240              .5          382              1.0241               23258
       360             .75          373                   1               16301
       480               1          373                   1               16213
       600            1.25          373                   1               16213
       720             1.5          373                   1               16213
       840            1.75          373                   1               16213
       960               2          373                   1               16213

解读:

1 原始的SGA值为：480M

2 当大于这个数值的时候我们发现无论是DB_TIME或是物理读都没有性能的提升!

3 当设置为0.75倍*480M也就是360M时,我们发现物理读数的性能有小小的性能降低

4 当设置为0.5倍*480M的时候,我们发现DB_TIME性能下降2.41% 而物理读数比原有的高出:23258-16213=7045

从上面的建议来看 我们设置的SGA480挺合理的!

 

SGA相关重要视图

select * from  v$sgainfo;--各部门分配情况
select * from  v$sgastat;--详细
select * from  v$sga_dynamic_components;--动态
 

select t.component,
       t.current_size / 1024 / 1024 || 'M' "当前大小(MB)",
       MIN_SIZE / 1024 / 1024 || 'M' "MIN_SIZE(MB)",
       MAX_SIZE / 1024 / 1024 || 'M' "MAX_SIZE(MB)"
  from v$sga_dynamic_components t;

 

SQL> select t.component,
  2         t.current_size / 1024 / 1024 || 'M' "当前大小(MB)",
  3         MIN_SIZE / 1024 / 1024 || 'M' "MIN_SIZE(MB)",
  4         MAX_SIZE / 1024 / 1024 || 'M' "MAX_SIZE(MB)"
  5    from v$sga_dynamic_components t;
 
COMPONENT                                                        当前大小(MB)                              MIN_SIZE(MB)                              MAX_SIZE(MB)
---------------------------------------------------------------- ----------------------------------------- ----------------------------------------- -----------------------------------------
shared pool                                                      224M                                      224M                                      224M
large pool                                                       4M                                        4M                                        4M
java pool                                                        4M                                        4M                                        4M
streams pool                                                     0M                                        0M                                        0M
DEFAULT buffer cache                                             240M                                      240M                                      240M
KEEP buffer cache                                                0M                                        0M                                        0M
RECYCLE buffer cache                                             0M                                        0M                                        0M
DEFAULT 2K buffer cache                                          0M                                        0M                                        0M
DEFAULT 4K buffer cache                                          0M                                        0M                                        0M
DEFAULT 8K buffer cache                                          0M                                        0M                                        0M
DEFAULT 16K buffer cache                                         0M                                        0M                                        0M
DEFAULT 32K buffer cache                                         0M                                        0M                                        0M
Shared IO Pool                                                   0M                                        0M                                        0M
ASM Buffer Cache                                                 0M                                        0M                                        0M
 
14 rows selected
SQL> --DEFAULT buffer cache 为240M
 

v$pga_target_advice

 

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

The content of the view is empty if PGA_AGGREGATE_TARGET is not set. In addition, the content of this view is not updated if the STATISTICS_LEVEL parameter is set to BASIC. Base statistics for this view are reset at instance startup and when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

ColumnDatatypeDescriptionPGA_TARGET_FOR_ESTIMATENUMBERValue of PGA_AGGREGATE_TARGET for this prediction (in bytes)PGA_TARGET_FACTORNUMBERPGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameterADVICE_STATUSVARCHAR2(3)Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameterBYTES_PROCESSEDNUMBERTotal bytes processed by all the work areas considered by this advice (in bytes)ESTD_EXTRA_BYTES_RWNUMBEREstimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGETwas set to the value of the PGA_TARGET_FOR_ESTIMATE column. This number is derived from the estimated number and size of work areas which would run in one-pass (or multi-pass) for that value of PGA_AGGREGATE_TARGET.ESTD_PGA_CACHE_HIT_PERCENTAGENUMBEREstimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equalsPGA_TARGET_FOR_ESTIMATE. This column is derived from the above two columns and is equal to BYTES_PROCESSED / (BYTES_PROCESSED + ESTD_EXTRA_BYTES_RW)ESTD_OVERALLOC_COUNTNUMBEREstimated number of PGA memory over-allocations if the value of PGA_AGGREGATE_TARGET is set to PGA_TARGET_FOR_ESTIMATE. A nonzero value means that PGA_TARGET_FOR_ESTIMATE is not large enough to run the work area workload. Hence, the DBA should not setPGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE since Oracle will not be able to honor that target.

 

SQL> Select pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
  2         pga_target_factor "与当前PGA比(倍)",
  3         estd_pga_cache_hit_percentage 命中率,
  4         estd_overalloc_count 超载
  5    FROM v$pga_target_advice order by 1 desc;

   PGA(MB)           与当前PGA比(倍)        命中率         超载
---------- -------------------- ---------- ----------
      2560                    8        100          0
      1920                    6        100          0
      1280                    4        100          0
       960                    3        100          0
       640                    2        100          0
       576                  1.8        100          0
       512                  1.6        100          0
       448                  1.4        100          0
       384                  1.2        100          0
       320                    1        100          0
       240                 0.75        100          0
       160                  0.5        100          0
        80                 0.25         80          5
        40                0.125         80          5

14 rows selected

SQL>

解读:

1　 我们原有的PGA为320M

2     当PGA降低到50％　160M我们的命中率依然100％

从上面的建议值我们可以降低我们的PGA，缓解内存紧张的压力~!~

v$db_cache_advice

 

下面是关于这个视图的结构描述：

 

字段

数据类型

描述

ID

NUMBER

缓冲池标识号（从1到8，1-6对应于DB_nK_CACHE_SIZE，DB_CACHE_SIZE与系统标准块尺寸的序号相关，如DB_BLOCK_SIZE为8K，则DB_CACHE_SIZE的标识号为3（2,4,8…）。7是DB_KEEP_CACHE_SIZE，8是DB_RECYCLE_CACHE_SIZE）

NAME

VARCHAR2(20)

缓冲池名称

BLOCK_SIZE

NUMBER

缓冲池块尺寸（字节为单位）

ADVICE_STATUS

VARCHAR2(3)

建议器状态：ON表示建议器在运行；OFF表示建议器已经关闭。当建议器关闭了，视图中的数据是上一次打开所统计得出的。

SIZE_FOR_ESTIMATE

NUMBER

预测性能数据的Cache大小（M为单位）

SIZE_FACTOR

NUMBER

预测的Cache大小因子（即与当前大小的比例）

BUFFERS_FOR_ESTIMATE

NUMBER

预测性能数据的Cache大小（缓冲块数）

ESTD_PHYSICAL_READ_FACTOR

NUMBER

这一缓冲大小时，物理读因子，它是如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数与当前实际物理读数的比率值。如果当前物理读数为0，这个值为空。

ESTD_PHYSICAL_READS

NUMBER

如果缓冲大小为SIZE_FOR_ESTIMATE时，建议器预测物理读数。

 

 SQL:

select size_for_estimate,
       size_factor,
       estd_physical_read_factor,
       estd_physical_reads
  from v$db_cache_advice
 where name = 'DEFAULT';

SQL> select size_for_estimate,
  2         size_factor,
  3         estd_physical_read_factor,
  4         estd_physical_reads
  5    from v$db_cache_advice
  6   where name = 'DEFAULT';

SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- ----------- ------------------------- -------------------
               24         0.1                    1.4371               23611
               48         0.2                    1.1667               19168
               72         0.3                    1.0762               17683
               96         0.4                      1.07               17581
              120         0.5                    1.0062               16532
              144         0.6                         1               16430
              168         0.7                         1               16430
              192         0.8                         1               16430
              216         0.9                         1               16430
              240           1                         1               16430
              264         1.1                         1               16430
              288         1.2                         1               16430
              312         1.3                         1               16430
              336         1.4                         1               16430
              360         1.5                         1               16430
              384         1.6                         1               16430
              408         1.7                         1               16430
              432         1.8                         1               16430
              456         1.9                         1               16430
              480           2                         1               16430

20 rows selected

SQL>

解读:

1 我们当前的db_cache 为:240M

2 当值为0.6倍*240M=144M时我们发现物理读数的性能无差

如果手动管理内存的话可以调整为144M !当然内存资源不要这么扣,有就用......

命中率怎么查?

  select 1-(sum(decode(name, 'physical reads', value, 0))/
         (sum(decode(name, 'db block gets', value, 0))+
         (sum(decode(name, 'consistent gets', value, 0))))) "Buffer Hit Ratio"
   from v$sysstat;