This topic describes how to manage and optimize execution plans.
Optimizers generate an execution plan for each SQL statement. In most cases, requests to your database from client applications consist of the same statements with different parameter values. If these statements are parameterized, the statements are the same. Then, you can create a cache based on the parameterized SQL queries to cache execution plans and other information excluding parameters. This cache is called plan cache.
The plan cache is used to ensure the stability of complex queries across version updates. Complex queries include queries that involve multiple JOIN operations. This is done by persistently storing the execution plan of SQL queries in the plan cache, which ensures that the execution plan is preserved during version updates. This process is called execution plan management.PolarDB-X
Workflow
When you execute a statement on PolarDB-X, the statement is processed based on the following procedure:
The SQL statement is parameterized by replacing all the parameters with the placeholder character (
?).The parameterized SQL statement is used as a key to check whether an execution plan for the SQL statement is cached in the plan cache. If no execution plan is cached, the optimizers are revoked for optimization.
Simple statements are directly executed, and execution plan management is not performed.
Complex statements are executed based on the fixed execution plan stored in the baseline. If multiple execution plans are available, the execution plan with the lowest cost is selected.
Plan cache
By default, the plan cache feature is enabled in PolarDB-X. You can execute the EXPLAIN statement to view the execution plan of an SQL statement. In the returned result, HitCache indicates whether the SQL statement hits the plan cache. After the plan cache feature is enabled, PolarDB-X parameterizes each SQL statement to replace all constants in the SQL statement with the placeholder character (?) and create a list of parameters. In the execution plan, you can find that the SQL statement for the LogicalView operator contains the placeholder character (?).
Execution plan management
After a complex SQL statement is processed by the plan cache feature, the SQL statement is also subject to the execution plan management process.
The plan cache and the execution plan management use parameterized SQL statements as keys to select execution plans. The execution plans of all SQL statements are cached in the plan cache. Only complex SQL statements are processed in the execution plan management. One or more optimized execution plans are provided for each SQL statement due to varying parameters.
In execution plan management, each SQL statement corresponds to one baseline and each baseline contains one or more execution plans. The cost of each execution plan is estimated based on the parameters used in the SQL statement and the execution plan with the lowest cost is selected. When an execution plan in the plan cache is provided for execution plan management, SQL Plan Management (SPM) starts a process to check whether the execution plan is known. If the execution plan is known, SPM checks whether the execution plan incurs the lowest cost. If the execution plan is unknown, SPM checks whether the execution plan needs to be executed to determine whether the execution plan needs to be optimized.
Instructions to perform O&M
PolarDB-X provides a variety of commands to manage execution plans. The following syntax is used:
BASELINE (LOAD|PERSIST|CLEAR|VALIDATE|LIST|DELETE) [Signed Integer,Signed Integer....]
BASELINE (ADD|FIX) SQL (HINT Select Statemtnt)BASELINE (ADD|FIX) SQL <HINT> <Select Statement>: stores an execution plan modified by a hint as a fixed execution plan.
BASELINE LOAD: updates the specified baseline information in system tables to the memory for the baseline information to take effect.
BASELINE LOAD_PLAN: updates the specified execution plan information in system tables to the memory for the information to take effect.
BASELINE LIST: lists the information about all baselines.
BASELINE PERSIST: flushes the specified baseline to the disk.
BASELINE PERSIST_PLAN: flushes the specified execution plan to the disk.
BASELINE CLEAR: removes a baseline from the memory.
BASELINE CLEAR_PLAN: removes an execution plan from the memory.
BASELINE DELETE: deletes a baseline from the disk.
BASELINE DELETE_PLAN: deletes an execution plan from the disk.
Best practices for optimizing execution plans
After data changes or the optimizer engine of PolarDB-X is upgraded, a better execution plan may become available for the same SQL statement. During automatic plan evolution, SPM includes the better execution plan that is automatically discovered by the Plan Enumerator in the baseline of the SQL statement. You can also use SPM instructions to optimize execution plans.
After the
EXPLAINstatement is executed, the returned result shows that the execution plan of the SQL statement uses a Hash Join and only this execution plan is included in the baseline list for the SQL statement.EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The following information about the execution plan is returned:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | | HitCache:true | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.06 sec)Execute the following SQL statement to view the baseline:
BASELINE LIST;The following information is returned:
+-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 1 row in set (0.02 sec)If the performance of the SQL statement in certain conditions is improved after a Batched Key Access join (BKA join, also known as a lookup join) is used, the preferred solution is to provide a hint for PolarDB-X to generate an expected execution plan. The syntax of the hint used in a BKA join is
/*+TDDL:BKA_JOIN(lineitem, part)*/. ExecuteEXPLAIN [HINT] [SQL]to check whether the execution plan meets the expectation.EXPLAIN /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The following information about the execution plan is returned:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:false | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.14 sec)After a hint is specified, the BKA join is used as the join algorithm. The baseline is not changed. If you want to use the preceding execution plan each time you execute this SQL statement, you must add the execution plan to the baseline.
You can use the
BASELINE ADDcommand provided by execution plan management to add an execution plan for the SQL statement. In this case, two groups of execution plans exist in the baseline of the SQL statement at the same time. The Plan Enumerator selects the execution plan with the lower cost.BASELINE ADD SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The following information is returned:
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.09 sec)Execute the following SQL statement to view the baseline:
BASELINE LIST;The following information is returned:
+-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | BASELINE_ID | PARAMETERIZED_SQL | PLAN_ID | EXTERNALIZED_PLAN | FIXED | ACCEPTED | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -1024543942 | Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | 0 | 1 | | -399023558 | SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? | -935671684 | Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) | 0 | 1 | +-------------+--------------------------------------------------------------------------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----------+ 2 rows in set (0.03 sec)The preceding result of the
BASELINE LISTcommand shows that the execution plan based on BKA_JOIN is added to the baseline of the SQL statement. If you execute theEXPLAINstatement for this SQL statement, you can find that as thep_name LIKE ?in the SQL statement changes, PolarDB-X selects different execution plans.To make PolarDB-X always use the preceding execution plan, you can use the
BASELINE FIXcommand to force PolarDB-X to use the specified execution plan.BASELINE FIX SQL /*+TDDL:bka_join(lineitem, part)*/ SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The following information is returned:
+-------------+--------+ | BASELINE_ID | STATUS | +-------------+--------+ | -399023558 | OK | +-------------+--------+ 1 row in set (0.07 sec) mysql> baseline list\G *************************** 1. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -1024543942 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) Gather(concurrent=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") FIXED: 1 ACCEPTED: 1 *************************** 2. row *************************** BASELINE_ID: -399023558 PARAMETERIZED_SQL: SELECT * FROM lineitem JOIN part ON l_partkey = p_partkey WHERE p_name LIKE ? PLAN_ID: -935671684 EXTERNALIZED_PLAN: Gather(parallel=true) ParallelHashJoin(condition="l_partkey = p_partkey", type="inner") LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)", parallel=true) FIXED: 0 ACCEPTED: 1 2 rows in set (0.01 sec)Execute the
EXPLAINstatement again to view the execution plan.EXPLAIN SELECT * FROM lineitem JOIN part ON l_partkey=p_partkey WHERE p_name LIKE '%green%';The following information about the execution plan is returned:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | LOGICAL PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Gather(parallel=true) | | ParallelBKAJoin(condition="l_partkey = p_partkey", type="inner") | | LogicalView(tables="[00-03].lineitem", shardCount=4, sql="SELECT `l_orderkey`, `l_partkey`, `l_suppkey`, `l_linenumber`, `l_quantity`, `l_extendedprice`, `l_discount`, `l_tax`, `l_returnflag`, `l_linestatus`, `l_shipdate`, `l_commitdate`, `l_receiptdate`, `l_shipinstruct`, `l_shipmode`, `l_comment` FROM `lineitem` AS `lineitem`", parallel=true) | | Gather(concurrent=true) | | LogicalView(tables="[00-03].part", shardCount=4, sql="SELECT `p_partkey`, `p_name`, `p_mfgr`, `p_brand`, `p_type`, `p_size`, `p_container`, `p_retailprice`, `p_comment` FROM `part` AS `part` WHERE (`p_name` LIKE ?)") | | HitCache:true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.01 sec)After the
BASELINE FIXcommand is executed, the value ofFIXEDin the execution plan that contains the BKA join is 1. This way, even if no hint is added, theEXPLAINstatement is executed for this SQL statement based on the execution plan regardless of the specified conditions.