Note
This view is available starting with V2.2.77.
Purpose
This view displays information about the SQL workarea on the current OBServer node, including the maximum available memory, current used memory, and current held memory. You can use this view to understand the usage of workarea in the current tenant, such as 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 held memory size of the workarea. The unit is bytes.
Note>The actual usage cannot be obtained from the memory management module, so only the held 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. It can be simply considered that the maximum available memory is calculated using the formula max_work_area_size - hold + total_mem_used, indicating the maximum memory size managed by auto under the current workarea conditions. |
| MEM_TARGET | bigint(20) | NO | The target size of available memory for the current workarea. The unit is bytes. The difference from 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 value is statistics by the SQL automatic memory management module, not the actual usage size. |
| GLOBAL_MEM_BOUND | bigint(20) | NO | The global maximum available memory size in auto mode. The unit is bytes. |
| DRIFT_SIZE | bigint(20) | NO | The change in current memory demand. 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 outside of scheduled triggers. |
| 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 the current OBServer node in the sys tenant.
obclient [oceanbase]> SELECT * FROM oceanbase.V$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 | 289392 | 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 | 289605 | 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 | 280701 | 1005 | 11.xxx.xxx.xxx | 28825 |
| 214748364 | 0 | 214748364 | 214748364 | 0 | 26843545 | 0 | 0 | 316 | 1006 | 11.xxx.xxx.xxx | 28825 |
+-------------------+--------------------+------------------------+------------+----------------+------------------+------------+----------------+-------------------+-----------+----------------+----------+
6 rows in set