Note
This view was introduced in OceanBase Database V2.2.77.
Purpose
The GV$SQL_WORKAREA view displays the workarea statistics of all operators subject to automatic SQL memory management.
Columns
| Column | Type | Nullable? | Description | |
|---|---|---|---|---|
| ADDRESS | RAW(8) | NO | The address of the handle corresponding to the SQL statement. At present, this column is not supported and its value is NULL by default. |
|
| HASH_VALUE | NUMBER | NO | The hash value of the SQL statement. At present, this column is not supported and its value is NULL by default. |
|
| DB_ID | NUMBER(38) | NO | The ID of the database to which the connection of the SQL request belongs.
Note
|
|
| SQL_ID | VARCHAR2(32) | NO | The unique identifier of the SQL statement. | |
| CHILD_NUMBER | NUMBER | NO | The number of cursors. At present, this column is not supported and its value is NULL by default. |
|
| WORKAREA_ADDRESS | RAW(8) | NO | The address of the workarea. | At present, this column is not supported and its value is NULL by default. |
| OPERATION_TYPE | VARCHAR2(160) | NO | The type of the operator using the workarea, such as Sort, Hash Join, or Group by. | |
| OPERATION_ID | NUMBER | NO | The unique identifier of the operator in the plan tree. | |
| POLICY | VARCHAR2(40) | NO | The policy for the workarea. Valid values: MANUALAUTO |
|
| ESTIMATED_OPTIMAL_SIZE | NUMBER | NO | The estimated memory size in bytes required for executing the operator in the workarea in optimal mode. | |
| ESTIMATED_ONEPASS_SIZE | NUMBER | NO | The estimated memory size in bytes required for executing the operator in one pass mode. | |
| LAST_MEMORY_USED | NUMBER | NO | The size in bytes of the memory used by the cursor in the last execution. | |
| LAST_EXECUTION | VARCHAR2(40) | NO | Indicates whether the workarea selects the optimal, one pass, or multipasses mode in the last execution of the cursor. | |
| LAST_DEGREE | NUMBER | NO | The degree of parallelism (DOP) of the last execution. | |
| TOTAL_EXECUTIONS | NUMBER | NO | The total number of executions in the workarea. | |
| OPTIMAL_EXECUTIONS | NUMBER | NO | The number of executions in optimal mode. | |
| ONEPASS_EXECUTIONS | NUMBER | NO | The number of executions in one pass mode. | |
| MULTIPASSES_EXECUTIONS | NUMBER | NO | The number of executions in multipasses mode. | |
| ACTIVE_TIME | NUMBER | NO | The average active duration of the workarea, in seconds. | |
| MAX_TEMPSEG_SIZE | NUMBER | NO | The maximum size in bytes of the temporary disk space used by the workarea. The value of this column is NULL if no temporary space is used. |
|
| LAST_TEMPSEG_SIZE | NUMBER | NO | The size of the temporary disk space used by the workarea in the last execution. The value of this column is NULL if no temporary space is used. |
|
| CON_ID | NUMBER | NO | The ID of the tenant. | |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. | |
| SVR_PORT | NUMBER(38) | NO | The port number of the OBServer node. |
Sample query
Query the workarea statistics of all operators subject to automatic SQL memory management on all OBServer nodes.
obclient [SYS]> SELECT * FROM SYS.GV$SQL_WORKAREA WHERE ROWNUM <= 1\G
The query result is as follows:
*************************** 1. row ***************************
ADDRESS: NULL
HASH_VALUE: NULL
DB_ID: 201001
SQL_ID: DC0ECB93D5AA3E26623B4E6CF05B6E74
CHILD_NUMBER: 461
WORKAREA_ADDRESS: NULL
OPERATION_TYPE: PHY_HASH_JOIN
OPERATION_ID: 1
POLICY: AUTO
ESTIMATED_OPTIMAL_SIZE: 2604288
ESTIMATED_ONEPASS_SIZE: 412725
LAST_MEMORY_USED: 109388
LAST_EXECUTION: OPTIMAL
LAST_DEGREE: 1
TOTAL_EXECUTIONS: 17
OPTIMAL_EXECUTIONS: 17
ONEPASS_EXECUTIONS: 0
MULTIPASSES_EXECUTIONS: 0
ACTIVE_TIME: 394
MAX_TEMPSEG_SIZE: 0
LAST_TEMPSEG_SIZE: 0
CON_ID: 1004
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
1 row in set