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

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

原文地址:http://www.eygle.com/archives/2006/04/oracle_pga_internal.Html

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航