Oracle 视图 V$SQL_WORKAREA 官方解释，作用，如何使用详细说明
本站中文解释
查看SQL的Workarea使用情况
Oracle视图V$SQL_WORKAREA用于查看SQL语句及其相关的工作区占用情况，以诊断SQL语句的执行性能状况。V$SQL_WORKAREA视图由以下几列组成：
• SQL_ID：SQL语句的唯一标识符；
• CHILD_NUMBER：表示执行相同SQL语句的并发线程子号；
• MEMORY_SIZE：工作区中驻留字节数；
• TEMP_SIZE：临时表空间占用字节数；
• SORT_SIZE：排序缓冲区占用字节数；
• HASH_SIZE：哈希表缓冲区占用的字节数；
• HASH_BLOCKS：哈希表空间占用的块数；
• INTERNAL_AJ_READS：处理内部异步I/O时，所读取的块缓冲区数；
• WAIT_TIME：等待Workarea初始化完成的时间；
• RESERVING_MEMORY：内存是否使用过，如果使用过则为Y，否则为N。
要使用V$SQL_WORKAREA视图，可以执行以下语句：
SELECT * FROM V$SQL_WORKAREA WHERE SQL_ID = ‘SQL ID’;
官方英文解释
V$SQL_WORKAREA displays information about work areas used by SQL cursors. Each SQL statement stored in the shared pool has one or more child cursors that are listed in the V$SQL view. V$SQL_WORKAREA lists all work areas needed by these child cursors; V$SQL_WORKAREA can be joined with V$SQLAREA on (ADDRESS, HASH_VALUE) and with V$SQL on (ADDRESS, HASH_VALUE, CHILD_NUMBER).
You can use this view to find out answers to the following questions:
-
What are the top 10 work areas that require the most cache area?
-
For work areas allocated in
AUTOmode, what percentage of work areas are running using maximum memory?
| Column | Datatype | Description |
|---|---|---|
|
|
|
Address of the parent cursor handle |
|
|
|
Hash value of the parent statement in the library cache. Two columns |
|
|
|
SQL identifier of the parent statement in the library cache |
|
|
|
Number of the child cursor that uses this work area. The columns |
|
|
|
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 ( |
|
|
|
Estimated size (in bytes) required by this work area to execute the operation completely in memory (optimal execution). Derived from either optimizer statistics or previous executions. |
|
|
|
Estimated size (in bytes) required by this work area to execute the operation in a single pass. Derived from either optimizer statistics or previous executions. |
|
|
|
Memory (in bytes) used by this work area during the last execution of the cursor |
|
|
|
Indicates whether this work area runs using |
|
|
|
Degree of parallelism used during the last execution of this operation |
|
|
|
Number of times this work area was active |
|
|
|
Number of times this work area ran in optimal mode |
|
|
|
Number of times this work area ran in one-pass mode |
|
|
|
Number of times this work area ran below the one-pass memory requirement |
|
|
|
Average time this work area is active (in hundredths of a second) |
|
|
|
Maximum temporary segment size (in bytes) created by an instantiation of this work area. This column is NULL if this work area has never spilled to disk. |
|
|
|
Temporary segment size (in bytes) created in the last instantiation of this work area. This column is NULL if the last instantiation of this work area did not spill to disk. |
|
|
|
The ID of the container to which the data pertains. Possible values include:
|
See Also:
-
“V$SQLAREA”
-
“V$SQL”
