Plan cache

The optimizer uses plan cache to avoid repeatedly generating execution plans for the same SQL query.

OceanBase Database caches previously generated execution plans that can be directly used the next time the corresponding SQL statements are executed. This policy, referred to as "Optimize Once", optimizes the execution process by avoiding the repeated generation of the same plans.

Plan cache features a typical key-value pair, where key is the parameterized SQL string and value is the execution plan corresponding to the SQL statement.

Each tenant has an independent plan cache on each server to cache SQL plans processed on this server. The plan cache of OceanBase Database supports the cache of three types of SQL execution plans: local plans, remote plans, and distributed plans. An SQL statement may have all three types of execution plans cached at the same time, depending on the data to be accessed.

By default, for each type of execution plan for an SQL statement, OceanBase Database only keeps the plan generated for the first execution of the SQL statement. In some cases, however, the parameter value of an SQL statement may affect the selection of its execution plan. As a result, the plan cache reserves different execution plans for different parameter values accordingly, so that the best plan is used for each execution.

Plan cache eviction

Plan cache eviction refers to the removal of execution plans from the plan cache to reduce memory usage. OceanBase Database supports automatic and manual eviction of plan cache.

Automatic eviction

A plan is automatically evicted when the memory usage of the plan cache reaches the upper limit, which is the threshold to start plan eviction.

• Conditions that trigger the eviction of an execution plan

  The plan cache of different tenants on different servers is checked automatically at the time interval specified by plan_cache_evict_interval for plan eviction when necessary. The eviction is triggered if the memory usage of the plan cache exceeds the upper limit set for the tenant.

• Execution plan eviction policy

  The most infrequently accessed plan is evicted first, and the eviction stops when the memory used by the plan cache is less than the lower limit set for plan eviction in the tenant.

• Configurations of plan cache eviction

Assume that the memory size of a tenant is 10 GB, with following parameter values:

• ob_plan_cache_percentage = 10

• ob_plan_cache_evict_high_percentage = 90

• ob_plan_cache_evict_low_percentage = 50

Then:

• The absolute value for the maximum memory usage for plan cache = 10 GB * 10 / 100 = 1 GB

• The upper limit to trigger plan eviction = 1 GB * 90/100 = 0.9 GB

• The lower limit to stop plan eviction = 1 GB * 50/100 = 0.5 GB

The calculation results show that when the plan cache of a tenant exceeds 0.9 GB, the plan eviction is triggered. The most infrequently accessed plan is evicted first. The eviction stops when the memory used by the plan cache is less than 0.5 GB. If new plans are being generated at a speed faster than the eviction of old plans, and the absolute memory usage of the plan cache reaches 1 GB, then the plan cache stops taking in new plans until it has evicted enough plans to reduce the occupied memory size to a value smaller than 1 GB.

Manual eviction

Manual eviction refers to the forced removal of plans from the plan cache. Currently, OceanBase Database allows you to clear the plan cache of a specific server of a tenant, or all plan caches of all servers of different tenants by running the following command:

obclient>ALTER SYSTEM FLUSH PLAN CACHE [tenant_list] [global] 
/*The format of tenant_list is tenant = 'tenant1, tenant2, tenant3....'*/

tenant_list and global are optional fields. The following instruction describes how they work:

• Plan caches of all tenants are cleared if tenant_list is not specified. Otherwise, only plan caches of specified tenants are cleared.

• If global is not specified, only the plan cache of the specific server is cleared. Otherwise, plan caches of all servers associated to the tenant are cleared.

Refreshing of plan cache

Execution plans in a plan cache may become invalid for various reasons. In this case, you need to refresh the invalid plans by deleting them from the plan cache, generating new optimal plans, and adding the new plans to the plan cache.

An execution plan may become invalid and needs to be refreshed in the following scenarios:

• When the corresponding SQL statement relates to changes in the schema, such as the addition of indexes and the deletion or addition of columns.

• When the corresponding SQL statement relates to the recollection of table statistics. OceanBase Database collects statistics during the major compaction of data, all plans in the plan cache are refreshed after each major compaction.

Use control of plan cache

Use control of plan cache is achieved by configuring system variables and hints.

• Control by system variables

  The plan cache is enabled or disabled for SQL queries when you set the value of the ob_enable_plan_cache variable to TURE or FALSE. Default value: TURE. You can set the level of this system variable to SESSION or GLOBAL.

• Hint control

Plans not supported by plan cache

• An execution plan that occupies more than 20 MB of memory is not added to the plan cache.

• A distributed execution plan that involves multiple tables is not added to the plan cache.

View of plan cache

Plan cache-related views include:

• (g)v$plan_cache_stat

  Records the status of each plan cache. Each plan cache has a record entry in this view.

• (g)v$plan_cache_plan_stat

  Records details of all execution plans in a plan cache and the general execution statistics of each plan.

• (g)v$plan_cache_plan_explain

  Records execution plans of an SQL statement in the plan cache.

For more information about parameters of views, see Plan cache-related views.