【OceanBase】27.OB的执行计划

1.执行计划的描述 

◼ SQL 是一种“描述型”语言。与“过程型”语言不同，用户在使用 SQL 时，只描述了“要做什么”，而不是“怎么
做”
◼ 数据库在接收到 SQL 查询时，必须为其生成一个“执行计划”。OceanBase 的执行计划本质上是由物理操作符构
成的一棵执行树
◼ 执行树从形状上可以分为“左深树”、“右深树”和“多枝树”三种（参见下图）。 OceanBase 的优化器在生成
连接顺序时主要考虑左深树的连接形式

--左深树
		  ⚫
		 /  \
		⚫  ⚫
	   / \
	  ⚫  ⚫
     /  \
    ⚫   ⚫


--右深数 
		  ⚫
		 /  \
		⚫  ⚫
	       /  \
	      ⚫   ⚫
			 /  \
			⚫   ⚫ 

--多枝树
		  ⚫
		 /  \
		⚫   ⚫
       /  \  / \
      ⚫  ⚫ ⚫  ⚫

2.执行计划展示(EXPLAIN)

◼ 通过Explain命令查看优化器针对给定SQL生成的逻辑执行计划
◼ Explain不会真正执行给定的SQL，可以放心使用该功能而不用担心在性能调试中可能给系统性能带来影响
◼ Explain命令格式如下例所示，展示格式包括 BASIC、EXTENDED、PARTITIONS 等等，内容的详细程度有所区别
EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] explainable_stmt
format_name: 
{ TRADITIONAL | JSON }
explainable_stmt: 
{ SELECT statement 
| DELETE statement 
| INSERT statement 
| REPLACE statement 
| UPDATE statement }

EXPLAIN BASIC SELECT statement ;
EXPLAIN EXTENDED SELECT statement ;
EXPLAIN PARTITIONS SELECT statement ;
EXPLAIN FORMAT =TRADITIONAL SELECT statement ;
EXPLAIN FORMAT =JSON SELECT statement ;

EXPLAIN BASIC DELETE statement ;
EXPLAIN EXTENDED DELETE statement ;
EXPLAIN PARTITIONS DELETE statement ;
EXPLAIN FORMAT =TRADITIONAL DELETE statement ;
EXPLAIN FORMAT =JSON DELETE statement ;

EXPLAIN BASIC INSERT statement ;
EXPLAIN EXTENDED INSERT statement ;
EXPLAIN PARTITIONS INSERT statement ;
EXPLAIN FORMAT =TRADITIONAL INSERT statement ;
EXPLAIN FORMAT =JSON INSERT statement ;

EXPLAIN BASIC REPLACE statement ;
EXPLAIN EXTENDED REPLACE statement ;
EXPLAIN PARTITIONS REPLACE statement ;
EXPLAIN FORMAT =TRADITIONAL REPLACE statement ;
EXPLAIN FORMAT =JSON REPLACE statement ;

EXPLAIN BASIC UPDATE statement ;
EXPLAIN EXTENDED UPDATE statement ;
EXPLAIN PARTITIONS UPDATE statement ;
EXPLAIN FORMAT =TRADITIONAL UPDATE statement ;
EXPLAIN FORMAT =JSON UPDATE statement ;

3.执行计划展示(EXPLAIN)-计划形状与算子信息

◼ Explain输出的第一部分是执行计划的树形结构展示。其中每一个操作在树中的层次通过其在OPERATOR中的缩进予
以展示：
|==========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------------
|0 |SORT 				|   |1    |2763|
|1 | MERGE INNER JOIN	|   |1    |2735|
|2 |  SORT 				|   |1000 |1686|
|3 |   TABLE SCAN 		|t2 |1000 |1180|
|4 |  TABLE SCAN 		|t1 |1    |815 |
==========================================


		  (0)SORT
			|
		(1)MERGE JOIN
			/     \
		   /       \
		(2)SORT   (4)TABLE SCAN
        /
       /
(3)TABLE SCAN

执行顺序为:3->2->4->1->0;

4.执行计划展示(EXPLAIN)-计划形状与算子信息

--执行计划各列含义
列名 含义
ID :执行树按照前序遍历的方式得到的编号（从0开始）
OPERATOR:操作算子的名称
NAME:对应表操作的表名（索引名）
EST. ROWS:估算的该操作算子的输出行数
COST:该操作算子的执行代价（微秒）

--常见的算子
类型 算子
表访问:table scan, table get
连接:NESTED-LOOP， BLK-NESTED-LOOP，Merge、hash
排序:sort，top-n sort
聚合:merge group-by，hash group-by，window function
分布式:exchange in/out remote/distribute
集合:union, except, intersect，minus
其他:limit, material, subplan, expression, count

5.执行计划展示(EXPLAIN)-操作算子详细输出

