Note
This view is introduced since OceanBase Database V2.2.77.
Purpose
The GV$SQL_WORKAREA_HISTOGRAM view displays the cumulative execution statistics of all workareas, such as the total number of fully in-memory executions that consumed 1 MB to 2 MB of memory and the number of executions in one pass mode.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| LOW_OPTIMAL_SIZE | NUMBER | NO | The minimum memory for the workarea in optimal mode. |
| HIGH_OPTIMAL_SIZE | NUMBER | NO | The maximum memory for the workarea in optimal mode. |
| OPTIMAL_EXECUTIONS | NUMBER | NO | The number of executions in optimal mode in the workarea with a memory size between values of LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE. |
| ONEPASS_EXECUTIONS | NUMBER | NO | The number of executions in one pass mode in the workarea with a memory size between values of LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE. |
| MULTIPASSES_EXECUTIONS | NUMBER | NO | The number of executions in multipasses mode in the workarea with a memory size between values of LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE. |
| TOTAL_EXECUTIONS | NUMBER | NO | The total number of executions in the workarea. |
| CON_ID | NUMBER | NO | The ID of the tenant. |
| SVR_IP | VARCHAR2(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | NUMBER | NO | The port number of the OBServer node. |
Sample query
View all statistics of all Workarea executions on all nodes in the current tenant, and display the first 10 records.
obclient[SYS]> SELECT * FROM SYS.GV$SQL_WORKAREA_HISTOGRAM WHERE ROWNUM<= 10;
The query result is as follows:
+------------------+-------------------+--------------------+--------------------+------------------------+------------------+--------+----------------+----------+
| LOW_OPTIMAL_SIZE | HIGH_OPTIMAL_SIZE | OPTIMAL_EXECUTIONS | ONEPASS_EXECUTIONS | MULTIPASSES_EXECUTIONS | TOTAL_EXECUTIONS | CON_ID | SVR_IP | SVR_PORT |
+------------------+-------------------+--------------------+--------------------+------------------------+------------------+--------+----------------+----------+
| 0 | 1048576 | 11899 | 0 | 0 | 11899 | 1004 | 172.xx.xxx.xxx | 2882 |
| 1048576 | 2097152 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 2097152 | 3145728 | 29 | 0 | 0 | 29 | 1004 | 172.xx.xxx.xxx | 2882 |
| 3145728 | 4194304 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 4194304 | 5242880 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 5242880 | 6291456 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 6291456 | 7340032 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 7340032 | 8388608 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
| 8388608 | 9437184 | 29 | 0 | 0 | 29 | 1004 | 172.xx.xxx.xxx | 2882 |
| 9437184 | 10485760 | 0 | 0 | 0 | 0 | 1004 | 172.xx.xxx.xxx | 2882 |
+------------------+-------------------+--------------------+--------------------+------------------------+------------------+--------+----------------+----------+
10 rows in set