eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 经典重现 | Blog首页 | What's Mean "internal dummy connection"? »

关于自动PGA管理的进一步探讨
modb.pro

伴随自动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.

 


历史上的今天...
    >> 2016-04-09文章:
    >> 2013-04-09文章:
    >> 2012-04-09文章:
    >> 2009-04-09文章:
    >> 2008-04-09文章:
           数据字典表之:DBA_TABLES

By eygle on 2006-04-09 16:47 | Comments (12) | Internal | 736 |

12 Comments

eygle,那怎么样能突破pga的大小限制呢?谢谢:)

_pga_max_size 可以部分控制阿,通常缺省足够了。

你的应用有特殊需求么?

在10G上测试了一下,10G把global memory bound限制为PGA_AGGREGATE_TARGET的20%,其他的没有变化了。

您好,我的RAM是8G,我的PGA target是1.5G,之前用10.2.0.1.0时PGA大概用300M就不再涨了,升级到10.2.0.3之后,session一直呈上升趋势,PGA也呈上升趋势,好像一直往上涨,现在已经700M了,很担心。

没关系,没达到Target一般不用太担心。

不过我怀疑是有些SQL的执行计划改变了,导致PGA耗用过高。
你可以查查v$tempseg_usage视图

可不可以把pga变成动态设置的呢?就是用程序动态调整

盖大哥
你好!我想请教一个问题。在你的《oracle数据库性能优化》书中,在自动PGA管理(13章)中,提到并发情况下PGA自动管理的效果,书中没有讲到如何进行并发设置的,我想了解一下怎样进行并发设置呢?

Oracle自行管理,无需进行参数设置。

我的 _pga_max_size=487157760
没有进行手工调整为什么也变成了400m?
10.2.0.1.0

还想问个事情,10g是自动pga管理内存的,如果我修改sort_area_size的话这个值还会不会起作用?

9i的自动PGA管理中,sort_area_size就不再生效了。

你确认你的_pga_max_size没有调整过?
你的PGA设置的是多少?

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磁盘,和这个有关系吗.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com