分类: Oracle
2007-03-07 14:18:15
WORKAREA_SIZE_POLICY指的就是PGA的管理,PGA的管理分手工和自动(PGA里非UGA的部分,比如sorting,hashing),以下参数在手工管理时用到SORT_AREA_SIZE,SORT_AREA_RETAINED_SIZE,sort结束后保留的大小,比如 SORT_AREA_SIZE是1M, SORT_AREA_RETAINED_SIZE是512K,则一个大的SORT可能用完1M,SORT 完后超过512K的会交换到TEMP SEGMENT里.
HASH_AREA_SIZE server process用来在内存里存hash table的大小.
大致来看,如果workarea_size_policy=manual那么pga_aggregate_target将会不被使用,而是使用响应的sort_area_size,hash_area_size等参数.;如果workarea_size_policy=auto的话那么就会使用pga_aggregate_target而不使用其他的sort_area_size,hash_area_size等参数.
if ( 数据库版本 >= 10gR1 )
then
if (workarea_size_policy=auto)
then
sort area size 无效
else
sort area size 有效
end if
else -- 数据库版本 < 10gr1
if ( shared server 连接方式 )
then
sort area size 有效
else -- dedicated server连接方式
if (workarea_size_policy=auto)
then
sort area size 无效
else
sort area size有效
end if
end if
end if
SQL> create table wwm as select * from all_objects;
SQL> alter session set sort_area_size=524288;
session xxx memory表示现在这个时刻用了多少,session xxx memory max 表示最高峰时用了多少
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4
---------------------------------------------------------
0 recursive calls
0 db block gets
420 consistent gets
0 physical reads
0 redo size
2178018 bytes sent via SQL*Net to client
22768 bytes received via SQL*Net from client
2038 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
30547 rows processed
SQL> alter session set sort_area_size=1048576; 同上
SQL> alter session set sort_area_size=1048576000; 同上
自动管理时sort_area_size不起作用.
SQL> alter session set workarea_size_policy=manual;
SQL> alter session set sort_area_size = 65536; --64K
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4
----------------------------------------------------------
0 recursive calls
22 db block gets
420 consistent gets
513 physical reads
0 redo size
2178018 bytes sent via SQL*Net to client
22768 bytes received via SQL*Net from client
2038 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
30547 rows processed
============退出,重进
SQL> alter session set sort_area_size=1048576;
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4;
----------------------------------------------------------
0 recursive calls
4 db block gets
420 consistent gets
424 physical reads
0 redo size
2178018 bytes sent via SQL*Net to client
22768 bytes received via SQL*Net from client
2038 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
30547 rows processed
还有排序操作在DISK上
SQL> set autotrace off
==============退出,重进
SQL> alter session set sort_area_size=1048576000;
SQL> set autotrace traceonly statistics;
SQL> select * from wwm order by 1,2,3,4;
----------------------------------------------------------
0 recursive calls
0 db block gets
420 consistent gets
0 physical reads
0 redo size
2178018 bytes sent via SQL*Net to client
22768 bytes received via SQL*Net from client
2038 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
30547 rows processed
以上分别用64K,1M,1G来测试,分配1G并不代表就要切实分配1G的MEM出去。而是说你有权利用到1G,相反,设置64K并不代表你的session只能用64k,一个SQL可能有多个SORT,指的是每个SORT 的限额.