可以看到,为了执行测试语句,为该session分配的PGA为4956780个字节,其中UGA为3677528个字节,大约3.5M。同时可以看出,执行完测试语句以后,oracle就把该session的PGA空间回收了(PGA从4956780下降到369796,而UGA从3677528下降到77008),顺带提一下,在8i中分配了PGA以后是不会回收的,也就是说session pga memory始终等于session pga memory max,而9i以后的PGA的分配方式发生了改变,从而能够在分配PGA以后还可以再回收一部分内存。结合上面为SQL语句所分配的3M的工作区,可以知道,UGA中的其他空间占用大约0.5M。而SQL工作区占整个PGA大小大约为64%,从这个方面也可以看出,SQL工作区是PGA中最占空间、也是最重要的部分。
Sess#4:
SQL> @d:/pga_by_process.sql
输入 sid 的值: 7
原值 7: and b.sid= &sid
新值 7: and b.sid= 7
PGA Used PGA Alloc PGA Max
---------- ---------- ----------
253932 382664 4969648
可以看到,这几个视图查出来的PGA的大小基本都是一致的。
我们继续测试,从sess#2可以看出,如果要让该SQL语句完全在内存中完成,需要大约67MB的PGA空间。根据5%的原理倒算,可以知道这个时候的pga_aggregate_target应该大于1340MB(67/0.05)。于是,我们设置1500MB,来看看是不是确实进行optimal了。顺便提醒一下,并不是说你的电脑得有超过1500MB的物理内存你才可以设置1500M的pga_aggregate_target,事实上pga_aggregate_target是按需分配的,不象SGA,一旦设置就占着内存,不用也得占着。也就是说是PGA是随着对内存需求的增长而不断增长的。我测试的机器上只有1GB的物理内存,但做测试时完全可以将pga_aggregate_target设置5GB,甚至更高的10GB。
Sess#5,我们将pga_aggregate_target设置为1500MB:
SQL> alter system set pga_aggregate_target=1500M;
Sess#1:
SQL> select a.* from pga_test a,pga_test b where rownum<600000 order by 1,2,3,4,5,6,7,8;
Sess#2:
SQL> @d:/pga_by_hashvalue.sql
输入 hashvalue 的值: 2656983355
原值 12: and a.hash_value = &hashvalue
新值 12: and a.hash_value = 2656983355
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<600000 order by 1,2,3,4,5,6,7,8
SORT AUTO 65.765625
73.9873047 2.90039063 OPTIMAL
我们可以看到,该SQL语句确实完全在内存里完成了(LAST_EXECUTION为“OPTIMAL”)。同时,实际的“期望尺寸”始终会小于optimal(65.765625<73.9873047),也符合前面说的第二条规则。
我们继续测试,看看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.05为250MB,为何该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缺省值为200M(209715200/1024/1024),而_smm_max_size缺省值为100MB(上面的查询结果中显示的单位是KB)。而每个session的PGA最多只能使用_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_size为5%*pga_aggregate_target。
如果_pga_max_size小于等于5%*pga_aggregate_target,则_smm_max_size为50%*_pga_max_size。
有些资料上说,可以通过修改_pga_max_size来突破这个100MB的限制。真的是这样吗?我们来测试。Sess#5,修改参数_pga_max_size为600MB:
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中再次执行测试语句,应该可以使用超过100MB的SQL工作区了。我们来看测试结果。
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