The optimizer uses a plan cache to avoid repeatedly generating execution plans for the same SQL query.
OceanBase AI Database caches previously generated execution plans so that they can be used directly on the next execution of the same SQL statement. This avoids the repeated generation of the same plans, optimizing the execution process. This strategy is called "Optimize Once".
The plan cache features a typical key-value pair structure, where the key is the parameterized SQL string, and the value is the execution plan corresponding to that SQL.
Each tenant has an independent plan cache on each server, caching SQL plans processed on that server. In the plan cache of OceanBase AI Database, SQL execution plans can be divided into three types: local plans, remote plans, and distributed plans. In the plan cache, the same SQL may have all three types of execution plans simultaneously, depending on the data it needs to access.
For each type of execution plan of an SQL statement, OceanBase AI Database retains only the plan generated during the first execution by default. However, in some cases, the parameter values of the same SQL may affect the choice of execution plan. Therefore, the plan cache retains different execution plans for different parameter values as needed, ensuring the most appropriate plan is available for each execution.
Plan cache eviction
Plan cache eviction refers to removing execution plans from the plan cache to reduce its memory usage. OceanBase AI Database supports both automatic and manual eviction methods.
Automatic eviction
Automatic eviction means that when the memory occupied by the plan cache reaches the upper memory limit for plan eviction (i.e., the high water mark for plan eviction), the system automatically evicts plans from the plan cache.
Conditions for triggering plan eviction
The system periodically (at intervals set by the
plan_cache_evict_intervalparameter) automatically checks the plan caches of different tenants on different servers and determines whether plan eviction is needed. If a plan cache's memory usage exceeds the tenant-set high water mark for plan eviction, plan cache eviction is triggered.Plan eviction strategy
When plan cache eviction is triggered, the least recently used execution plans are evicted first. After a portion of plans are evicted, eviction stops when the memory usage of the plan cache reaches the tenant-set low water mark for plan eviction.
Configuration related to plan cache eviction
plan_cache_evict_interval: Sets the interval at which the system checks whether plan eviction is needed.ob_plan_cache_percentage: Sets the percentage of the tenant's memory that the plan cache can use. Formula: Maximum allowable plan cache memory (absolute upper limit) = Tenant memory upper limit * ob_plan_cache_percentage / 100.ob_plan_cache_evict_high_percentage: Sets the percentage of the absolute upper memory limit that triggers plan cache eviction. Formula: Memory size triggering plan cache eviction (high water mark for plan eviction) = Absolute upper memory limit * ob_plan_cache_evict_high_percentage / 100.ob_plan_cache_evict_low_percentage: Sets the percentage of the absolute upper memory limit that stops plan cache eviction. (Low water mark for plan eviction). Formula: Memory value stopping plan eviction (low water mark for plan eviction) = Absolute upper memory limit * ob_plan_cache_evict_low_percentage / 100.
For example, if a tenant's memory size is 10 GB and the parameter values are set as follows:
ob_plan_cache_percentage= 10ob_plan_cache_evict_high_percentage= 90ob_plan_cache_evict_low_percentage= 50
Then the calculations are as follows:
Absolute upper limit of plan cache memory = 10 G * 10 / 100 = 1 GB
High water mark for plan eviction = 1 G * 90 / 100 = 0.9 GB
Low water mark for plan eviction = 1 G * 50 / 100 = 0.5 GB
The calculation results show that when the plan cache usage of a tenant exceeds 0.9 GB on a server, eviction is triggered. The oldest unexecuted plan is evicted first. Eviction stops when the memory usage drops to 0.5 GB. If the eviction rate cannot keep up with the rate at which new plans are generated, no new plans will be added to the plan cache once its memory usage reaches the absolute upper limit of 1 GB. New plans will only be added again after the memory usage falls below 1 GB after an eviction.
Manual plan cache eviction
Manual plan cache eviction forcibly removes 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: Specifies the SQL statement in the formatsql_id = 'xxx'. If this parameter is not specified, the plan cache for all SQL statements is cleared.Note
When using the
SQL_identifierparameter under the system tenantsys, you must also specifytenant_list. Under a user tenant, you cannot specify thetenant_listparameter.database_list: Specifies the database. If this parameter is not specified, the plan cache for all databases is cleared.Note
When using the
database_listparameter under the system tenantsys, you must also specifytenant_list. Under a user tenant, you cannot specify thetenant_listparameter.tenant_list: Specifies the tenant scope. The format oftenant_lististenant = 'tenant_name, tenant_name....'. Iftenant_listis not specified, the plan cache for all tenants is cleared. Otherwise, only the plan cache for the specified tenant is cleared.Note
Only the system tenant
syscan specifytenant_list. Other tenants cannot specify this parameter; they can only clear their own plan cache. If the system tenant does not specifytenant_list, the plan cache for all tenants is cleared.GLOBAL: IfGLOBALis not specified, only the local plan cache is cleared. If specified, the plan cache for the tenant on all servers is cleared.
Plan cache refresh
Execution plans in the plan cache may become invalid for various reasons. In such cases, the invalid plans need to be refreshed by deleting them, re-optimizing and generating new plans, and then adding them back to the plan cache.
The following scenarios can cause execution plans to become invalid and require refreshing:
When the schema of a table involved in an SQL statement changes (for example, adding an index, dropping or adding a column), the corresponding execution plan in the plan cache is refreshed.
When an SQL statement involves rebuilding statistics for a table, the corresponding execution plan in the plan cache is refreshed.
Plan cache usage control
Plan cache usage can be controlled using system variables and hints.
System variable control
When
ob_enable_plan_cacheis set toTRUE, SQL requests can use the plan cache. When set toFALSE, SQL requests do not use the plan cache. The default isTRUE. This system variable can be set at the session level or global level.Hint control
- Use the hint statement
/*+ USE_PLAN_CACHE(NONE)*/to indicate not to use the plan cache. - Use the hint statement
/*+USE_PLAN_CACHE(DEFAULT)*/to indicate to use the plan cache.
- Use the hint statement
Plan cache views
Execution plan-related views include:
(G)V$OB_PLAN_CACHE_STATdisplays the overall status of the plan cache for the current tenant on the current (all) OBServer nodes.(G)V$OB_PLAN_CACHE_PLAN_STATdisplays the status of each cached object in the plan cache for the current tenant on the current (all) OBServer nodes.(G)V$OB_PLAN_CACHE_PLAN_EXPLAINdisplays the physical execution plans cached in the plan cache on the current (all) OBServer nodes.
For detailed parameter information about these views, see Plan cache views.