CNDBA社区
1 查看执行计划（逻辑：优化器生成）
执行计划是对SQL 语句在数据库中执行过程的描述。可以通过 EXPLAIN 命令查看优化器针对指定 SQL 生成的逻辑执行计划。
OceanBase 数据库的执行计划命令有三种模式：EXPLAIN BASIC、EXPLAIN 和 EXPLAIN EXTENDED。这三种模式对执行计划展现不同粒度的细节信息:
- EXPLAIN BASIC 命令用于最基本的计划展示。
- EXPLAIN EXTENDED 命令用于最详细的计划展示。
- EXPLAIN 命令所展示的信息可以帮助普通用户了解整个计划的执行方式。
EXPLAIN 命令格式如下：
EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] [PRETTY | PRETTY_COLOR] explainable_stmt
format_name:
{ TRADITIONAL | JSON }
explainable_stmt:
{ SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement }
一般来说，可以使用 EXPLAIN EXTENDED 命令，将表扫描的范围段展示出来。使用 EXPLAIN OUTLINE 命令可以显示 Outline 信息。
FORMAT 选项可用于选择输出格式。TRADITIONAL 表示以表格格式显示输出，这也是默认设置。JSON 表示以 JSON 格式显示信息。
使用 EXPLAIN PARTITITIONS 也可用于检查涉及分区表的查询。如果检查针对非分区表的查询，则不会产生错误，但 PARTIONS 列的值始终为 NULL。
对于复杂的执行计划，可以使用 PRETTY 或者 PRETTY_COLOR 选项将计划树中的父节点和子节点使用树线或彩色树线连接起来，使得执行计划展示更方便阅读。
示例如下：
obclient [oceanbase]> explain SELECT/*+ PARALLEL(15)*/t2.zone, t1.svr_ip, COUNT(*) AS RPC_COUNT,
-> AVG(t1.elapsed_time),AVG(t1.queue_time)
-> FROM oceanbase.GV$OB_SQL_AUDIT t1, __all_server t2
-> WHERE t1.svr_ip = t2.svr_ip AND IS_EXECUTOR_RPC = 1
-> AND tenant_id = 1001
-> AND request_time > time_to_usec('2023-07-10 17:20:59')
-> AND request_time < time_to_usec('2023-07-19 17:20:59')
-> GROUP BY t1.svr_ip ORDER BY t2.zone;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ======================================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------------------------------------- |
| |0 |PX COORDINATOR MERGE SORT | |1 |2012 | |
| |1 | EXCHANGE OUT DISTR |:EX10002 |1 |2011 | |
| |2 | SORT | |1 |2011 | |
| |3 | HASH GROUP BY | |1 |2011 | |
| |4 | EXCHANGE IN DISTR | |15 |2011 | |
| |5 | EXCHANGE OUT DISTR (HASH) |:EX10001 |15 |2009 | |
| |6 | HASH GROUP BY | |15 |2006 | |
| |7 | SHARED HASH JOIN | |100 |2005 | |
| |8 | EXCHANGE IN DISTR | |3 |1 | |
| |9 | EXCHANGE OUT DISTR (BC2HOST)|:EX10000 |3 |1 | |
| |10| PX BLOCK ITERATOR | |3 |1 | |
| |11| TABLE SCAN |t2 |3 |1 | |
| |12| SUBPLAN SCAN |GV$OB_SQL_AUDIT |100 |2001 | |
| |13| PX BLOCK ITERATOR | |100 |2000 | |
| |14| TABLE SCAN |__all_virtual_sql_audit|100 |2000 | |
| ======================================================================================== ……
2 执行计划缓存（Plan Cache）
OceanBase 数据库会缓存之前生成的执行计划，以便在下次执行该 SQL 时直接使用，可以避免反复执行，从而优化执行过程，这种策略被称为”Optimize Once”，即”一次优化”。
每个租户在每一台服务器上都有一个独立的计划缓存，用以缓存在此服务器上处理过的 SQL 计划。在 OceanBase 数据库的计划缓存中，SQL 的执行计划可以分为本地计划、远程计划和分布式计划三种类型。在计划缓存中，同一条 SQL 根据其需要访问的数据不同，可能同时具有三种执行计划。
对于 SQL 的执行计划，OceanBase 数据库默认只会保留第一次执行 SQL 时生成的计划；但在某些情况下，同一条 SQL 的参数值可能会影响到执行计划的选择，所以计划缓存会根据需要，为不同的参数值保留不同的执行计划，从而保证每次执行时可以使用最合适的计划。
2.1 计划缓存的淘汰
计划缓存的淘汰是指将执行计划从计划缓存中删除，减少计划缓存对内存的使用。
OceanBase 数据库支持自动淘汰和手动淘汰两种方式。
2.1.1 自动淘汰
自动淘汰是指当计划缓存占用的内存达到了需要淘汰计划的内存上限（即淘汰计划的高水位线）时，对计划缓存中的计划执行自动淘汰。
触发执行计划淘汰的条件
每隔一段时间（plan_cache_evict_interval 设置）系统会自动检查不同租户在不同服务器上的计划缓存，并判断是否需要执行计划淘汰。如果某个计划缓存占用的内存超过该租户设置的淘汰计划的高水位线，则会触发计划缓存淘汰。执行计划淘汰策略
当触发计划缓存淘汰后，优先淘汰最久没被使用的执行计划，淘汰一部分执行计划后，当计划缓存使用的内存为该租户设置的淘汰计划的低水位线时，停止淘汰。与计划缓存淘汰相关配置
plan_cache_evict_interval 设置检查执行计划是否需要淘汰的间隔时间。
ob_plan_cache_percentage 设置计划缓存可使用内存占租户内存的百分比。
公式如下：计划缓存最多可使用内存（内存上限绝对值）=租户内存上限 ob_plan_cache_percentage/100
ob_plan_cache_evict_high_percentage 设置触发计划缓存淘汰的内存大小占内存上限绝对值的百分比。
公式如下：触发计划缓存淘汰的内存大小(淘汰计划的高水位线) = 内存上限绝对值 ob_plan_cache_evict_high_percentage/100
ob_plan_cache_evict_low_percentage 设置停止淘汰计划缓存的内存大小占内存上限绝对值的百分比。(淘汰计划的低水位线) 。
公式如下：停止淘汰计划时的内存值(淘汰计划的低水位线)= 内存上限绝对值 * ob_plan_cache_evict_low_percentage/100
例如，租户内存大小为 10 GB，各参数值设置如下：
ob_plan_cache_percentage＝10
ob_plan_cache_evict_high_percentage＝90
ob_plan_cache_evict_low_percentage＝50
计算得出：
计划缓存内存上限绝对值 ＝ 10G 10 / 100 = 1 GB
淘汰计划的高水位线 = 1G 90 / 100 = 0.9 GB
淘汰计划的低水位线 ＝ 1G * 50 / 100 = 0.5 GB
由计算结果可知，当该租户在某个服务器上计划缓存使用超过 0.9 GB 时，会触发淘汰，优先淘汰最久没执行的计划，当淘汰到使用内存只有 0.5 GB 时，则停止淘汰。 如果淘汰速度没有新计划生成速度快，计划缓存使用内存达到内存上限绝对值 1 GB 时，将不在往计划缓存中添加新计划，直到执行淘汰后所占内存小于 1 GB 才会添加新计划到计划缓存中。
2.1.2 手动淘汰
手动淘汰是指强制将计划缓存中计划进行删除。
现在支持指定不同租户对应的当前服务器或全部服务器中计划缓存全部删除，SQL 语句如下：
obclient>ALTER SYSTEM FLUSH PLAN CACHE [tenant_list] [global]
其中 tenant_list 和 global 为可选字段，使用说明如下：
tenant_list 格式为 tenant = ‘tenant_name, tenant_name….’。如果没有指定 tenant_list，则清空所有租户的计划缓存。
如果没有指定 global，则清空本机的计划缓存。
2.2 计划缓存的刷新
计划缓存中执行计划可能因为各种原因而失效，这时需要将计划缓存中失效计划进行刷新，即将该执行计划删除后重新优化生成计划再加入计划缓存。
如下场景会导致执行计划失效，需要对执行计划进行刷新：
- SQL 中涉及表的 Schema 变更时（比如添加索引、删除或增加列等），该 SQL 在计划缓存中所对应的执行计划将被刷新。
- SQL 中涉及重新收集表的统计信息时，该 SQL 在计划缓存中所对应的执行计划会被刷新。由于 OceanBase 数据库在数据合并时会统一进行统计信息的收集，因此在每次进行合并后，计划缓存中所有计划将被刷新。
2.3 计划缓存的使用控制
2.3.1 系统变量控制
当 ob_enable_plan_cache 设置为 TURE 时，表示 SQL 请求可以使用计划缓存；设置为 FALSE 时，表示 SQL 请求不使用计划缓存。默认为 TURE。此系统变量可被设置为 Session 级别或者 Global 级别。
2.3.2 Hint 控制
使用 Hint 语句 /+USE_PLAN_CACHE(NONE)/ 表示不使用计划缓存。
使用 Hint 语句 /+USE_PLAN_CACHE(DEFAULT)/ 表示使用计划缓存。
2.4 计划缓存的视图
执行计划相关视图包括：
- (G)V$OB_PLAN_CACHE_STAT：记录每个计划缓存的状态，每个计划缓存在该视图中有一条记录。
- (G)V$OB_PLAN_CACHE_PLAN_STAT：记录计划缓存中所有执行计划的具体信息及每个计划总的执行统计信息。
- (G)V$OB_PLAN_CACHE_PLAN_EXPLAIN：记录某条 SQL 在计划缓存中的执行计划。
3 查看实时执行计划（物理）
实时执行计划展示可以展示 SQL 的物理执行计划。
使用 EXPLAIN 命令可以展示出当前优化器所生成的执行计划，但由于统计信息变化、用户 Session 变量设置变化等，会造成该 SQL 在计划缓存中实际对应的计划可能与 EXPLAIN 的结果并不相同。
为了确定该 SQL 在系统中实际使用的执行计划，需要进一步分析计划缓存中的物理执行计划。
OceanBase 数据库每个服务器的计划缓存都是独立的。用户可以直接访问 V$OB_PLAN_CACHE_PLAN_STAT 视图查询本服务器上的计划缓存并提供 tenant_id 和需要查询的 SQL 字符串（可以使用模糊匹配），查询该条 SQL 在计划缓存中对应的 plan_id，并进一步展示该 SQL 在计划缓存中的物理执行计划。
- 查询 V$OB_PLAN_CACHE_PLAN_STAT 视图获取 SQL 在计划缓存中的 plan_id。
obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_STAT WHERE tenant_id= 1 AND STATEMENT LIKE 'select t4.tenant_id, t4.tenant_name,%'/G
*************************** 1. row ***************************
TENANT_ID: 1
SVR_IP: 10.103.241.133
SVR_PORT: 2882
PLAN_ID: 147
SQL_ID: 242AE493248CC9EB38CAE8EF99F4D5BA
TYPE: 1
IS_BIND_SENSITIVE: 0
IS_BIND_AWARE: 0
DB_ID: 201001
……
- 获得 plan_id 后，用户可以使用 tenant_id 和 plan_id 访问 V$OB_PLAN_CACHE_PLAN_EXPLAIN 视图查询执行计划相关信息。
obclient [oceanbase]> SELECT * FROM oceanbase.V$OB_PLAN_CACHE_PLAN_EXPLAIN WHERE tenant_id = 1 AND plan_id = 147;
+-----------+----------------+----------+---------+------------+--------------+----------------------+----------------------+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+----------------+----------+---------+------------+--------------+----------------------+----------------------+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 10.103.241.133 | 2882 | 147 | 0 | 0 | PHY_NESTED_LOOP_JOIN | NULL | 10000000000 | 254857439 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 1 | 1 | PHY_HASH_JOIN | NULL | 100000 | 2008638 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 2 | 2 | PHY_SUBPLAN_SCAN | NULL | 3 | 2 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 3 | 3 | PHY_TABLE_SCAN | A | 3 | 2 | table_rows:5, physical_range_rows:5, logical_range_rows:5, index_back_rows:0, output_rows:2, est_method:basic_stat, avaiable_index_name[__all_virtual_tenant_mysql_sys_agent] |
| 1 | 10.103.241.133 | 2882 | 147 | 2 | 4 | PHY_SUBPLAN_SCAN | NULL | 100000 | 2000264 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 3 | 5 | PHY_TABLE_SCAN | __all_virtual_unit | 100000 | 2000000 | table_rows:1, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:invalid_method, avaiable_index_name[__all_virtual_unit] |
| 1 | 10.103.241.133 | 2882 | 147 | 1 | 6 | PHY_MATERIAL | NULL | 100000 | 2020804 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 2 | 7 | PHY_SUBPLAN_SCAN | NULL | 100000 | 2000264 | NULL |
| 1 | 10.103.241.133 | 2882 | 147 | 3 | 8 | PHY_TABLE_SCAN | __all_virtual_server | 100000 | 2000000 | table_rows:1, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:invalid_method, avaiable_index_name[__all_virtual_server] |
+-----------+----------------+----------+---------+------------+--------------+----------------------+----------------------+-------------+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.005 sec)
注意：
这里展示的计划为物理执行计划，在算子命名上会与 EXPLAIN 所展示的逻辑执行计划有所不同。
4 执行计划绑定
在系统上线前，可以直接在 SQL 语句中添加 Hint，控制优化器按 Hint 指定的行为进行计划生成。
但对于已上线的业务，如果出现优化器选择的计划不够优化时，则需要在线进行计划绑定，即无需业务进行 SQL 更改，而是通过 DDL 操作将一组 Hint 加入到 SQL 中，从而使优化器根据指定的一组 Hint，对该 SQL 生成更优计划。该组 Hint 称为 Outline。
Outline 视图为 DBA_OB_OUTLINES。
4.1 创建 Outline
OceanBase 数据库支持通过两种方式创建 Outline，一种是通过 SQL_TEXT (用户执行的带参数的原始语句)，另一种是通过 SQL_ID 创建。
4.1.1 使用 SQL_TEXT 创建 Outline（不推荐）
使用 SQL_TEXT 创建 Outline 的语法如下：
CREATE [OR REPLACE] OUTLINE
ON [ TO ];
示例：
obclient> CREATE OUTLINE outline1 ON SELECT /+NO_REWRITE/ FROM tbl1 WHERE col1 = 4 AND col2 = 6 ORDER BY 2 TO SELECT FROM tbl1 WHERE col1 = 4 AND col2 = 6 ORDER BY 2;
不过由于文本中空格和换行如果有差异就会导致匹配不上，所以不建议通过文本创建 Outline（除非文本非常简单）。
4.1.2 使用 SQL_ID 创建 Outline
使用 SQL_ID 创建 Outline 的语法如下：
obclient> CREATE OUTLINE outline_name ON sql_id USING HINT hint_text;
SQL_ID 为需要绑定的 SQL 对应的 SQL_ID，可以通过以下方式获取：
- 通过查询 V$OB_PLAN_CACHE_PLAN_STAT 获取。
- 通过查询 GV$OB_SQL_AUDIT 获取。
示例：
obclient> CREATE OUTLINE otl_idx_c2 ON “ED570339F2C856BA96008A29EDF04C74”
USING HINT /+ INDEX(t1 idx_c2)/ ;
Outline Data 是优化器为了完全复现某一计划而生成的一组 Hint 信息，以 BEGIN_OUTLINE_DATA 开始，并以 END_OUTLINE_DATA 结束。
Outline Data 可以通过 EXPLAIN EXTENDED 命令获得：
obclient> EXPLAIN EXTENDED SELECT/+ index(t1 idx_c2)/ * FROM t1 WHERE c2 = 1/G
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2")
END_OUTLINE_DATA
*/
4.2 确定 Outline 创建生效
- 确定 Outline 创建成功。
通过查看 DBA_OB_OUTLINES 视图，确认是否成功创建对应名称的 Outline。
obclient> SELECT * FROM DBA_OB_OUTLINES WHERE OUTLINE_NAME = 'otl_idx_c2'/G
*************************** 1. row ***************************
tenant_id: 1001
database_id: 1100611139404776
outline_id: 1100611139404777
database_name: test
outline_name: otl_idx_c2
visible_signature: SELECT * FROM t1 WHERE c2 = ?
sql_text: SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1
outline_target:
outline_sql: SELECT /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/*
FROM t1 WHERE c2 = 1
- 确定新的 SQL 是否通过绑定的 Outline 生成了新执行计划。
当绑定 Outline 的 SQL 执行新的查询后，查询 GV$OB_PLAN_CACHE_PLAN_STAT 表中该 SQL 对应的计划信息中的 outline_id。如果 outline_id 与在 DBA_OB_OUTLINES 中查到的 outline_id 相同，则表示是按绑定的 Outline 生成的执行计划，否则不是。
obclient> SELECT SQL_ID, PLAN_ID, STATEMENT, OUTLINE_ID, OUTLINE_DATA
FROM oceanbase.GV$OB_PLAN_CACHE_PLAN_STAT
WHERE STATEMENT LIKE '%SELECT * FROM t1 WHERE c2 =%'/G
*************************** 1. row ***************************
sql_id: ED570339F2C856BA96008A29EDF04C74
plan_id: 17225
statement: SELECT * FROM t1 WHERE c2 = ?
outline_id: 1100611139404777
outline_data: /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$1" "test.t1"@"SEL$1" "idx_c2") END_OUTLINE_DATA*/
4.3 删除 Outline
删除 Outline 后，对应 SQL 将不再依据所绑定的 Outline 重新生成执行计划。
先查询DBA_OB_OUTLINES视图，然后删除即可：
DROP OUTLINE outline_name;
版权声明：本文为博主原创文章，未经博主允许不得转载。
