Auto plan cache

Background information

Choosing an execution plan involves many factors, such as statistics, different join orders, and query transformations. The optimization time varies for different query statements. For some SQL statements, the query optimization time can be a large part of the total running time. If these statements run frequently, the long optimization time increases the system load. Caching and reusing execution plans reduces the optimization time for each run. This improves query performance, lowers the database load, and increases throughput capacity.

For many other queries, the optimization time is small, but the running time is heavily influenced by the execution plan. Different parameter values in an SQL statement can correspond to different optimal execution plans. In some scenarios, MySQL retrieves actual data from the engine based on parameter values for further optimization.

Using a fixed execution plan for these queries may not improve query response time or reduce load overhead. It can even cause performance regression.

PolarDB for MySQL provides the auto plan cache feature to improve the query performance of SQL statements with long optimization times, reduce system load, and avoid performance regression from fixed execution plans. The auto plan cache feature has three modes: AUTO, DEMAND, and ENFORCE. You can set the loose_plan_cache_type parameter to one of these modes to cache execution plans in the plan cache. This reduces optimization time and improves query performance. A cached execution plan is automatically invalidated if the statistics of a referenced table change or if a Data Definition Language (DDL) operation is performed on a referenced table.

Prerequisites

Parameters

You can set the parameters in the following table in the PolarDB console. For more information, see Set cluster and node parameters.

Interface descriptions

• dbms_sql.add_plan_cache(schema, query): Caches the execution plan of a specified SQL statement in the plan cache.

  When the loose_plan_cache_type parameter is set to DEMAND, you can use this built-in stored procedure to cache the execution plan of a specified SQL statement. The following example shows how:

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

  After this statement is executed, the execution plan is cached for any SQL statement that matches the SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ? template.

• dbms_sql.display_plan_cache_table(): Displays information about the tables referenced in the current plan cache. The following example shows how:

  CALL dbms_sql.display_plan_cache_table()\G

  The following result is returned:

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

  The following table describes the parameters.

  • SCHEMA_NAME: The name of the schema where the referenced table resides.

  • TABLE_NAME: The name of the referenced table.

  • REF_COUNT: The number of times the table is referenced in the plan cache.

  • VERSION: The version of the referenced table in the plan cache.

  • VERSION_TIME: The time when the current version of the table was referenced.

• dbms_sql.delete_sharing_by_rowid(row_id): Deletes the execution plan of a specified SQL statement.

  The row_id is the row ID of the execution plan stored in the mysql.sql_sharing table.

  Example

  1. Run the following command to view the execution plan information in the cache.

     SELECT Id, Schema_name, Type, Digest_text FROM mysql.sql_sharing WHERE Type = 'PLAN_CACHE'\G

     The following result is returned:

     *************************** 1. row ***************************
              Id: 1
     Schema_name: test
            Type: PLAN_CACHE
     Digest_text: SELECT * FROM `t_for_plan` WHERE `c1` > ? AND `c1` < ?

     The query result shows that the row_id value is 1.

  2. Delete the execution plan from the previous query.

     CALL dbms_sql.delete_sharing_by_rowid(1);

Get cached information from the plan cache

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

Query performance