Querying the V$WORKAREA_ACTIVE View

Querying the V$WORKAREA_ACTIVE View

SELECT TO_NUMBER(DECODE(sid, 65535, null, sid)) sid,

       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;

The output of this query might look like the following:

SID         OPERATION     ESIZE       MEM   MAX MEM  PASS   TSIZE

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

  8   GROUP BY (SORT)       315       280       904     0

  8         HASH-JOIN      2995      2377      2430     1   20000

  9   GROUP BY (SORT)     34300     22688     22688     0

 11         HASH-JOIN     18044     54482     54482     0

 12         HASH-JOIN     18044     11406     21406     1  120000”

Excerpt From: Corporation, Oracle. “Oracle® Database Performance Tuning Guide, 12c Release 1 (12.1).”