PGA_AGGREGATE_TARGET
WORKAREA_SIZE_POLICY
V$PGASTAT
_pga_max_size
[@more@] 伴随自动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.
在Oracle9i之前，PGA的计算和控制都是比较复杂的事情，从Oracle9i开始，Oracle提供了一种SQL内存管理的新方法：自动化SQL执行内存管理（Automated SQL Execution Memory Management），使用这个新特性，Oracle可以自动调整S Q L内存区，而不用关闭数据库，这一改进大大简化了DBA的工作，同时也提高了Oracle数据库的性能。
为实现自动的PGA管理，Oracle引入了几个新的初始化参数：
1．PGA_AGGREGATE_TARGET- 此参数用来指定所有session总计可以使用最大PGA内存。这个参数可以被动态的更改,取值范围从10M -- （4096G-1 ）bytes。
2。WORKAREA_SIZE_POLICY- 此参数用于开关PGA内存自动管理功能，该参数有两个选项：AUTO 和 MANUAL，当设置为AUTO时，数据库使用Oracle9i提供的自动PGA管理功能，当设置为MANUAL时，则仍然使用Oracle9i前手工管理的方式。缺省的，Oracle9i中WORKAREA_SIZE_POLICY被设置为AUTO。
需要注意的是，在Oracle9i中，PGA_AGGREGATE_TARGET参数仅对专用服务器模式下（Dedicated Server）的专属连接有效，但是对共享服务器（Shared Server）连接无效；从Oracle10g开始PGA_AGGREGATE_TARGET对专用服务器连接和共享服务器连接同时生效。
PGA_AGGREGATE_TARGET 参数同时限制全局PGA分配和私有工作区内存分配：
1．对于串行操作，单个SQL操作能够使用的PGA内存按照以下原则分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
2．对于并行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）
要理解PGA的自动调整，还需要区分可调整内存（TUNABLE MEMORY SIZE）与不可调整内存（UNTUNABLE MEMORY SIZE）。可调整内存是由SQL工作区使用的，其余部分是不可调整内存。启用了自动PGA调整之后， Oracle仍然需要遵循以下原则：
UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE <= PGA_AGGREGATE_TARGET
数据库系统只能控制可调整部分的内存分配，如果可调整的部分过小，则Oracle永远也不会强制启用这个等式。
另外，PGA_AGGREGATE_TARGET参数在CBO优化器模式下，对于SQL的执行计划会产生影响。Oracle在评估执行计划时会根据PGA_AGGREGATE_TARGET参数评估在Sort，HASH-JOIN或Bitmap操作时能够使用的最大或最小内存，从而选择最优的执行计划。
对于PGA_AGGREGATE_TARGET参数的设置，Oracle提供这样一个建议方案
1．对于OLTP系统
PGA_AGGREGATE_TARGET = ( * 80%) * 20%
2．对于DSS系统
PGA_AGGREGATE_TARGET = ( * 80%) * 50%
也就是说，对于一个单纯的数据库服务器，通常我们需要保留20%的物理内存给操作系统使用，剩余80%可以分配给Oracle使用。Oracle使用的内存分为两部分SGA和PGA，那么PGA可以占用Oracle消耗总内存的20%（OLTP系统）至50%（DSS系统）。这只是一个建议设置，更进一步的我们应该根据数据库的具体性能指标来调整和优化PGA的使用。
当workarea_size_policy 设置成auto ,pga_aggregate_target 这个变量设置的值有实际意义么??这个参数的默认值25165824 bytes 是不是太少了, 但是这样的设置很多系统跑起来也没问题,是不是系统使用的内存可以超过这个值.有没有上限呢,比如超过多少后就不能再扩展了?IXDBA.NET技术社区
还有关于单个用户连接占用的内存数量的计算方法是不是可以从v$sesstat 何v$process 表中大致的估算出.如何计算向对准确呢?如果某个查寻遇到非常大的排序操作,这个查询在使用到多少pga_aggregate_target 指定的内存后进行磁盘排序呢 ？
eygle：
pga_aggregate_target 设置的是个期望值，如果pga_aggregate_target 的设置，不足以供不可调整部分使用，就会扩展，如果 workarea_size_policy 设置成auto 那么你应该相应设置 PGA_AGGREGATE_TARGET 。
PGA内存主要分为两部分
Untunable Memory Size + Tunable Memory Size你应该设置
Untunable Memory Size + Tunable Memory Size <=PGA_AGGREGATE_TARGET 如果Untunable Memory Size > PGA_AGGREGATE_TARGET 那么Oracle会超出PGA_AGGREGATE_TARGET 的设置至于超出部分怎么分配内存，我没有仔细研究过不过估计跟workarea executions - optimal有关，应该是optimal size的一个函数，实际上对于单个进程
Untunable Memory Size > PGA_AGGREGATE_TARGET 这种状况是不可能出现的。
至于进程PGA你可以查询:V$PROCESS 我想以下字段是你关心的
PGA_USED_MEM, PGA_ALLOC_MEM AND PGA_MAX_MEM
Lyra：
pga_aggregate_target此参数是指你的所有连接可使用的内存总量;每个连接使用的内存包含:此连接的SQL语句中的绑定变量(类似于C程序中的局部变量,所以在Oracle中也称此部分为stack),还有语句的一些句柄信息;以及此语句排序操作所用到的内存区。
其中消耗内存最大的就是排序区;其中排序区在使用时申请,使用完毕即释放.因此你所需要的排序区可如下估算:单个连接需要4M*同时运行的最大连接数。而pga_aggregate_target的大小只要略大于此估算即可.
那么排序区就等于是eygle指的tunable memory size,其他部分如bind variable和handle都属于untunable memory size了?不知理解的对否!
BTW:9i上为何设置了pga_aggregate_target后,sort_area_size就不起作用了呢?那么是否可以只设置二者之一?另外:我的pga_aggregate_target=0,怎么还是要为连接分配pga值呢?
Lyra：
关于以tunable memory size和untunable memory size的划分PGA,我没有研究过.当pga_aggregate_target的值非0时,表示你指定了预期PGA的最大值,这样sort_area_size就不起作用了,这是Oracle9i才有的特性,其目的就是不让用户去管理更为底层的PGA分配,而是让用户指定个整体宏观值,而让Oracle自己去管理底层的分配,这样管理起来就方便,而且性能也会提高,毕竟系统会比用户更了解自己对内存的需求。这可能是Oracle的一个发展趋势;另外为了保持向后兼容,也保留了sort_area_size参数,这只有在pga_aggregate_target=0的情况下才有意义。
lyra老兄说的4M是怎么算出来的,我怎么查看v$process处理用户的单个process 只有400k左右呢,还要加上哪些内存使用呢？
Lyra：
答:这是比较合理的上限值;当然如果你的内存较小,这个值也可估计的小些。你所看到的400k仅是些进程所消耗的要内存;象排序区等是不包含在内的。
Xzh2000：
在9i以后的版本,没有必要将PGA搞的那么清楚，只需要分析v$pgastat，然后调整PGA就OK啦。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-999831/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-999831/
扫一扫
544
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
