ob_sql_work_area_percentage

Description

The ob_sql_work_area_percentage variable specifies the percentage of memory in the tenant that is allocated for SQL work areas.

Privilege requirements

• Query variables

  Users in the sys tenant and all users in other tenants can execute the SHOW VARIABLES statement or view the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (Oracle-compatible mode) or the information_schema.GLOBAL_VARIABLES view (MySQL-compatible mode) to query the value of a global system variable.

• Modify variables

  • Users in the sys tenant can modify the value of a global system variable directly.

  • MySQL users must have the SUPER or ALTER SYSTEM privilege to modify the value of a global system variable.

  • For V4.x, Oracle users must have the ALTER SYSTEM privilege to modify the value of a global system variable starting from V4.2.0.

Attributes

Considerations

The SQL work area is the memory used by blocking operators such as sorting. The value of the ob_sql_work_area_percentage tenant-level variable specifies the percentage of tenant memory allocated for SQL work areas. The default value is 5%, which means that work area memory = tenant memory * ob_sql_work_area_percentage (default 5%).

If the concurrent request volume is high and each request uses a large amount of work area memory, an error indicating insufficient work area memory may occur. Common scenarios where this error occurs are UNION, SORT, and GROUP BY. You can avoid this error by appropriately increasing the value of the system variable. For example:

obclient> SET GLOBAL ob_sql_work_area_percentage = 10;

References

FAQ