Skip Headers
Oracle® Database Reference
10g Release 2 (10.2)

Part Number B14237-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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 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:

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
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 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 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 KB) 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 KB) 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(31) 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.

See Also:

Oracle Database Performance Tuning Guide for more information on how to monitor SQL work areas