ob_sql_work_area_percentage

Description

ob_sql_work_area_percentage specifies the percentage of the SQL work area memory in the total memory of the tenant.

Privilege requirements

• Query variables

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

• Modify variables

  • In the sys tenant, you can directly modify the value of a Global system variable.

  • In a MySQL user tenant, you must have the SUPER or ALTER SYSTEM privilege to modify the value of a Global system variable.

  • In an Oracle user tenant, you must have the ALTER SYSTEM privilege to modify the value of a Global system variable.

Attributes

Usage notes

The SQL work area memory is the memory used by blocking operators such as sorting. You can use the tenant system variable ob_sql_work_area_percentage to control the SQL work area memory. By default, the value of ob_sql_work_area_percentage is 5%. That is, the SQL work area memory is 5% of the total memory of the tenant.

If the number of concurrent requests is large and each request occupies a large amount of SQL work area memory, an out-of-memory error may occur. This error often occurs in scenarios such as UNION, SORT, and GROUP BY. If this error occurs, you can increase the value of the system variable to avoid the error. For example:

obclient> SET GLOBAL ob_sql_work_area_percentage = 10;

References

Common memory issues