Execution plan cache

The execution plan cache is used to reduce the number of times execution plans are generated.

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 cache eviction

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

Automatic eviction

Automatic eviction means that when the memory used by the plan cache reaches the memory upper limit for eviction (i.e., the high watermark for plan eviction), the plans in the plan cache are automatically evicted.

• Conditions that trigger the eviction of execution plans

  At regular intervals (the interval is set by the plan_cache_evict_interval parameter), the system automatically checks the plan caches of different tenants on different servers and determines whether plan eviction is needed. If the memory used by a plan cache exceeds the high watermark for plan eviction set for the tenant, plan cache eviction is triggered.

• Execution plan eviction strategy

  When plan cache eviction is triggered, the least recently used execution plans are evicted first. After some plans are evicted, eviction stops when the memory used by the plan cache reaches the low watermark for plan eviction set for the tenant.

• Configurations related to plan cache eviction

  • plan_cache_evict_interval This parameter is used to set the interval for checking whether execution plans need to be evicted.

  • 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 on a server exceeds 0.9 GB, eviction is triggered. The least recently executed plans are evicted first. Eviction stops when the memory used drops to 0.5 GB. If the eviction speed is slower than the speed of new plan generation, and the plan cache memory usage reaches the absolute upper limit of 1 GB, no new plans will be added to the plan cache until eviction reduces the memory usage to below 1 GB.

Manual eviction

Manual eviction refers to forcibly deleting plans from the plan cache. The SQL statement is as follows:

ALTER SYSTEM FLUSH PLAN CACHE [[SQL_identifier] [database_list] tenant_list] [GLOBAL];

The parameters are described as follows:

• SQL_identifier: Used to specify the SQL. Format: sql_id = 'xxx'. If this parameter is not specified, the plan cache of all SQL statements is cleared.

  Note

  When using the SQL_identifier parameter in the system tenant sys, you must specify tenant_list. When using it in a common tenant, you cannot specify the tenant_list parameter.

• database_list: Used to specify the database. If this parameter is not specified, all plan caches are cleared.

  Note

  When using the database_list parameter in the system tenant sys, you must specify tenant_list. When using it in a common tenant, you cannot specify the tenant_list parameter.

• tenant_list: Used to specify the tenant scope. Format: tenant = 'tenant_name, tenant_name....'. If tenant_list is not specified, the plan cache of all tenants is cleared. Otherwise, only the plan cache of the specified tenants is cleared.

  Note

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

• GLOBAL: If GLOBAL is not specified, only the plan cache on the local server is cleared. Otherwise, the plan cache on all servers where the tenant resides is cleared.

Plan cache refresh

Execution plans in the 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, regenerating optimal plans, and adding the new plans to the plan cache.

The following scenarios cause execution plans to become invalid and require a refresh:

• When the schema of tables involved in the SQL changes (for example, adding indexes, deleting or adding columns), the corresponding execution plan in the plan cache will be refreshed.

• When statistics are recollected for tables involved in the SQL, the corresponding execution plan in the plan cache will be refreshed.

Plan cache use control

Plan cache use can be controlled through system variables and hints.

• System variable control

  When ob_enable_plan_cache is set to TURE, SQL requests use the plan cache; when set to FALSE, SQL requests do not use the plan cache. The default is TURE. This system variable can be set at the session or global level.

• Hint control

  • Use the hint /*+ USE_PLAN_CACHE(NONE)*/ to indicate not to use the plan cache.
  • Use the hint /*+USE_PLAN_CACHE(DEFAULT)*/ to indicate to use the plan cache.

Plan cache views

Execution plan related views include:

• (G)V$OB_PLAN_CACHE_STAT displays the overall status of the plan cache of the current tenant on the current (or all) OBServer nodes.

• (G)V$OB_PLAN_CACHE_PLAN_STAT displays the status of each cached object in the plan cache of the current tenant on the current (or all) OBServer nodes.

• (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN displays the physical execution plans cached in the plan cache on the current (or all) OBServer nodes.

For detailed parameter information about the views, see Plan cache view.