ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PGA自动管理原理深入分析及性能调整(四)

PGA自动管理原理深入分析及性能调整(四)

原创 Linux操作系统 作者:hanson 时间:2019-06-28 16:39:04 0 删除 编辑

我们继续测试,看看SQL工作区的“期望尺寸”是否真的不能超过100MB。为此,需要设置5%*

pga_aggregate_target>100MB,因此pga_aggregate_target最少要大于2G,我们设置5GB

Sess#5,我们将pga_aggregate_target设置为5GB

SQL> alter system set pga_aggregate_target=5G;

              Sess#1,注意,为了能够占用更多的PGA,这时的SQL语句已经把where条件修改了:

SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;

              Sess#5,找到该语句的hash值:

SQL> select hash_value from v$sql where sql_text='select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8';

HASH_VALUE

----------

3008669403

              Sess#2

SQL> /

输入 hashvalue 的值:  3008669403

原值   12:   and a.hash_value = &hashvalue

新值   12:   and a.hash_value = 3008669403

SQL_TEXT

--------------------------------------------------------------------------------

OPERATION_TYPE                           POLICY                  Used MB

---------------------------------------- -------------------- ----------

Est Opt MB Est OnePass MB LAST_EXECUTION       LAST_TEMPSEG_SIZE

---------- -------------- -------------------- -----------------

select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8

SORT                                     AUTO                  87.265625

137.232422     3.87109375 1 PASS                       127926272

可以看到,optimal尺寸已经超过100MB很多了,但是实际分配的“期望尺寸”却只有88MB左右。而5G*0.05250MB,为何该SQL用不了呢?这其实是由两个隐藏参数决定的,分别是_pga_max_size_smm_max_size。我们来看一下这两个参数的含义和缺省值:

Sess#5

SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');

KSPPINM        KSPPSTVL    KSPPDESC

-------------- ---------- -----------------------------------------------

_pga_max_size  209715200   Maximum size of the PGA memory for one process

_smm_max_size  102400      maximum work area size in auto mode (serial)

我们可以看到_pga_max_size缺省值为200M209715200/1024/1024),而_smm_max_size缺省值为100MB(上面的查询结果中显示的单位是KB)。而每个sessionPGA最多只能使用_pga_max_size的一半,也就是100MB

当你修改参数pga_aggregate_target的值时,Oracle系统会根据pga_aggregate_target_pga_max_size这两个值来自动修改参数_smm_max_size。具体修改的规则是:

Ø       如果_pga_max_size大于5%*pga_aggregate_target,则_smm_max_size5%*pga_aggregate_target

Ø       如果_pga_max_size小于等于5%*pga_aggregate_target,则_smm_max_size50%*_pga_max_size

有些资料上说,可以通过修改_pga_max_size来突破这个100MB的限制。真的是这样吗?我们来测试。Sess#5,修改参数_pga_max_size600MB

SQL> show parameter pga

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

pga_aggregate_target                 big integer 5368709120

SQL> alter system set "_pga_max_size"=600M;

我们将_pga_max_size的值设置为600M,其一半就是300MB,已经超过5%*pga_aggregate_target(即250MB)了。所以这两者的较小值为250M,如果这时我们在sess#1中再次执行测试语句,应该可以使用超过100MBSQL工作区了。我们来看测试结果。

Sess#1

SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;

Sess#2

SQL> /

输入 hashvalue 的值:  3008669403

原值   12:   and a.hash_value = &hashvalue

新值   12:   and a.hash_value = 3008669403

 

SQL_TEXT

--------------------------------------------------------------------------------

OPERATION_TYPE                           POLICY                  Used MB

---------------------------------------- -------------------- ----------

Est Opt MB Est OnePass MB LAST_EXECUTION       LAST_TEMPSEG_SIZE

---------- -------------- -------------------- -----------------

select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8

SORT                                     AUTO                  87.265625

137.232422     3.87109375 1 PASS                       127926272

我们看到,“期望尺寸”仍然是大约88MB,并没有突破100MB的限制。其中的问题就在于参数

_smm_max_size 上。我们来看这个时候该参数值是多少:

Sess#5

SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');

KSPPINM        KSPPSTVL    KSPPDESC

-------------- ---------- -----------------------------------------------

_pga_max_size  629145600   Maximum size of the PGA memory for one process

_smm_max_size  102400      maximum work area size in auto mode (serial)

可以看到参数_smm_max_size值仍然是100MB。实际上,这也是一个对 “期望尺寸”的限制参数。这里可以看到“期望尺寸”不能超过100MB。这时,我们只要简单的执行:

Sess#5

SQL> alter system set pga_aggregate_target=5G;

SQL> select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm in ('_pga_max_size','_smm_max_size');

KSPPINM        KSPPSTVL    KSPPDESC

-------------- ---------- -----------------------------------------------

_pga_max_size  629145600   Maximum size of the PGA memory for one process

_smm_max_size  262144      maximum work area size in auto mode (serial)

我们可以看到,只要设置一下pga_aggregate_target,就会按照前面所说的规则重新计算并设置_smm_max_size的值,该参数修改后的值为250MB。这个时候我们重复上面的测试:

Sess#1

SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;

Sess#2

SQL> /

输入 hashvalue 的值:  3008669403

原值   12:   and a.hash_value = &hashvalue

新值   12:   and a.hash_value = 3008669403

SQL_TEXT

--------------------------------------------------------------------------------

OPERATION_TYPE                           POLICY                  Used MB

---------------------------------------- -------------------- ----------

Est Opt MB Est OnePass MB LAST_EXECUTION       LAST_TEMPSEG_SIZE

---------- -------------- -------------------- -----------------

select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8

SORT                                     AUTO                 137.195313

154.345703     4.09179688 OPTIMAL

这时,我们看到,“期望尺寸”为138MB左右,终于超过了100MB。如果我们再次将参数_smm_max_size人为的降低到100MB,则“期望尺寸”又将不能突破100MB了。我们来看试验。

Sess#5

SQL> alter system set "_smm_max_size"=102400;

Sess#1

SQL> select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8;

Sess#2

SQL> /

输入 hashvalue 的值:  3008669403

原值   12:   and a.hash_value = &hashvalue

新值   12:   and a.hash_value = 3008669403

SQL_TEXT

--------------------------------------------------------------------------------

OPERATION_TYPE                           POLICY                  Used MB

---------------------------------------- -------------------- ----------

Est Opt MB Est OnePass MB LAST_EXECUTION       LAST_TEMPSEG_SIZE

---------- -------------- -------------------- -----------------

select a.* from pga_test a,pga_test b where rownum<1300000 order by 1,2,3,4,5,6,7,8

SORT                                     AUTO                  87.265625

137.232422     3.87109375 1 PASS                       127926272

可以看到,结果正如我们所预料的。由此,得出我们重要的结论,就是在非并行方式下,“期望尺寸”为min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size),而不是很多资料上所说的不是很严密的min(5%*pga_aggregate_target,50%*_pga_max_size)oracle当然是不推荐我们修改这两个隐藏参数的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9842/viewspace-410383/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-04-14

  • 博文量
    54
  • 访问量
    44907