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

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

Prerequisites

An AnalyticDB for MySQL cluster that meets both of the following conditions is created:

The cluster is in Data Warehouse Edition (V3.0) or Data Lakehouse Edition (V3.0).
The minor version of the cluster is 3.1.10.0 or later.

Note For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

Background information

When a large number of queries are concurrently executed, an extended period of time required for SQL compilation optimization may become the bottleneck of system performance. In this case, you can use the plan cache feature to cache the execution plan of an SQL statement. When other SQL statements that share the same SQL pattern are executed, the cached execution plan is used to accelerate SQL compilation optimization and improve query performance.

The plan cache feature is suitable only for high-concurrency query scenarios.

Enable or disable the plan cache feature

By default, the plan cache feature is disabled. You can execute the following statements to enable or disable the 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

Suppose that you have the following high-concurrency query request:

SELECT * FROM tbl0 WHERE col0 = ?;

Execute the following statement to enable the plan cache feature:
```
SET ADB_CONFIG O_SPC_SCOPE=POINT_QUERY;
```
Execute the following SELECT statement to cache the execution plan of the statement:
```
SELECT * FROM tbl0 WHERE col0 = 666;
```
Execute another SELECT statement that shares the same SQL pattern. The execution plan that is cached in Step 2 is used. This accelerates SQL compilation optimization.
```
SELECT * FROM tbl0 WHERE col0 = 777;
```