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 can avoid hard parsing of SQL statements. When the same SQL request is received, the OceanBase database obtains the analyzed version of the statement from the cache to speed up statement execution.
Parameters related to the plan cache
| Parameter | Description |
|---|---|
| plan_cache_evict_interval | Specifies the interval for obsoleting the execution plan cache. The default value is 30s. |
System variables related to the plan cache
| System variable | Description |
|---|---|
| ob_plan_cache_percentage | Specifies the maximum percentage of the tenant memory for the plan cache. Maximum memory available for the plan cache (absolute value of the maximum memory available) = Tenant memory upper limit × ob_plan_cache_percentage/100. The default value is 5. |
| ob_plan_cache_evict_high_percentage | Specifies the memory usage to trigger plan cache eviction, where the usage is the percentage of the memory occupied by the plan cache with respect to the absolute value of the memory upper limit. Memory size for triggering plan cache eviction (upper threshold for plan cache eviction) = Absolute value of the memory upper limit * ob_plan_cache_evict_high_percentage/100. The default value is 90. |
| ob_plan_cache_evict_low_percentage | Specifies the memory threshold for stopping plan cache eviction. Memory size for stopping plan cache eviction (lower threshold for plan cache eviction) = Absolute value of the memory upper limit * ob_plan_cache_evict_low_percentage/100. The default value is 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. Then:
Absolute value of the maximum memory available 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 occupies more than 0.9 GB, plan cache eviction is triggered for the tenant. The earliest unexecuted plans are obsoleted with priority. When the memory occupied by the plan cache is reduced to 0.5 GB, plan cache eviction is stopped. If plan cache eviction is slower than the generation of new plans, when the memory occupied by the plan cache reaches the absolute value of the memory limit, that is, 1 GB, no new plans will no longer be added to the plan cache until the memory occupied by the plan cache is reduced to 1 GB.