V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_ACTIVE contains an instantaneous view of the work areas currently allocated by the system. You can join this view against V$SQL_WORKAREA on the WORKAREA_HANDLE to access the definition of that work area.

You can use this view to answer the following:

• What are the top 10 largest work areas currently allocated in my system?

• What percentage of memory is over-allocated (EXPECTED_SIZE < ACTUAL_MEM_USED) and under-allocated (EXPECTED_SIZE > ACTUAL_MEM_USED)?

• What are the active work areas using more memory than what is expected by the memory manager?

  Column	Datatype	Description
  WORKAREA_ADDRESS	RAW(4)	Address of the workarea handle. This is the "primary key" for the view.
  OPERATION_TYPE	VARCHAR2(20)	Type of operation using the work area (for example, SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, BITMAP CREATE)
  OPERATION_ID	NUMBER	A unique number used to identify the operation in the execution plan. This identifier can be joined to V$SQL_PLAN to locate the operation that uses this work area.
  POLICY	VARCHAR2(10)	Sizing policy for this work area (MANUAL or AUTO)
  SID	NUMBER	Session identifier
  QCINST_ID	NUMBER	Query coordinator instance identifier. Along with QCSID, enables you to uniquely identify the query coordinator.
  QCSID	NUMBER	Query coordinator session identifier. This is the same as the SID if the work area is allocated by a serial cursor.
  ACTIVE_TIME	NUMBER	Average time this work area is active (in centi-seconds)
  WORK_AREA_SIZE	NUMBER	Maximum size of the work area as it is currently used by the operation
  EXPECTED_SIZE	NUMBER	Expected size (in KB) for this work area. EXPECTED_SIZE is set on behalf of the operation by the memory manager. Memory can be over-allocated when the WORK_AREA_SIZE has a higher value than EXPECTED_SIZE. This can occur when the operation using this work area takes a long time to resize it.
  ACTUAL_MEM_USED	NUMBER	The amount of PGA memory (in KB) currently allocated on behalf of this work area. This value should range between 0 and WORK_AREA_SIZE.
  MAX_MEM_USED	NUMBER	The maximum memory amount (in KB) used by this work area
  NUMBER_PASSES	NUMBER	The number of passes corresponding to this work area (0 if running in OPTIMAL mode)