第1章 章名章名章名章名章名
SQL> SELECT sql_text, operation_type, POLICY, last_memory_used / 1024 / 1024, 2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a WHERE l.hash_value = a.hash_value 4 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000'; OPERATION_TYPE POLIC
LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
------------------ ------ ------------------------- -------- ----------------- GROUP BY (SORT) AUTO .548828125206 PASSES 62914560 SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 10 global memory bound .5 SQL> alter system set pga_aggregate_target=30M; System altered.
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000; 20000 rows selected. Elapsed: 00:00:53.30 SQL> SET autotrace off
SQL> SELECT operation_type, POLICY, last_memory_used / 1024 / 1024, 2 last_execution, last_tempseg_size
3 FROM v$sql l, v$sql_workarea a WHERE l.hash_value = a.hash_value 4 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE ------------------ ----- --------------------------- -------------- ----------------- GROUP BY (SORT) AUTO 1.480468756 PASSES 57671680 SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 30 global memory bound 1.5
在Oracle9iR2中，通过以上测试可以注意到，PGA的global memory bound会一直处在5%的PGA_AGGREGATE_TARGET参数设置，直到5%*PGA_AGGREGATE_TARGET超过100M,然后global memory bound被限制为100M。
修改PGA_AGGREGATE_TARGET参数可以使用类似如下命令:
alter system set pga_aggregate_target=4096M ;
修改参数后,通常需要执行一些排序操作才能看到视图信息的变化，以下通过一些不同设置输出继续来看一下PGA_AGGREGATE_TARGET与global memory bound的关系：
·11·
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 10 global memory bound .5 SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 40 global memory bound 2 SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB
---------------------------------------------------------------- ---------- aggregate PGA target parameter 1024 global memory bound 51.1992188 SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 4096 global memory bound 100
实际上这个100M的上限是受到了另外一个隐含参数的控制，该参数为_pga_max_size，在Oracle9iR2中该参数的缺省值为200M，单进程串行操作PGA的上限不能超过该参数的1/2.
SQL> @GetHidPar
Enter value for par: pga_max
old 6: AND x.ksppinm LIKE '%&par%' new 6: AND x.ksppinm LIKE '%pga_max%' NAME VALUE DESCRIB
--------------------------------------------- ---------------- ---
_pga_max_size 209715200 Maximum size of the PGA memory for one process
如果修改该参数， global memory bound将可以突破100M的上限:
SQL> alter system set \System altered. ……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB FROM v$pgastat ·12·
第1章 章名章名章名章名章名
2 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound'); NAME MB ---------------------------------------------------------------- ---------- aggregate PGA target parameter 4096 global memory bound 200
从Oracle10gR2开始，_pga_max_size的设置和PGA_AGGREGATE_TARGET相关，在不同P_A_T参数设置下，_pga_max_size会自动调整以提供更好的性能需要。通过一些简单测试可以得出Oracle10gR2下两者的关系：
_pga_max_size PGA_AGGREGATE_TARGET 比例关系 209715200 800M 0.25 214732800 1G 0.2 429486080 2G 0.2 644239360 3G 0.2 858992640 4G 0.2 939180032 5G 0.2 939180032 6G 0.15
注意到当P_A_T设置大于5G之后，_pga_max_size不再变化，在1G ~ 5G范围，_pga_max_size按照20%*P_A_T设置增长。当PGA增大，单进程能够使用的最大内存应当随之增加，这种增强是极其有益的。
对于PGA的控制,还有一系列的内部参数,列举如下,仅供参考:
NAME VALUE DESCRIB
------------------------ ----- ---------------------------------------------------------- _smm_auto_min_io_size 56 Minimum IO size (in KB) used by sort/hash-join in auto mode _smm_auto_max_io_size 248 Maximum IO size (in KB) used by sort/hash-join in auto mode _smm_auto_cost_enabled TRUE if TRUE, use the AUTO size policy cost functions _smm_control 0 provides controls on the memory manager _smm_trace 0 Turn on/off tracing for SQL memory manager _smm_min_size 128 minimum work area size in auto mode
_smm_max_size 2560 maximum work area size in auto mode (serial) _smm_px_max_size 15360 maximum work area size in auto mode (global)
_smm_bound 0 overwrites memory manager automatically computed bound _smm_advice_log_size 0 overwrites default size of the PGA advice workarea history log _smm_advice_enabled TRUE if TRUE, enable v$pga_advice
5.2.6 PGA的调整建议
伴随自动PGA调整功能的引入，Oracle同时引入相应的动态性能视图用于优化建议，PGA的优化建议通过v$pga_target_advice 和 v$pga_target_advice_histogram 提供。
v$pga_target_advice视图通过对不同PGA设置进行评估，给出在不同设置下的PGA命中率和OverAlloc等信息。
SQL> select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB, PGA_TARGET_FACTOR,
·13·
书名书名书名书名书名书名书名书名书名书名书名书名书名书名
2 ESTD_PGA_CACHE_HIT_PERCENTAGE, ESTD_OVERALLOC_COUNT 3 from v$pga_target_advice;
PGAMB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ---------- ----------------- ----------------------------- -------------------- 37.5 0.125 35 391 75 0.25 43 58 150 0.5 61 0 225 0.75 70 0 300 1 70 0 360 1.2 71 0 420 1.4 72 0 480 1.6 72 0 540 1.8 72 0 600 2 72 0 900 3 72 0 1200 4 72 0 1800 6 72 0 2400 8 72 0 14 rows selected
可以看到，在以上输出中，当PGA设置为150M时，即可消除PGA过载；在PGA设置为420M时，可以达到最高命中率72%；当前PGA设置为300M。根据这些信息，我们可以调整PGA为420M，从而提高性能。
从Oracle9i或者Oracle10g提供的OEM界面上，我们都可以直观的看到这些建议信息： 下图是当前的PGA设置页：
点击建议按钮我们可以看到Oracle对于PGA设置的建议：
·14·
第1章 章名章名章名章名章名
本例修改后数据库的PGA分配为420M:
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 420M
该曲线既是根据v$pga_target_advice收集的信息绘制出来的，我们可以直接在图中拖动调整PGA的设置，确定后在PGA页应用该修改即可达到优化数据库的目的。
调整PGA后，建议信息会刷新，统计信息需要重新评估：
SQL> select PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB,PGA_TARGET_FACTOR, 2 ESTD_PGA_CACHE_HIT_PERCENTAGE,ESTD_OVERALLOC_COUNT 3 from v$pga_target_advice;
PGAMB PGA_TARGET_FACTOR ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT ---------- ----------------- ----------------------------- -------------------- 52.5 0.125 100 1 105 0.25 100 0 210 0.5 100 0 315 0.75 100 0 420 1 100 0 504 1.2 100 0 588 1.4 100 0
·15·
05内存管理
