ORACLE 数据字典之V$SQL_WORKAREA_ACTIVE

首先看一下V$SQL_WORKAREA_ACTIVE的结构：
[quote]SQL> desc V$SQL_WORKAREA_ACTIVE
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_HASH_VALUE NUMBER
SQL_ID VARCHAR2(13)
WORKAREA_ADDRESS RAW(4)
OPERATION_TYPE VARCHAR2(40)
OPERATION_ID NUMBER
POLICY VARCHAR2(12)
SID NUMBER
QCINST_ID NUMBER
QCSID NUMBER
ACTIVE_TIME NUMBER
WORK_AREA_SIZE NUMBER
EXPECTED_SIZE NUMBER
ACTUAL_MEM_USED NUMBER
MAX_MEM_USED NUMBER
NUMBER_PASSES NUMBER
TEMPSEG_SIZE NUMBER
TABLESPACE VARCHAR2(30)
SEGRFNO# NUMBER
SEGBLK# NUMBER[/quote]

通过这张视图，可以看到当前SQL_WORKAREA_ACTIVE的相关操作如sort，hash join等及内存信息，以下就是一个客户生产库的状态
[quote]SQL> SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
2 operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2;
3 4 5 6
SID OPERATION ESIZE MEM MAX MEM PASS TSIZE
---------- ---------------------------------------- ---------- ---------- ---------- ---------- ----------
2501 HASH-JOIN 2233 1221 1221 0
3966 GROUP BY (HASH) 3276 321 321 0[/quote]