Note
This view is introduced since OceanBase Database V2.2.77.
Purpose
The V$OB_SQL_WORKAREA_MEMORY_INFO view displays some general information about the SQL workarea, including the maximum available memory, current memory usage, and currently held memory. In this view, you can learn about the usage of the workareas in the current tenant, such as the memory usage.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| MAX_WORKAREA_SIZE | bigint(20) | NO | The maximum workarea memory, determined by parameters, indicating how much memory can be used (in bytes). |
| WORKAREA_HOLD_SIZE | bigint(20) | NO | The current hold memory size of the workarea (in bytes).
NoteSince the actual usage cannot be obtained from the memory management module, only the hold value can be retrieved. |
| MAX_AUTO_WORKAREA_SIZE | bigint(20) | NO | The estimated maximum available memory size in auto mode (in bytes). It can be simply considered that the maximum available memory is calculated by the formula max_work_area_size - hold + total_mem_used, representing the maximum memory managed by auto under the current workarea situation. |
| MEM_TARGET | bigint(20) | NO | The target size of available memory for the current workarea (in bytes). The difference from max_auto_work_area is that this value is a certain proportion of max_auto_work_area. |
| TOTAL_MEM_USED | bigint(20) | NO | The current auto memory usage size (in bytes). This is the size counted 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 (in bytes). |
| DRIFT_SIZE | bigint(20) | NO | The current change in required memory size (in bytes). Mainly used to count fluctuation values; 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 calculations of the global bound size that are irregularly triggered. |
| TENANT_ID | bigint(20) | NO | The ID of the tenant. |
| SVR_IP | varchar(46) | NO | The IP address of the OBServer node. |
| SVR_PORT | bigint(20) | NO | The port number of the OBServer node. |
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