PolarDB:Auto plan cache

Background information

The selection of an execution plan depends on many factors such as statistics, join orders, and query transformations. Optimization time varies for different query statements. The optimization time for some SQL statements may be very long in comparison to the overall execution time. If such SQL statements are executed several times, the system load increases due to their long optimization time. Caching and reusing the execution plans of SQL statements can reduce the optimization time when SQL statements are executed. This improves query performance, reduces database loads, and boosts throughput.

In contrast, some query statements have very short query optimization time. Their execution time depends heavily on execution plans. Parameter values in SQL statements also determine optimal execution plans. In some scenarios, MySQL optimizes actual data obtained from the engine based on parameter values.

If the preceding query statements use fixed execution plans, their query response time and load overheads are not significantly optimized. The query performance may even be degraded.

To improve the query performance of SQL statements that have very long optimization time, reduce system loads, and avoid query performance degradation caused by fixed execution plans when SQL statements are executed, PolarDB for MySQL launches the auto plan cache feature. The auto plan cache feature supports three modes: AUTO, DEMAND, and ENFORCE. You can select one by setting the loose_plan_cache_type parameter to cache execution plans of SQL statements in the plan cache to reduce optimization time and improve query performance. When the statistics of the tables referenced in the execution plans cached in the plan cache change or DDL operations are performed on the tables referenced by the execution plans cached in the plan cache, the cached execution plans automatically become invalid.

Prerequisites

Parameters

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

Stored procedures

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

  If the loose_plan_cache_type parameter is set to DEMAND, you can use this built-in stored procedure to cache the execution plans of the specified SQL statement in the plan cache. Example:

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

  After the preceding statements are executed, when your executed SQL statements meet the SELECT * FROM t_for_plan WHERE c1 > ? AND c1 < ? template, the execution plans of the SQL statements are cached in the plan cache.

• dbms_sql.display_plan_cache_table(): displays information about tables referenced in the plan cache. Example:

  CALL dbms_sql.display_plan_cache_table()\G

  Sample result:

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

  Parameters in the stored procedure:

  • 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 references of the table in the plan cache.

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

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

• dbms_sql.delete_sharing_by_rowid(row_id): deletes the execution plans of the specified SQL statement.

  row_id: the row ID value for the execution plan stored in the mysql.sql_sharing table.

  Examples

  1. Execute the following SQL statement to query the execution plans cached in the plan cache:

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

     Sample result:

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

     The result indicates that the row_id value is 1.

  2. Delete the execution plans obtained in the preceding query.

     CALL dbms_sql.delete_sharing_by_rowid(1);

Query information of 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

Performance data