ORACLE 数据字典之V$SQL_WORKAREA_ACTIVE

首先看一下V$SQL_WORKAREA_ACTIVE的结构：

引用

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

通过这张视图，可以看到当前SQL_WORKAREA_ACTIVE的相关操作如sort，hash join等及内存信息，以下就是一个客户生产库的状态

引用

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