Overview
Displays the workarea statistics for all operators executed by SQL automatic memory management.
Columns
Field |
Type |
Nullable |
Description |
|---|---|---|---|
| ADDRESS | varbinary(8) | NO | Handle address corresponding to the SQL statement. No value specified. |
| HASH_VALUE | bigint(0) | NO | Hash value of the statement |
| DB_ID | bigint(20) | NO | The database ID to which the connection of this SQL request belongs. |
| SQL_ID | varchar(32) | NO | Unique SQL identifier of the SQL statement |
| CHILD_NUMBER | bigint(0) | NO | Number of Cursors |
| WORKAREA_ADDRESS | varbinary(8) | NO | Work Area Address |
| OPERATION_TYPE | varchar(40) | NO | Workarea operator type, such as Sort, Hash Join, and Group by |
| OPERATION_ID | bigint(20) | NO | Unique Identifier for Identifiers in the Plan Tree |
| POLICY | varchar(10) | NO | WorkArea policies:
|
| ESTIMATED_OPTIMAL_SIZE | bigint(20) | NO | Estimated memory size for operator execution under optimal conditions, in bytes |
| ESTIMATED_ONEPASS_SIZE | bigint(20) | NO | In a one-pass scenario, the operator estimates the memory size in bytes. |
| LAST_MEMORY_USED | bigint(20) | NO | The memory size used by the cursor in the last execution, in bytes. |
| LAST_EXECUTION | varchar(10) | NO | Was the workarea set to optimal, one pass, or multi passes during the last execution? |
| LAST_DEGREE | bigint(20) | NO | Last Execution Parallelism |
| TOTAL_EXECUTIONS | bigint(20) | NO | Total Number of Workarea Uses |
| OPTIMAL_EXECUTIONS | bigint(20) | NO | Number of executions in the optimal scenario |
| ONEPASS_EXECUTIONS | bigint(20) | NO | Number of One Pass Scenario Executions |
| MULTIPASSES_EXECUTIONS | bigint(20) | NO | Number of Executions in Multi Passes Scenarios |
| ACTIVE_TIME | bigint(20) | NO | Average Time of Workarea Being Active, in Seconds |
| MAX_TEMPSEG_SIZE | bigint(20) | NO | Maximum temporary disk space for workarea, in bytes. If NULL, no temporary space is used. |
| LAST_TEMPSEG_SIZE | bigint(20) | NO | The temporary disk space used by the workspace in the last execution. If NULL, no temporary space was used. |
| CON_ID | bigint(20) | NO | Tenant ID |
| SVR_IP | varchar(46) | NO | Server IP Address |
| SVR_PORT | bigint(20) | NO | Server Port Number |
Sample query
View the workarea statistics of all operators executed by SQL automatic memory management on all OBServer nodes.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$SQL_WORKAREA LIMIT 1\G
The query result is as follows:
*************************** 1. row ***************************
ADDRESS: NULL
HASH_VALUE: NULL
DB_ID: 201001
SQL_ID: DC0ECB93D5AA3E26623B4E6CF05B6E74
CHILD_NUMBER: 337
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: 460
MAX_TEMPSEG_SIZE: 0
LAST_TEMPSEG_SIZE: 0
CON_ID: 1002
SVR_IP: 172.xx.xx.xx
SVR_PORT: 2882
1 row in set