Plan cache overview

OceanBase Database contains a large number of rewrite rules and complex plan generation algorithms, which provide the database with powerful optimization capabilities. However, increased rewrite attempts and more complex plan generation algorithms will inevitably lead to longer plan generation times. In extreme TP scenarios, it may take 1 ms to generate a plan for a SQL statement that precisely matches a single value in the primary key, but only 0.5 ms to execute the plan. In such a scenario, if a plan has to be regenerated for each SQL execution, most of the time will be consumed on plan generation for the statement. Therefore, OceanBase Database has introduced a plan cache mechanism to share execution plans for similar SQL queries.

How plan cache works

When OceanBase Database receives a SQL request, it first uses the fast parser module to perform rapid parameterization on the SQL text. The purpose of rapid parameterization is to replace constant parameters in the SQL text with a wildcard (?), such as replacing SELECT * FROM t1 WHERE c1 = 1 with SELECT * FROM t1 WHERE c1 = ?. Then, OceanBase Database checks the plan cache to see if there is an existing plan available for this parameterized SQL. If an available plan is found, the database directly executes this plan. If no plan is found, the database regenerates an execution plan for this SQL and saves the generated plan in the plan cache for subsequent SQL queries. Typically, retrieving an execution plan directly from the plan cache is at least an order of magnitude faster than regenerating the execution plan. Therefore, using the plan cache can significantly reduce the time needed to obtain an execution plan, thereby reducing the SQL response time.

Adaptive plan cache

Plan cache is enabled by default in OceanBase Database. In AP scenarios, disabling plan cache often yields better performance. However, in HTAP mixed-workload scenarios, disabling plan cache may improve performance for AP-style SQL but can significantly impact TP-style SQL. Therefore, OceanBase Database supports adaptive plan cache in V4.3.5 BP2, enabling plan cache for TP SQL and disabling it for AP SQL to achieve better overall performance.

Adaptive plan cache control

OceanBase Database adds the following parameters for adaptive plan cache in V4.3.5 BP2:

• enable_adaptive_plan_cache: A tenant-level parameter that controls whether the tenant enables adaptive plan cache. For more information, see enable_adaptive_plan_cache.

• _pc_adaptive_min_exec_time_threshold: A tenant-level hidden parameter that controls the minimum execution time threshold for enabling adaptive plan cache. When adaptive plan cache is enabled, only SQL whose execution time exceeds this threshold will use adaptive plan cache. Attributes:

  Attribute	Description
  Type	TIME
  Default	1s
  Range	[0, +∞]
  Modifiable	Yes. You can modify it with ALTER SYSTEM SET. For example: ALTER SYSTEM SET _pc_adaptive_min_exec_time_threshold = '2s';. Note In Oracle-compatible mode, hidden parameters must be enclosed in double quotes in the syntax.
  Requires OBServer restart	No. Changes take effect immediately.

• _pc_adaptive_effectiveness_ratio_threshold: A tenant-level hidden parameter that controls the minimum ratio threshold for enabling adaptive plan cache. When adaptive plan cache is enabled, only SQL whose (plan execution time / plan generation time) is greater than or equal to this threshold will use adaptive plan cache. Attributes:

  Attribute	Description
  Type	INT
  Default	5
  Range	[0, +∞]
  Modifiable	Yes. You can modify it with ALTER SYSTEM SET. For example: ALTER SYSTEM SET _pc_adaptive_effectiveness_ratio_threshold = 6;. Note In Oracle-compatible mode, hidden parameters must be enclosed in double quotes in the syntax.
  Requires OBServer restart	No. Changes take effect immediately.

• _force_enable_plan_tracing: A tenant-level hidden parameter that controls whether plan tracing is enabled when plan cache is disabled. Attributes:

  Attribute	Description
  Type	BOOL
  Default	TRUE, meaning plan tracing is enabled when plan cache is disabled.
  Values	TRUE FALSE
  Modifiable	Yes. You can modify it with ALTER SYSTEM SET. For example: ALTER SYSTEM SET _force_enable_plan_tracing = FALSE;. Note In Oracle-compatible mode, hidden parameters must be enclosed in double quotes in the syntax.
  Requires OBServer restart	No. Changes take effect immediately.

When adaptive plan cache is enabled, an SQL is determined to require plan cache to be disabled if all of the following three conditions are met after execution:

• The SQL execution time exceeds the threshold set by _pc_adaptive_min_exec_time_threshold.
• The ratio of SQL execution time to plan generation time exceeds the threshold set by _pc_adaptive_effectiveness_ratio_threshold.
• The preceding two conditions are met for five consecutive executions.