Note
This view is available starting with V2.2.77.
Purpose
This view displays the statistics of all work areas executed on all nodes of the current tenant, such as the number of times full in-memory processing occurred between 1M and 2M, the number of one-pass executions, etc.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| LOW_OPTIMAL_SIZE | NUMBER | NO | The minimum memory size for the workarea in optimal mode. |
| HIGH_OPTIMAL_SIZE | NUMBER | NO | The maximum memory size for the workarea in optimal mode. |
| OPTIMAL_EXECUTIONS | NUMBER | NO | The number of executions in optimal mode within the lowest and highest intervals. |
| ONEPASS_EXECUTIONS | NUMBER | NO | The number of one-pass executions within the lowest and highest intervals. |
| MULTIPASSES_EXECUTIONS | NUMBER | NO | The number of multi-pass executions within the lowest and highest intervals. |
| TOTAL_EXECUTIONS | NUMBER | NO | The total number of executions for the workarea. |
| CON_ID | NUMBER | NO | The tenant ID. |
| SVR_IP | VARCHAR2(46) | NO | The server IP address. |
| SVR_PORT | NUMBER | NO | The server port number. |
Sample query
Query the statistics of all work areas executed on all nodes of 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