【PGA】通過10g新特性得到PGA的調整建議

secooler發表於2009-09-12
10g Oracle給出了一系列的自動優化的建議,PGA分配多大能給系統帶來最大的效能?V$PGA_TARGET_ADVICE檢視給出了很好的“預測”!

看一下這個檢視能給我們帶來什麼樣的資訊(檢視中每個列都很有幫助):
sys@ora10g> SELECT   pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
  2           pga_target_factor,
  3           estd_pga_cache_hit_percentage,
  4           estd_overalloc_count
  5    FROM   v$pga_target_advice;

   PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
        10                .5                            34                   13
        15               .75                            34                   13
        20                 1                           100                   13
        24               1.2                           100                   13
        28               1.4                           100                   13
        32               1.6                           100                    3
        36               1.8                           100                    0
        40                 2                           100                    0
        60                 3                           100                    0
        80                 4                           100                    0
       120                 6                           100                    0
       160                 8                           100                    0

12 rows selected.

通過上面的資料可以得到如下的結論:
1.第一列表示不同的PGA的具體值

2.第二列PGA_TARGET_FACTOR為“1”表示當前的pga_aggregate_target設定大小(其他數值都是以這個資料為基礎的倍數),我這裡是20M,通過pga_aggregate_target引數可以確認一下
sys@ora10g> show parameter pga_aggregate_target;

NAME                    TYPE                 VALUE
----------------------- -------------------- -----------
pga_aggregate_target    big integer          20M

3.第三列表示PGA的估算得到的Cache命中率的百分比
目前系統如果PGA為20M的時候,就可以達到100%的命中率

4.第四列如果為“0”表示可以消除PGA的過載
從上面的資料中可以得到,當PGA為36M的時候,可以消除PGA的過載。

5.綜合以上的結論,我們最終可以將PGA的大小設定為36M。
sys@ora10g> alter system set pga_aggregate_target=36m;

System altered.

6.調整後,再次查詢一下v$pga_target_advice檢視得到如下的建議資訊,可以看到基本上已經滿足現在的系統需求。
sec@ora10g> SELECT pga_target_for_estimate / 1024 / 1024 "PGA(MB)",
  2         pga_target_factor,
  3         estd_pga_cache_hit_percentage,
  4         estd_overalloc_count
  5  FROM   v$pga_target_advice;

   PGA(MB) PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
---------- ----------------- ----------------------------- --------------------
        18                .5                            94                    2
        27               .75                            94                    2
        36                 1                           100                    0
43.1992188               1.2                           100                    0
50.3994141               1.4                           100                    0
57.5996094               1.6                           100                    0
64.7998047               1.8                           100                    0
        72                 2                           100                    0
       108                 3                           100                    0
       144                 4                           100                    0
       216                 6                           100                    0
       288                 8                           100                    0

12 rows selected.


恭喜你,到這裡,您一定已經會“看”這個v$pga_target_advice檢視了。

這是一個動態的過程,可以定期的檢視這個檢視得到更加有效的PGA大小的設定建議。

【OEM方法】同樣是上面的建議資訊,我們可以通過Oracle的OEM得到更加直觀的資訊。
1.使用自己的伺服器的IP地址登陸到OEM介面
http://144.194.192.183:1158/em/console/logon/logon

2.資料使用者名稱(sys)和密碼(sys的密碼),“Connect As”選擇“SYSDB”,最後點選“Login”

3.最上面有三個大的可選選單“Home”、“Performance”、“Administration”和“Maintenance”,需要選擇“Administration”,在“Database Configuration”大類中的第一個就是“Memory Parameters”,點選進入,此時您會看到“SGA”和“PGA”,點選第二個“PGA”,OK,這裡就可以看到“Advice”和“PGA Memory Usage Details”兩個按鈕,第一個“Advice”按鈕得到的資訊就是我們上面通過v$pga_target_advice檢視得到的資訊,這裡會更加直觀的,以曲線圖的形式進行展示。
另外一個按鈕“PGA Memory Usage Details”是通過檢視v$pga_target_advice_histogram得到的一個統計柱狀圖。

附圖如下:

【PGA】通過10g新特性得到PGA的調整建議

【PGA】通過10g新特性得到PGA的調整建議

【PGA】通過10g新特性得到PGA的調整建議


【附檢視官方解釋】10g官方文件中關於v$pga_target_advice和v$pga_target_advice_histogram兩個檢視的說明

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.

Column Datatype Description
PGA_TARGET_FOR_ESTIMATE NUMBER Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)
PGA_TARGET_FACTOR NUMBER PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter
ADVICE_STATUS VARCHAR2(3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter
BYTES_PROCESSED NUMBER Total bytes processed by all the work areas considered by this advice (in bytes)
ESTD_EXTRA_BYTES_RW NUMBER Estimated number of extra bytes which would be read or written if PGA_AGGREGATE_TARGET was 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_PERCENTAGE NUMBER Estimated value of the cache hit percentage statistic when PGA_AGGREGATE_TARGET equals PGA_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_COUNT NUMBER Estimated 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 set PGA_AGGREGATE_TARGET to PGA_TARGET_FOR_ESTIMATE since Oracle will not be able to honor that target.

V$PGA_TARGET_ADVICE_HISTOGRAM

V$PGA_TARGET_ADVICE_HISTOGRAM predicts how statistics displayed by the V$SQL_WORKAREA_HISTOGRAM dynamic view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. This 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 when the STATISTICS_LEVEL initialization parameter is set to BASIC. Base statistics for this view are reset at instance startup or when the value of the PGA_AGGREGATE_TARGET initialization parameter is dynamically modified.

Column Datatype Description
PGA_TARGET_FOR_ESTIMATE NUMBER Value of PGA_AGGREGATE_TARGET for this prediction (in bytes)
PGA_TARGET_FACTOR NUMBER PGA_TARGET_FOR_ESTIMATE / the current value of the PGA_AGGREGATE_TARGET parameter
ADVICE_STATUS VARCHAR2(3) Indicates whether the advice is enabled (ON) or disabled (OFF) depending on the value of the STATISTICS_LEVEL parameter
LOW_OPTIMAL_SIZE NUMBER Lower bound for the optimal memory requirement of work areas included in this row (in bytes)
HIGH_OPTIMAL_SIZE NUMBER Upper bound for the optimal memory requirement of work areas included in this row (in bytes)
ESTD_OPTIMAL_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run optimal given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_ONEPASS_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run one-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_MULTIPASSES_EXECUTIONS NUMBER Number of work areas with an optimal memory requirement comprised between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE which are predicted to run multi-pass given a value of PGA_AGGREGATE_TARGET equal to PGA_TARGET_FOR_ESTIMATE
ESTD_TOTAL_EXECUTIONS NUMBER Sum of ESTD_OPTIMAL_EXECUTIONS, ESTD_ONEPASS_EXECUTIONS, and ESTD_MULTIPASSES_EXECUTIONS
IGNORED_WORKAREAS_COUNT NUMBER Number of work areas with optimal memory requirement between LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE ignored in the advice generation due to memory and CPU constraints


-- The End --



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-614500/,如需轉載,請註明出處,否則將追究法律責任。

相關文章