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 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.