◼ Explain输出的第二部分是各操作算子的详细信息，包括输出表达式、过滤条件、分区信息以及各算子的独有信息，
包括排序键、连接键、下压条件等等：

Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], 
[t1.c2, ASC]), prefix_pos(1)
1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
equal_conds([t1.c1 = t2.c2]), other_conds(nil)
2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
3 - output([t2.c2], [t2.c1]), filter(nil),access([t2.c2], [t2.c1]), partitions(p0)
4 - output([t1.c1], [t1.c2]), filter(nil),access([t1.c1], [t1.c2]), partitions(p0)

6.执行计划展示(EXPLAIN)-示例

◼ 如下示例可以看出要访问的表为 t1_c2 这张索引表，表的主键为(c2, c1)，扫描的范围是全表扫描。
OceanBase (root@oceanbase)> explain extended 
select /*+ index(t1 t1_c2) */* from t1 where c3 = 5 and c1 = 6 order by c2, c3;
========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------
|0 |TABLE SCAN|t1(t1_c2)|1 |1255|
========================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1(0x7f1d520a0a98)], [t1.c2(0x7f1d520a0d98)], 
[t1.c3(0x7f1d5209fbe0)]), filter([t1.c3(0x7f1d5209fbe0) = 5(0x7f1d5209f5d8)], 
[t1.c1(0x7f1d520a0a98) = 6(0x7f1d520a0490)]),
access([t1.c3(0x7f1d5209fbe0)], [t1.c1(0x7f1d520a0a98)], 
[t1.c2(0x7f1d520a0d98)]), partitions(p0),is_index_back=true, filter_before_indexback[false,true],
range_key([t1.c2(0x7f1d520a0d98)], [t1.c1(0x7f1d520a0a98)]), 
range(MIN,MIN ; MAX,MAX)always true

7.实时执行计划展示

实时执行计划展示可以展示 SQL 的物理执行计划。而使用Explain命令，可以展示出当前优化器所生成的执行计划，
但该SQL在计划缓存中实际对应的计划可能与Explain的结果并不相同，造成这种现象的原因有很多，诸如统计信息
变化、用户session变量设置变化等等。为了确定该SQL在系统中实际使用的执行计划，有时还需要进一步分析计划
缓存中的物理执行计划。
OceanBase 数据库每个服务器的计划缓存都是独立的 。 用户可以通过查询(g)v$plan_cache_plan_explain 这张虚
拟表来展示某条SQL在计划缓存中的执行计划。其中有几个注意点：
◼ (g)v$plan_cache_plan_explain 这张虚拟表展示的计划为物理执行计划，在算子命名上会与 EXPLAIN 所展示
的逻辑执行计划有所不同
◼ 如果访问v$plan_cache_plan_explain，必须给定tenant_id和plan_id的值，否则系统将返回空集
◼ 如果访问gv$plan_cache_plan_explain，必须给定ip、port、tenant_id、plan_id这四列的值，否则系统将返
回空集

Step1：首先通过(g)v$plan_cache_plan_stat虚拟表查询到SQL 在计划缓存中对应的plan_id

select * from v$plan_cache_plan_stat 
where tenant_id= 1001 and statement like 'insert into t1 values%'\G
***************************1. row ***************************
			 tenant_id: 1001
				svr_ip:100.81.152.44
			 svr_port:15212
			  plan_id: 7
		       sql_id:0
			     type: 1
		    statement: insert into t1 values(1)
            plan_hash:1
     last_active_time:2016-05-28 19:08:57.416670
         avg_exe_usec:0
     slowest_exe_time:1970-01-01 08:00:00.000000
     slowest_exe_usec:0
           slow_count:0
            hit_count:0
             mem_used:8192

Step2：通过查询(g)v$plan_cache_plan_explain 这张虚拟表来展示某条SQL在计划缓存中的执行计划
SELECT * FROM v$plan_cache_plan_explain
WHERE tenant_id = 1001 AND plan_id = 7;
+---------+---------------+-------+--------+--------------------+------+------+------+
|TENANT_ID| IP | PORT |PLAN_ID | OPERATOR | NAME | ROWS | COST |
+---------+---------------+-------+--------+--------------------+------+------+------+
| 1001 | 192.168.8.121 | 15212 | 7 | PHY_ROOT_TRANSMIT | NULL | 0 | 0 |
| 1001 | 192.168.8.121 | 15212 | 7 |  PHY_INSERT       | NULL | 0 | 0 |
| 1001 | 192.168.8.121 | 15212 | 7 |   PHY_EXPR_VALUES | NULL | 0 | 0 |
+---------+---------------+-------+--------+--------------------+------+------+------+
3 rows in set (0.01 sec)

8.EXPLAIN与实时执行计划的对比

EXPLAIN命令的输出逻辑执行计划：

