Note
This view was introduced in OceanBase Database V2.2.77.
Purpose
The V$SQL_WORKAREA view displays the workarea statistics of all operators previously 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. No value is specified for this column. 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(40) | NO | The type of the operator using the workarea, such as Sort, Hash Join, or Group by. |
| OPERATION_ID | NUMBER(38) | NO | The unique identifier of the operator in the plan tree. |
| POLICY | VARCHAR2(10) | NO | The policy for the workarea. Valid values: MANUALAUTO |
| ESTIMATED_OPTIMAL_SIZE | NUMBER(38) | NO | The estimated memory size in bytes required for executing the operator in the workarea in optimal mode. |
| ESTIMATED_ONEPASS_SIZE | NUMBER(38) | NO | The estimated memory size in bytes required for executing the operator in one pass mode. |
| LAST_MEMORY_USED | NUMBER(38) | NO | The size in bytes of the memory used by the cursor in the last execution. |
| LAST_EXECUTION | VARCHAR2(10) | NO | Indicates whether the workarea selects the optimal, one pass, or multipasses mode in the last execution of the cursor. |
| LAST_DEGREE | NUMBER(38) | NO | The degree of parallelism (DOP) of the last execution. |
| TOTAL_EXECUTIONS | NUMBER(38) | NO | The total number of executions in the workarea. |
| OPTIMAL_EXECUTIONS | NUMBER(38) | NO | The number of executions in optimal mode. |
| ONEPASS_EXECUTIONS | NUMBER(38) | NO | The number of executions in one pass mode. |
| MULTIPASSES_EXECUTIONS | NUMBER(38) | NO | The number of executions in multipasses mode. |
| ACTIVE_TIME | NUMBER(38) | NO | The average active duration of the workarea, in seconds. |
| MAX_TEMPSEG_SIZE | NUMBER(38) | 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(38) | 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(38) | 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 the current OBServer node.
obclient [SYS]> SELECT * FROM SYS.V$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