The execution plan returned by executing the EXPLAIN statement is an estimated plan. The actual execution plan is in the plan cache. The plan cache prevents hard parsing of SQL statements. When the same SQL request is sent to OceanBase Database, OceanBase Database obtains the execution plan of the statement from the cache and executes the plan to speed up statement execution.
Parameters related to the plan cache
| Parameter | Description |
|---|---|
| plan_cache_evict_interval | The interval for checking whether execution plans need to be evicted. Default value: 1s. |
System variables related to the plan cache
| System variable | Description |
|---|---|
| ob_plan_cache_percentage | Sets the percentage of tenant memory that can be occupied by the plan cache. Maximum memory available for the plan cache (absolute value of the maximum memory available) = Maximum tenant memory × ob_plan_cache_percentage/100. Default value: 5. |
| ob_plan_cache_evict_high_percentage | Sets the percentage of the absolute value of the maximum memory usage to trigger plan cache eviction. Memory threshold that triggers plan cache eviction (upper threshold for plan cache eviction) = Maximum memory × ob_plan_cache_evict_high_percentage/100. Default value: 90. |
| ob_plan_cache_evict_low_percentage | Sets the memory threshold that stops plan cache eviction, which is the percentage of the memory occupied by the plan cache to the maximum memory. Memory threshold that stops plan cache eviction (lower threshold for plan cache eviction) = Maximum memory × ob_plan_cache_evict_low_percentage/100. Default value: 50. |
Assume that the memory size of a tenant is 10 GB, the value of ob_plan_cache_percentage is 10, the value of ob_plan_cache_evict_high_percentage is 90, and the value of ob_plan_cache_evict_low_percentage is 50. In this case, the values of these variables can be used to calculate some thresholds as follows:
Maximum memory for the plan cache = 10 GB × 10 / 100 = 1 GB
Upper threshold for plan cache eviction = 1 GB × 90 / 100 = 0.9 GB
Lower threshold for plan cache eviction = 1 GB × 50 / 100 = 0.5 GB
When the plan cache of the tenant on an OBServer node occupies more than 0.9 GB, plan cache eviction is triggered for the tenant. The earliest unexecuted plans are evicted first. If the memory occupied by the plan cache is reduced to 0.5 GB, plan cache eviction is stopped. 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, 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.
More information
For more information about the plan cache, see Plan cache.