【PGA】通過10g新特性得到PGA的調整建議
看一下這個檢視能給我們帶來什麼樣的資訊(檢視中每個列都很有幫助):
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得到的一個統計柱狀圖。
附圖如下:
【附檢視官方解釋】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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle12c新特性(9)--限制PGA的大小Oracle
- 對SGA和PGA的優化建議優化
- Oracle9i的動態SGA,PGA特性探索Oracle
- 全面分析PGA
- 如何設定新資料庫的PGA值資料庫
- pga_aggregate_target和_pga_max_size都不能絕對限制實際PGA的使用
- pga_aggregate_target 相關總結 -- Oracle PGAOracle
- 認識PGA及PGA_AGGREGATE_TARGET [final]
- Oracle:PGA 簡介Oracle
- Oracle PGA詳解Oracle
- Oracle PGA管理(一)Oracle
- oracle PGA 構成Oracle
- 使用dbms_sqltune獲得SQL調整建議SQL
- Oracle PGA引數的管理Oracle
- oracle例項記憶體(SGA和PGA)調整Oracle記憶體
- pga_aggregate_target和_pga_max_size都不能絕對限制實際PGA的使用--ora04030
- PGA基礎知識
- PGA 文件及個人理解
- oracle10g PGAOracle
- PGA學習筆記筆記
- 如何優化oracle pga優化Oracle
- PGA 記憶體的管理 (zt)記憶體
- Oracle9i中的PGAOracle
- PGA自動管理原理深入分析及效能調整(六)
- PGA自動管理原理深入分析及效能調整(一)
- PGA自動管理原理深入分析及效能調整(五)
- (轉)PGA自動管理原理深入分析及效能調整
- pga知識點總結
- PGA,sga命中sql查詢SQL
- ORACLE PGA程式全域性區Oracle
- v$pga_target_advice
- oracle PGA管理(演算法)Oracle演算法
- Oracle Automatic PGA Memory ManagementOracle
- 查詢SGA和PGA大小
- V$PGA_TARGET_ADVICE的用法
- 【kingsql分享】關於PGA的研究SQL
- PGA/UGA、SGA的一點知識
- pga使用的一些資料