9.69 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:
-
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 |
---|---|---|
|
|
Hash value of the SQL statement that is currently being executed |
|
|
SQL identifier of the SQL statement that is currently being executed |
|
|
Time when the execution of the SQL currently executed by this session started |
|
|
SQL execution identifier (see |
|
|
Address of the work area handle. This is the primary key for the view. |
|
|
Type of operation using the work area. Can include values such as |
|
|
A unique number used to identify the operation in the execution plan. This identifier can be joined to |
|
|
Sizing policy for this work area ( |
|
|
Session identifier |
|
|
Query coordinator instance identifier. Along with |
|
|
Query coordinator session identifier. This is the same as the SID if the work area is allocated by a serial cursor. |
|
|
Average time this work area is active (in microseconds) |
|
|
Maximum size (in bytes) of the work area as it is currently used by the operation |
|
|
Expected size (in bytes) for this work area. |
|
|
Amount of PGA memory (in bytes) currently allocated on behalf of this work area. This value should range between |
|
|
Maximum memory amount (in bytes) used by this work area |
|
|
Number of passes corresponding to this work area ( |
|
|
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 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. |
|
|
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. |
|
|
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. |
|
|
The ID of the container to which the data pertains. Possible values include:
|
See Also:
-
Oracle Database Concepts for more information about SQL work areas