Scenario
OceanBase Database provides the plan cache mechanism for SQL tuning. To be specific:
- When receiving an SQL statement for the first time, the system hard-parses the SQL statement, calculates costs based on parameter values of the SQL statement, generates a plan, and writes the plan to the plan cache.
- When receiving the SQL statement again, the system searches the plan cache for a plan of the SQL statement, and if a plan is found, executes the SQL statement based on the plan.
Hard parsing consumes a large amount of CPU resources. The plan cache mechanism can improve the performance of SQL statements and the overall database throughput. However, the plan cache mechanism also causes problems. For example, the quality of an SQL statement execution plan depends on the quality of input parameters of the current hard parsing. The parameter values of the SQL statement received for the first time do not necessarily meet the requirements of most requests. In other words, these parameter values are applicable only to a “minority” of scenarios and may lead to poor performance in a “majority” of scenarios. In addition, if the data distribution changes and the original plan is not applicable to the new scenario, performance of the SQL statement also deteriorates.
Case analysis
Example: The plan of an SQL statement changes, causing performance deterioration of the SQL statement and a failure of core business.
View the CPU time trends of the SQL statement:
Performance of the SQL statement in the indexed plan is significantly higher than that in the full-table scan plan that causes an execution exception. We recommend that you bind a correct plan to the SQL statement as soon as possible.
Normal indexed plan:
Abnormal unindexed plan:
Optimization suggestions
Bind a correct plan to the SQL statement as soon as possible.
