Note
This view is available starting with V2.2.77.
Purpose
This view displays some overall information about SQL Workarea on all OBServer nodes, such as the maximum available memory, current used memory, and current hold memory. You can use this view to obtain the usage of Workarea in the current tenant, including the memory utilization.
Columns
Column |
Type |
Nullable |
Description |
|---|---|---|---|
| MAX_WORKAREA_SIZE | bigint(20) | NO | The maximum workarea memory, which is determined by the parameter. The unit is bytes. |
| WORKAREA_HOLD_SIZE | bigint(20) | NO | The current hold memory size of Workarea. The unit is bytes.
Note>The actual usage cannot be obtained from the memory management module. Therefore, only the hold value can be obtained. |
| MAX_AUTO_WORKAREA_SIZE | bigint(20) | NO | The estimated maximum available memory size when auto is enabled. The unit is bytes. You can simply consider that the maximum available memory is calculated using the formula max_work_area_size - hold + total_mem_used, which represents the maximum memory size managed by auto in the current Workarea situation. |
| MEM_TARGET | bigint(20) | NO | The target size of available memory for the current Workarea. The unit is bytes. The difference between this value and max_auto_work_area is that this value is a certain proportion of the max_auto_work_area value. |
| TOTAL_MEM_USED | bigint(20) | NO | The current auto memory usage size. The unit is bytes. This size is statistically calculated by the SQL automatic memory management module, not the actual usage size. |
| GLOBAL_MEM_BOUND | bigint(20) | NO | The maximum available memory size in auto mode. The unit is bytes. |
| DRIFT_SIZE | bigint(20) | NO | The change in the current required memory size. The unit is bytes. It is mainly used to calculate the fluctuation value. When the fluctuation reaches a certain range, it will automatically trigger a recalculation of the global bound size. |
| WORKAREA_COUNT | bigint(20) | NO | The number of registered operator profiles. |
| MANUAL_CALC_COUNT | bigint(20) | NO | The number of times the global bound size is calculated without being triggered periodically. |
| TENANT_ID | bigint(20) | NO | The tenant ID. |
| SVR_IP | varchar(46) | NO | The server IP address. |
| SVR_PORT | bigint(20) | NO | The server port number. |
Sample query
Query the SQL Workarea information of all tenants on all OBServer nodes in the sys tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_WORKAREA_MEMORY_INFO;
The query result is as follows:
+-------------------+--------------------+------------------------+------------+----------------+------------------+------------+----------------+-------------------+-----------+----------------+----------+
| MAX_WORKAREA_SIZE | WORKAREA_HOLD_SIZE | MAX_AUTO_WORKAREA_SIZE | MEM_TARGET | TOTAL_MEM_USED | GLOBAL_MEM_BOUND | DRIFT_SIZE | WORKAREA_COUNT | MANUAL_CALC_COUNT | TENANT_ID | SVR_IP | SVR_PORT |
+-------------------+--------------------+------------------------+------------+----------------+------------------+------------+----------------+-------------------+-----------+----------------+----------+
| 53687091 | 0 | 53687091 | 53687091 | 0 | 6710886 | -8000000 | 0 | 289359 | 1001 | 11.xxx.xxx.xxx | 28825 |
| 214748364 | 0 | 214748364 | 214748364 | 0 | 26843545 | 0 | 0 | 1144 | 1002 | 11.xxx.xxx.xxx | 28825 |
| 53687091 | 0 | 53687091 | 53687091 | 0 | 6710886 | -8000000 | 0 | 289572 | 1003 | 11.xxx.xxx.xxx | 28825 |
| 214748364 | 0 | 214748364 | 214748364 | 0 | 26843545 | 0 | 0 | 870 | 1004 | 11.xxx.xxx.xxx | 28825 |
| 53687091 | 0 | 53687091 | 53687091 | 0 | 6710886 | -8000000 | 0 | 280662 | 1005 | 11.xxx.xxx.xxx | 28825 |
| 214748364 | 0 | 214748364 | 214748364 | 0 | 26843545 | 2417152 | 0 | 316 | 1006 | 11.xxx.xxx.xxx | 28825 |
+-------------------+--------------------+------------------------+------------+----------------+------------------+------------+----------------+-------------------+-----------+----------------+----------+
6 rows in set