自动pga管理

Elapsed: 00:00:30.23

SQL> select

sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution, last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text='select distinct * from a where rownum<500000';

SQL_TEXT OPERATION_TYPE ------------------------------------------------ ---------------- select distinct * from a where rownum<500000 GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE ------ ---------------------------- -------------- ----------------- AUTO 19.35 OPTIMAL

可以看到这时候使用了19.35M内存,所有排序操作都在内存中完成,语句的执行时间也从00:01:00.5减少到了00:00:30.23.

上面还提到了expect size不能超过100M,我们来看一下是否能测试出来.

SQL> select name,value/1024/1024/1024 from v$parameter where name='pga_aggregate_target';

NAME VALUE/1024/1024/1024 ----------------------------------- -------------------- pga_aggregate_target 10

SQL> select distinct * from a;

5000000 rows selected

Elapsed: 00:09:35.42

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024, last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text='select distinct * from a’

SQL_TEXT OPERATION_TYPE POLICY ---------------------------- ----------------- -------- select distinct * from a GROUP BY (SORT) AUTO

LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE

---------------------------- ---------------- ------------------- 92.356732 1 PASS 129138688

为什么不能用到5% pga_aggregate_target=0.05*10g=500M的内存呢,这是由于有隐藏参数_pga_max_size来控制,每个session只能用到一半_pga_max_size值大小的内存, _pga_max_size默认的大小为200M.我们看看改了_pga_max_size的效果如何. SQL> alter system set \

System altered

SQL> select distinct * from a;

5000000 rows selected

Elapsed: 00:08:55.23

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024, last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text='select distinct * from a';

SQL_TEXT OPERATION_TYPE POLICY ---------------------------- ----------------- -------- select distinct * from a GROUP BY (SORT) AUTO

LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE ---------------------------- ---------------- ------------------- 198.56346 OPTIMAL

可以看到198.56346的内存被使用并且全部在内存中执行.

再来看一下并发情况下pga自动内存管理的效果. 首先看一下20个并发的情况

SQL> select value/1024/1024 from v$parameter where name = 'pga_aggregate_target';

VALUE/1024/1024 --------------- 50

select sql_text,operation_type,policy,last_memory_used/1024/1024, last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text = ' select distinct * from a ';

SQL_TEXT OPERATION_TYPE -------------------------------------------------- ---------------- select distinct * from a GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE -------- ---------------------------- --------------- ------------------- AUTO 2.328674 1 PASS 6995868

这时用了2.328674/50=4.6% pga_aggregate_target

换成40个并发

SQL> select sql_text,operation_type,policy,last_memory_used/1024/1024, last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text = ' select distinct * from a ';

SQL_TEXT OPERATION_TYPE -------------------------------------------------- ---------------- select distinct * from a GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE -------- ---------------------------- --------------- ------------------- AUTO .52145 15 PASSES 7995392

我们可以看到0.52145/50=0.010429=1%,由于没有足够的内存让40个进程进行onepass操作,所以这里每个进程只用到了1%左右的内存进行multipass操作.

再来看看如果100个并发的情况会怎么样

SQL> select sql_text,operation_type,policy,last_memory_used/1024, last_execution,last_tempseg_size from v$sql l,v$sql_workarea a where l.hash_value=a.hash_value

and sql_text = ' select distinct * from a ';

SQL_TEXT OPERATION_TYPE -------------------------------------------------- ---------------- select distinct * from a GROUP BY (SORT)

POLICY LAST_MEMORY_USED/1024 LAST_EXECUTION LAST_TEMPSEG_SIZE

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

AUTO 595 105 PASSES 110256384

注意,100个并发的情况下已经出现overalloc的情况了,每个进程使用了0.6M,100个进程已经使用超过pga_aggregate_target的值了,这是因为memory bound每3秒才由global memory management更新一次,发布的memory bound还未及时反映到内存使用情况导致.但是这也说明一种情况,就是pga_aggregate_target是可以被突破的,虽然这种情况很少见.我们也可以从v$pgastat中看到overalloc的信息.

NAME VALUE UNIT over allocation count 15483

PGA Advice功能

自从9i release 2开始,oracle提供了pga advice功能,主要通过2张视图 v$pga_target_advice ,v$pga_target_advice_histogram实现建议功能.看一下两张视图的结构

v$pga_target_advice Column PGA_TARGET_FOR_ESTIMATE Datatype NUMBER Description 估计的(bytes) PGA_TARGET_FACTOR NUMBER PGA_TARGET_FOR_ESTIMATE 除以当前pga_aggregate_target得出的值 pga_aggregate_target大小SQL> select * from v$pgastat where NAME like ‘over%’;

ADVICE_STATUS BYTES_PROCESSED ESTD_EXTRA_BYTES_RW VARCHAR2(3) 指示advice开启或关闭 NUMBER NUMBER 所有workarea操作的数据量(bytes) 估计设置pga_aggregate_target为PGA_TARGET_FOR_ESTIMATE时读写byte数 ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER ESTD_OVERALLOC_COUNT

v$pga_target_advice_histogram Column Datatype Description NUMBER 估计内存命中率 估计overalloc次数