SID OPERATION ESIZE MEM
---------- ---------------------------------------- ---------- ----------
MAX MEM PASS TSIZE
---------- ---------- ----------
2125 HASH-JOIN 1733 1040
1040 0
当前执行的sql语句使用过的会话id为2125，操作类型为hash-join，这个工作区域估计占用大小为1733bytes，实际为1040，过去占用的pga内存为1040，无one-pass记录，没有产生额外的磁盘分配。
6、v$sql_workarea
另外我们可以通过workarea_address与v$sql_workarea查看哪些分配了最大内存消耗的操作：
eg：
SQL> SELECT *
2 FROM (SELECT workarea_address, operation_type, policy, estimated_optimal_size
3 FROM V$SQL_WORKAREA
4 ORDER BY estimated_optimal_size DESC)
5 WHERE ROWNUM <= 10;
WORKAREA_ADDRESS OPERATION_TYPE POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA36F8 GROUP BY (HASH) AUTO
37981184
C0000004C64B5148 HASH-JOIN AUTO
31110144
C0000004C7FCDF20 HASH-JOIN AUTO
31110144
WORKAREA_ADDRESS OPERATION_TYPE POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2AA3690 HASH-JOIN AUTO
28812288
C0000004C74E2C90 HASH-JOIN AUTO
20407296
C0000004E25AE498 HASH-JOIN AUTO
20373504
WORKAREA_ADDRESS OPERATION_TYPE POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004DE610500 HASH-JOIN AUTO
20373504
C0000004D13C43F8 HASH-JOIN AUTO
12705792
C0000004C7FCDF88 SORT (v2) AUTO
9923584
WORKAREA_ADDRESS OPERATION_TYPE POLICY
---------------- ---------------------------------------- --------------------
ESTIMATED_OPTIMAL_SIZE
----------------------
C0000004E2CE30D8 HASH-JOIN AUTO
9205760
10 rows selected.
可以看到，第一个是work area的地址为C0000004E2AA36F8，进行了group by的操作，使用的是自动work area 分配方式，该操作，估计需要消耗工作区域大小为37981184byte。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ (^ω^) +++++++