PolarDB:Auto plan cache

How it works

The query optimizer selects an execution plan based on statistics, join orders, and query transformations. For queries where optimization time is a significant fraction of total execution time, caching the plan eliminates repeated optimizer work for the same query pattern.

Auto Plan Cache intercepts queries that match configured thresholds and stores their execution plans. Subsequent queries matching the same parameterized pattern use the cached plan directly, skipping the optimization phase.

Cached plans are automatically invalidated when:

• Statistics on referenced tables change

• DDL operations are performed on referenced tables

Not all queries benefit from plan caching. For queries where the optimal plan depends heavily on parameter values—where different values lead to very different optimal plans—a fixed cached plan may degrade performance. Use AUTO mode to cache selectively, or DEMAND mode to control which queries are cached.

Supported versions

Auto Plan Cache requires one of the following:

• PolarDB for MySQL 8.0.1, revision version 8.0.1.1.33 or later

• PolarDB for MySQL 8.0.2, revision version 8.0.2.2.12 or later

Choose a caching mode

Set the mode using the loose_plan_cache_type parameter.

OFF (default) disables plan caching entirely.

Configure parameters

Configure the following parameters on the Parameters page of the PolarDB console. For more information, see Configure cluster and node parameters.

AUTO mode cache conditions

A query's execution plan is cached only when both conditions are met:

1. Total execution time >= loose_auto_plan_cache_time_threshold

2. Optimization time / total execution time >= loose_auto_plan_cache_pct_threshold

The cached plan takes effect only after the query has been executed at least loose_auto_plan_cache_count_threshold times.

Use stored procedures

CALL dbms_sql.add_plan_cache("schema_name", "query");

CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");

CALL dbms_sql.display_plan_cache_table()\G

*************************** 1. row ***************************
 SCHEMA_NAME: test
  TABLE_NAME: t_for_plan
   REF_COUNT: 1
     VERSION: 0
VERSION_TIME: 2023-03-10 17:21:35.605264

CALL dbms_sql.delete_sharing_by_rowid(row_id);

Query the plan cache

Execution plans are stored in the SQL Sharing module. Query the INFORMATION_SCHEMA.SQL_SHARING table to inspect cached plans:

SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA
FROM INFORMATION_SCHEMA.SQL_SHARING
WHERE json_contains(REF_BY, '"PLAN_CACHE"') OR json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

The EXTRA field contains a PLAN_CACHE_INFO object showing the referenced tables, their versions, and the number of cache hits.

End-to-end example using DEMAND mode

1. Create a test table:

   CREATE TABLE t_for_plan AS
     WITH RECURSIVE t(c1, c2, c3) AS (
       SELECT 1, 1, 1
       UNION ALL
       SELECT c1+1, c1 % 50, c1 % 200 FROM t WHERE c1 < 1000
     )
   SELECT c1, c2, c3 FROM t;
   
   CREATE INDEX i_c1_c2 ON t_for_plan(c1, c2);

2. Set the caching mode to DEMAND. Use either method:

   • In the PolarDB console, go to the Parameters page, set loose_plan_cache_type to DEMAND, then reconnect to the database.

   • In the current session:

     SET plan_cache_type = demand;

3. Register the query for caching:

   CALL dbms_sql.add_plan_cache("test", "SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10");

4. Run the query:

   SELECT * FROM t_for_plan WHERE c1 > 1 AND c1 < 10;

5. Verify the cached plan:

   SELECT TYPE, REF_BY, SQL_ID, SCHEMA_NAME, DIGEST_TEXT, PLAN_ID, PLAN, PLAN_EXTRA, EXTRA
   FROM INFORMATION_SCHEMA.SQL_SHARING
   WHERE json_contains(REF_BY, '"PLAN_CACHE"') OR json_contains(REF_BY, '"PLAN_CACHE(DEMAND)"')\G

   Sample output:

   *************************** 1. row ***************************
          TYPE: SQL
        REF_BY: ["PLAN_CACHE(DEMAND)"]
        SQL_ID: 9jrvksr3wjux6
   SCHEMA_NAME: test
   DIGEST_TEXT: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?
       PLAN_ID: NULL
          PLAN: NULL
    PLAN_EXTRA: NULL
         EXTRA: {"TRACE_ROW_ID":1}
   *************************** 2. row ***************************
          TYPE: PLAN
        REF_BY: ["PLAN_CACHE"]
        SQL_ID: 9jrvksr3wjux6
   SCHEMA_NAME: test
   DIGEST_TEXT: NULL
       PLAN_ID: 08xftakma6pm6
          PLAN: /*+ INDEX(`t_for_plan`@`select#1` `i_c1_c2`) */
    PLAN_EXTRA: {"access_type":["`t_for_plan`:range"]}
         EXTRA: {"PLAN_CACHE_INFO":{"tables":[`test`.`t_for_plan`], "versions":[0], "hits": 0}}

   The PLAN_CACHE_INFO in the EXTRA field shows the referenced table, its version, and the number of cache hits.

Performance data

A stress test on an 8-core, 32 GB cluster with 25 tables of 4 million rows each. The test query was SELECT id FROM sbtestN WHERE k IN(...) with an IN list of 20 values, run under both the Prepared Statement (PS) protocol and the non-PS protocol.

• The following figure shows the performance test results for the PS protocol.

• The following figure shows the performance test results for the non-PS protocol.

Auto Plan Cache improved throughput by more than 50% for both protocols compared to the baseline (OFF mode).

These results are from a workload where optimization time is a significant fraction of total query time. Workloads dominated by execution time rather than optimization time will see smaller gains. If your queries are short-running or their optimal plans vary significantly by parameter value, measure the impact before enabling the feature in production.