通过修改workarea_size_policy加快磁盘hash

http://blog.csdn.net/robinson1988/article/details/6055560

在OLAP中，由于数据量巨大，业务逻辑复杂，有时候确实无法避免磁盘HASH，磁盘SORT等操作

SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> alter session set hash_area_size = 2100000000;

Session altered.

SQL> alter session set sort_area_size = 2100000000;

Session altered.

Oracle有个限制，每个进程分配的最大内存不能够操作2G 。在workarea自动管理中，每个进程的work area不能超过1G

所以当你尝试分配2G的hash_area给 这个进程，会报错

SQL> alter session set hash_area_size = 2147483648;
alter session set hash_area_size = 2147483648
                                   *
ERROR at line 1:
ORA-02017: integer value required

SQL> alter session set hash_area_size = 2147483647;

Session altered.

SQL> select username,inst_id,sid,serial#,event,p1,p2,p3,sql_id,sql_child_number
  2  from gv$session where username='ADWU_OPTIMA_LA11' and osuser='luobi';

USERNAME                INST_ID        SID    SERIAL# EVENT                                  P1         P2         P3 SQL_ID        SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11              2       4885      11759 direct path write temp              20012      71053         64 6qsuc8mafy20m                1

请注意观察 p3 ，当设置 workarea 手工管理的时候，一次能写入64个block，相比以前的 7个block来说写入速度加快9倍

那么现在大家也该明白了，这个SQL的主要性能问题就是在于在 在workarea 自动管理模式下磁盘HASH 的时候一次只能写入7个block
而设置workarea 手工管理，可以让磁盘HASH 一次写入64个block。