« 经典重现 | Blog首页 | What's Mean "internal dummy connection"? »
关于自动PGA管理的进一步探讨
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2006/04/oracle_pga_internal.html
伴随自动PGA调整新特性的引入,Oracle随之引入了一系列新的视图,V$PGASTAT就是其中的一个.
在V$PGASTAT中有这样一个条目: global memory bound ,该条目记录数据库允许的最高PGA内存使用量,我们可以从不同的PGA参数设置来观察一下Oracle运行的PGA上限.
SQL> alter system set pga_aggregate_target=&Nm;
Enter value for nm: 10m
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=10m
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:03:04.12
…….
SQL> SET autotrace off
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
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC
-------------------------------------------------- ------------------ -----
LAST_MEMORY_USED/1024/1024 LAST_EXE LAST_TEMPSEG_SIZE
-------------------------- -------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO
.548828125 206 PASSES 62914560
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 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=&Nm;
Enter value for nm: 30M
old 1: alter system set pga_aggregate_target=&Nm
new 1: alter system set pga_aggregate_target=30M
System altered.
Elapsed: 00:00:00.05
SQL> SET autotrace traceonly
SQL> SELECT DISTINCT * FROM t WHERE ROWNUM < 500000;
20000 rows selected.
Elapsed: 00:00:53.30
………..
SQL> SET autotrace off
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
4 WHERE l.hash_value = a.hash_value
5 AND sql_text = 'SELECT DISTINCT * FROM t WHERE ROWNUM < 500000';
SQL_TEXT OPERATION_TYPE POLIC LAST_MEMORY_USED/1024/1024
-------------------------------------------------- ------------------ ----- --------------------------
LAST_EXECUTION LAST_TEMPSEG_SIZE
-------------------- -----------------
SELECT DISTINCT * FROM t WHERE ROWNUM < 500000 GROUP BY (SORT) AUTO 1.48046875
6 PASSES 57671680
Elapsed: 00:00:00.02
SQL>
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 30
global memory bound 1.5
Elapsed: 00:00:00.00
我们可以注意到,PGA的global memory bound会一直处在5%的PGA_AGGREGATE_TARGET参数设置,直到5% PGA_AGGREGATE_TARGET超过100M,然后global memory bound被限制为100M,也就是满足我们前文提到的:
对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
注意,修改PGA_AGGREGATE_TARGET参数可以使用如下命令:
alter system set pga_aggregate_target=4096M ;
修改参数后,通常需要之行操作才能看到视图信息的变化:
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 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
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 20
global memory bound 1
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 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
2 FROM v$pgastat
3 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
2 FROM v$pgastat
3 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,该参数的缺省值为200M,单进程串行操作PGA的上限不能超过该参数的1/2.
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm LIKE '%&par%'
7 /
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 "_pga_max_size"=400M;
System altered.
……………………
SQL> SELECT NAME, VALUE / 1024 / 1024 MB
2 FROM v$pgastat
3 WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
NAME MB
---------------------------------------------------------------- ----------
aggregate PGA target parameter 4096
global memory bound 200
对于PGA的控制,还有一系列的内部参数,列举如下,仅供参考:
SQL> l
1 SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6* AND x.ksppinm LIKE '%&par%'
SQL> /
Enter value for par: smm
old 6: AND x.ksppinm LIKE '%&par%'
new 6: AND x.ksppinm LIKE '%smm%'
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
11 rows selected.
历史上的今天...
>> 2009-04-09文章:
>> 2008-04-09文章:
------
这篇 【关于自动PGA管理的进一步探讨】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2006-04-09 16:47 | Comments (12) | Posted to Internal | Edit |
留言 (12)
eygle,那怎么样能突破pga的大小限制呢？谢谢:)
Posted by: battleman at September 5, 2006 12:43 PM
_pga_max_size 可以部分控制阿，通常缺省足够了。
你的应用有特殊需求么？
Posted by: eygle at September 5, 2006 4:43 PM
在10G上测试了一下，10G把global memory bound限制为PGA_AGGREGATE_TARGET的20％，其他的没有变化了。
Posted by: 秦一 at September 6, 2006 10:40 AM
您好，我的RAM是8G,我的PGA target是1.5G,之前用10.2.0.1.0时PGA大概用300M就不再涨了，升级到10.2.0.3之后，session一直呈上升趋势，PGA也呈上升趋势，好像一直往上涨，现在已经700M了，很担心。
Posted by: kevensun at April 11, 2007 1:25 PM
没关系，没达到Target一般不用太担心。
不过我怀疑是有些SQL的执行计划改变了，导致PGA耗用过高。
你可以查查v$tempseg_usage视图
Posted by: eygle at April 11, 2007 1:41 PM
可不可以把pga变成动态设置的呢?就是用程序动态调整
Posted by: hyj at April 27, 2007 4:42 PM
盖大哥
你好!我想请教一个问题。在你的《oracle数据库性能优化》书中，在自动PGA管理(13章)中,提到并发情况下PGA自动管理的效果，书中没有讲到如何进行并发设置的，我想了解一下怎样进行并发设置呢？
Posted by: hyj at May 10, 2007 11:37 AM
Oracle自行管理，无需进行参数设置。
Posted by: eygle at May 10, 2007 2:09 PM
我的 _pga_max_size=487157760
没有进行手工调整为什么也变成了400m?
10.2.0.1.0
Posted by: field0617 at June 21, 2007 10:32 AM
还想问个事情,10g是自动pga管理内存的,如果我修改sort_area_size的话这个值还会不会起作用?
Posted by: field0617 at June 21, 2007 10:51 AM
9i的自动PGA管理中，sort_area_size就不再生效了。
你确认你的_pga_max_size没有调整过？
你的PGA设置的是多少?
Posted by: eygle at June 21, 2007 11:03 AM
SELECT NAME, VALUE / 1024 / 1024 MB
FROM v$pgastat
WHERE NAME IN ('aggregate PGA target parameter', 'global memory bound');
aggregate PGA target parameter 2323
global memory bound 232.294921875
比例变为10%了,和上面翻倍刚好吻合,就是不知道为什么自动也这样了
确实没有调过.我的10g+asm raw磁盘,和这个有关系吗.
Posted by: field0617 at June 21, 2007 11:15 AM