Plan cache

The optimizer uses a 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.

A plan cache features a typical key-value pair structure, where the key is a parameterized SQL string and the 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 eviction

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

Automatic eviction

Plans are automatically evicted when the memory used by the plan cache reaches the upper limit, which is the threshold to start plan eviction.

• Conditions that trigger the eviction of execution plans

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

• Execution plan eviction policy

  The most infrequently accessed plans are 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 eviction

  • plan_cache_evict_interval specifies the interval for checking whether it is necessary to evict execution plans.

  • ob_plan_cache_percentage specifies the percentage of the tenant memory that can be used by the plan cache. The maximum memory available for the plan cache is calculated by using the following formula: Maximum memory available for the plan cache (absolute value of the upper limit) = Maximum memory available for the tenant * ob_plan_cache_percentage/100

  • ob_plan_cache_evict_high_percentage specifies the percentage of the memory used by the plan cache to the maximum memory available for the plan cache to trigger plan eviction. The used memory size to trigger plan eviction is calculated by using the following formula: Used memory size (upper limit to trigger plan eviction) = Maximum memory available for the plan cache * ob_plan_cache_evict_high_percentage/100

  • ob_plan_cache_evict_low_percentage specifies the percentage of the memory used by the plan cache to the maximum memory available for the plan cache to stop plan eviction. This is the lower limit to stop plan eviction. This threshold is calculated by using the following formula: Used memory size (lower limit to stop plan eviction) = Absolute value of the maximum memory available for the plan cache * ob_plan_cache_evict_low_percentage/100

Assume that the memory size of a tenant is 10 GB and the following parameter settings apply:

• ob_plan_cache_percentage＝10

• ob_plan_cache_evict_high_percentage＝90

• ob_plan_cache_evict_low_percentage＝50

Then:

• Absolute value of the maximum memory available for the plan cache = 10 GB × 10/100 = 1 GB

• Upper limit to trigger plan eviction = 1 GB × 90/100 = 0.9 GB

• 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]

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

• The format of tenant_list is tenant = 'tenant_name, tenant_name....'. Plan caches of all tenants are cleared if tenant_list is not specified. Otherwise, only plan caches of specified tenants are cleared.

  Note

  Only the sys tenant can specify tenant_list. Other tenants cannot specify this parameter, meaning that they can only clear their own plan caches. If the sys tenant does not specify tenant_list, the plan caches of all tenants are cleared.

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

Refreshing of the 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 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 caches

You can configure system variables and hints to control the plan cache.

• Control by using 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. The default value is TURE. You can set the level of this system variable to SESSION or GLOBAL.

• Control by using hints

  • The hint statement /+USE_PLAN_CACHE(NONE)/ specifies not to use the plan cache.
  • The hint statement /+USE_PLAN_CACHE(DEFAULT)/ specifies to use the plan cache.

Plans not supported by the 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 a plan cache.

Plan cache-related views

Plan cache-related views include:

• (G)V$OB_PLAN_CACHE_STAT

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

• (G)V$OB_PLAN_CACHE_PLAN_STAT

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

• (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN

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

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