how to use the plan cache feature - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL provides the plan cache feature to cache the execution plans of SQL statements. When you execute SQL statements that share the same SQL pattern, AnalyticDB for MySQL uses the cached execution plan. This reduces SQL compilation and optimization time and improves system query performance. This topic describes how to enable the plan cache feature and provides usage examples.

Prerequisites

The minor version of the cluster must be 3.1.10.0 or later.

Note

To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

Background information

When the number of concurrent queries is high, the time required for SQL compilation and optimization can become a system performance bottleneck. The plan cache feature caches the execution plans of SQL statements. When you execute subsequent SQL statements that share the same SQL pattern, the cached execution plan is used directly. This reduces SQL compilation and optimization time and improves system query performance.

The plan cache feature is applicable only to high-concurrency point query scenarios. This feature is not recommended for non-point query scenarios.

Enable or disable the plan cache feature

By default, the plan cache feature is disabled. You can execute the following commands to enable or disable the plan cache feature.

Enable the plan cache feature:
```
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;
```
Disable the plan cache feature:
```
SET ADB_CONFIG O_SPC_SCOPE=NONE;
```

Examples

Assume that you have the following high-concurrency point query request:

SELECT * FROM tbl0 WHERE col0 = ?;

Enable the plan cache feature:
```
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;
```
Execute an SQL query statement. The execution plan for this statement is then cached.
```
SELECT * FROM tbl0 WHERE col0 = 666;
```
When you execute a statement with the same SQL pattern, the execution plan cached in the previous step is used automatically. This reduces SQL compilation and optimization time.
```
SELECT * FROM tbl0 WHERE col0 = 777;
```