Query Plan: ==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------------------------
|0 |PX COORDINATOR         |        |392040000|270172176|
|1 | EXCHANGE OUT DISTR    |:EX10000|392040000|233061644|
|2 |  PX PARTITION ITERATOR|        |392040000|233061644|
|3 |   HASH JOIN           |        |392040000|233061644|
|4 |    TABLE SCAN         |t1      |200000   |132543 |
|5 |    TABLE SCAN         |t2      |200000   |132543 |
=========================================================
Outputs & filters: 
-------------------------------------
0 - output([t1.c1]), filter(nil)
1 - output([t1.c1]), filter(nil), dop=1
2 - output([t1.c1]), filter(nil)
3 - output([t1.c1]), filter(nil), 
equal_conds([t1.c1 = t2.c1]), other_conds(nil)
4 - output([t1.c1]), filter(nil), 
access([t1.c1]), partitions(p[0-1])
5 - output([t2.c1]), filter(nil), 
access([t2.c1]), partitions(p[0-1])

v$plan_cache_plan_explain 输出的实时执行计划：
+--------------------------+------+-----------+-----------
| OPERATOR                | NAME | ROWS | COST 
+--------------------------+------+-----------+-----------
| PHY_PX_FIFO_COORD       | NULL | 392039999 | 270172175 
|  PHY_PX_REDUCE_TRANSMIT | NULL | 392039999 | 233061643 
|   PHY_GRANULE_ITERATOR  | NULL | 392039999 | 233061643 
|    PHY_HASH_JOIN        | NULL | 392039999 | 233061643 
|     PHY_TABLE_SCAN      | t1   | 200000    | 132542 
|     PHY_TABLE_SCAN      | t2   | 200000    | 132542 
+--------------------------+------+-----------+-----------
EXPLAIN命令的输出逻辑执行计划： v$plan_cache_plan_explain 输出的实时执行计划：
v$plan_cache_plan_explain中的数据没有类似explain输出的第二部份的各操作算子的详细信息

总结:
可以看到真实的执行计划中,里面有关键字前缀:PHY_;这与Oracle的执行计划有所不同。

9.查看执行计划的几种其它方式

◼ EXPLAIN|DESCRIBE|DESC方式查看执行计划
select * from v$sql_audit;
{EXPLAIN | DESCRIBE | DESC} 
[BASIC | OUTLINE | EXTENDED | EXTENDED_NOADDR | PARTITIONS | FORMAT = {TRADITIONAL| 
JSON}] 
{SELECT statement | DELETE statement | INSERT statement | REPLACE statement| UPDATE 
statement}

◼ 通过SQL Trace查看执行过程信息及各阶段的耗时
SET ob_enable_trace_log = 1;
SHOW TRACE;

--用法:
SET ob_enable_trace_log = 1;
SQL STATEMENT;
SHOW TRACE;

◼ 通过v$sql_audit 查看每一次SQL请求的来源、执行状态等统计信息
select * from v$sql_audit;

--查看执行计划的方法。
DESCRIBE/DESC/EXPLAIN BASIC SELECT statement;
DESCRIBE/DESC/EXPLAIN OUTLINE SELECT statement;
DESCRIBE/DESC/EXPLAIN EXTENDED SELECT statement;
DESCRIBE/DESC/EXPLAIN EXTENDED_NOADDR SELECT statement;
DESCRIBE/DESC/EXPLAIN PARTITIONS SELECT statement;
DESCRIBE/DESC/EXPLAIN FORMAT =TRADITIONAL SELECT statement;
DESCRIBE/DESC/EXPLAIN FORMAT =JSON SELECT statement;

DESCRIBE/DESC/EXPLAIN BASIC DELETE statement;
DESCRIBE/DESC/EXPLAIN OUTLINE DELETE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED DELETE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED_NOADDR DELETE statement;
DESCRIBE/DESC/EXPLAIN PARTITIONS DELETE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =TRADITIONAL DELETE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =JSON DELETE statement;

DESCRIBE/DESC/EXPLAIN BASIC INSERT statement;
DESCRIBE/DESC/EXPLAIN OUTLINE INSERT statement;
DESCRIBE/DESC/EXPLAIN EXTENDED INSERT statement;
DESCRIBE/DESC/EXPLAIN EXTENDED_NOADDR INSERT statement;
DESCRIBE/DESC/EXPLAIN PARTITIONS INSERT statement;
DESCRIBE/DESC/EXPLAIN FORMAT =TRADITIONAL INSERT statement;
DESCRIBE/DESC/EXPLAIN FORMAT =JSON INSERT statement;

DESCRIBE/DESC/EXPLAIN BASIC REPLACE statement;
DESCRIBE/DESC/EXPLAIN OUTLINE REPLACE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED REPLACE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED_NOADDR REPLACE statement;
DESCRIBE/DESC/EXPLAIN PARTITIONS REPLACE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =TRADITIONAL REPLACE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =JSON REPLACE statement;

DESCRIBE/DESC/EXPLAIN BASIC UPDATE statement;
DESCRIBE/DESC/EXPLAIN OUTLINE UPDATE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED UPDATE statement;
DESCRIBE/DESC/EXPLAIN EXTENDED_NOADDR UPDATE statement;
DESCRIBE/DESC/EXPLAIN PARTITIONS UPDATE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =TRADITIONAL UPDATE statement;
DESCRIBE/DESC/EXPLAIN FORMAT =JSON UPDATE statement;