V$SQL_WORKAREA_ACTIVE

Oracle 11gR1

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 WORKAREA_ADDRESS to access the definition of that work area. If a work area spills to disk, then this view contains information for the temporary segment created on behalf of this work area.

The last three columns are included to enable joining V$SQL_WORKAREA_ACTIVE with V$TEMPSEG_USAGE to retrieve more information on this temporary segment.

You can use this view to answer the following:

· What are the top 10 largest work areas currently allocated in the 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?

· What are the active work areas that have spilled to disk?

Column	Datatype	Description
SQL_HASH_VALUE	NUMBER	Hash value of the SQL statement that is currently being executed
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement that is currently being executed
SQL_EXEC_START	DATE	Time when the execution of the SQL currently executed by this session started
SQL_EXEC_ID	NUMBER	SQL execution identifier (see V$SQL_MONITOR)
WORKAREA_ADDRESS	RAW(4 | 8)	Address of the work area handle. This is the primary key for the view.
OPERATION_TYPE	VARCHAR2(20)	Type of operation using the work area (SORT, HASH JOIN, GROUP BY, BUFFERING, BITMAP MERGE, or 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(6)	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 hundredths of a second)
WORK_AREA_SIZE	NUMBER	Maximum size (in bytes) of the work area as it is currently used by the operation
EXPECTED_SIZE	NUMBER	Expected size (in bytes) for this work area. EXPECTED_SIZE is set on behalf of the operation by the memory manager. Memory can be over-allocated when 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	Amount of PGA memory (in bytes) currently allocated on behalf of this work area. This value should range between 0 and WORK_AREA_SIZE.
MAX_MEM_USED	NUMBER	Maximum memory amount (in bytes) used by this work area
NUMBER_PASSES	NUMBER	Number of passes corresponding to this work area (0 if running in OPTIMAL mode)
TEMPSEG_SIZE	NUMBER	Size (in bytes) of the temporary segment used on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.
TABLESPACE	VARCHAR2(30)	Tablespace name for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.
SEGRFNO#	NUMBER	Relative file number within the tablespace for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.
SEGBLK#	NUMBER	Block number for the temporary segment created on behalf of this work area. This column is NULL if this work area has not (yet) spilled to disk.

Note:

1. SELECT sql_hash_value, sql_id, operation_type, policy, sid, work_area_size, actual_mem_used, tempseg_size, tablespace

FROM V$SQL_WORKAREA_ACTIVE;

SQL_HASH _VALUE	SQL_ID	OPERATION_TYPE	POLICY	SID	WORK_AREA _SIZE	ACTUAL_MEM _USED	TEMPSEG_ SIZE	TABLESPACE
1679125978	g22rnbdk1avfu	HASH-JOIN	AUTO	2196	17917952	10025984	1217396736	TEMP
175263300	g56j680574mk4	HASH-JOIN	AUTO	2129	3726336	3577856	NULL	NULL
576637872	gx7gk1nj5xkxh	LOAD WRITE BUFFERS	AUTO	2326	0	0	NULL	NULL
1721781002	3qr082tma0ksa	GROUP BY (SORT)	AUTO	2289	278528	278528	NULL	NULL
3993155112	f7j93nrr05dj8	SORT (v2)	AUTO	2231	821248	821248	NULL	NULL
2154551539	6cuagwa06rq7m	GROUP BY (SORT)	AUTO	2229	311296	311296	NULL	NULL
3139891090	f56vtt2xkdvwk	HASH-JOIN	AUTO	1793	12495872	15559680	NULL	NULL

Oracle dynamic performance views