看一下这个视图能给我们带来什么样的信息（视图中每个列都很有帮助）：
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界面
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博客 ” ，链接：https://blog.itpub.net/70034820/viewspace-2993986/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 中国OCM联盟隆重落户ITPUB社区，百位OCM已入驻，欢迎更多的OCM加入我们哦！
- OCMU联盟-一个有温度的联盟
- 丢掉盔甲，我们依然是骄傲的骑士——Secooler“三问四看”话认证培训
- 【数据安全】一次惊心动魄的ASM磁盘头损坏故障处理过程带来的深思
- 【参数】恢复db_recovery_file_dest_size参数为默认值“0”方法
- 【如何做快乐的DBA】ITPUB社区第一期博客专家专题“如何做快乐的DBA”
- 【Bitmap Index】B-Tree索引与Bitmap位图索引的锁代价比较研究
- 【RMAN】使用RMAN的Duplicate功能创建物理DataGuard报错（ORA-17627、ORA-17629）处理
- 【CUBE】Oracle分组函数之CUBE魅力
- 【ROLLUP】Oracle分组函数之ROLLUP魅力
