OB_查看SQL执行计划
查看执行计划查看执行计划(OB3.x)
查看预估执行计划
通过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 }
查看真实执行计划
- (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这四列的值 ， 否则系统将返回空集
##先从v$plan_cache_plan_stat 视图查出 tenant_id和plan_id
SELECT * FROM v$plan_cache_plan_stat
WHERE tenant_id= 1001 AND STATEMENT LIKE 'INSERT INTO T1 VALUES%'\G
####再根据上一步查出的tenant_id和plan_id查询出执行计划
SELECT tenant_id,svr_ip,svr_port,plan_id,operator,name,cost
FROM v$plan_cache_plan_explain WHERE tenant_id = 1001 AND plan_id = 7 \G
注意：
- 目前3.2.3.3版本无法指定查看rows列的内容，会提示语法报错。已经提交社区
- v$plan_cache_plan_stat 和v$plan_cache_plan_explain无法联合查询执行计划，只能分两步查看。
使用trace查看个阶段耗时
通过v$sql_audit查看每一次SQL请求的来源、执行状态等统计信息
select * from v$sql_qudit where query_sql like '%%'\G
查看SQL执行计划(OB4.x版本)
EXPLAIN 命令格式
OceanBase 数据库的执行计划命令有三种模式：EXPLAIN BASIC、EXPLAIN 和 EXPLAIN EXTENDED。这三种模式对执行计划展现不同粒度的细节信息:
- EXPLAIN BASIC 命令用于最基本的计划展示。
- EXPLAIN EXTENDED 命令用于最详细的计划展示（通常在排查问题时使用这种展示模式）。
- EXPLAIN 命令所展示的信息可以帮助普通用户了解整个计划的执行方式。
obclient [ZHZBUSI]> explain select t3.id,t3.name from zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2;
+------------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------------+
| ==================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------- |
| |0 |NESTED-LOOP JOIN CARTESIAN | |2 |14 | |
| |1 |├─TABLE RANGE SCAN |T3(IDX_T3_ID)|2 |9 | |
| |2 |└─MATERIAL | |1 |5 | |
| |3 | └─TABLE FULL SCAN |T4 |1 |5 | |
| ==================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([T3.ID], [T3.NAME]), filter(nil), rowset=16 |
| conds(nil), nl_params_(nil), use_batch=false |
| 1 - output([T3.ID], [T3.NAME]), filter(nil), rowset=16 |
| access([T3.__pk_increment], [T3.ID], [T3.NAME]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([T3.ID], [T3.__pk_increment]), range(2,MIN ; 2,MAX), |
| range_cond([T3.ID = 2]) |
| 2 - output(nil), filter(nil), rowset=16 |
| 3 - output(nil), filter([2 = T4.ID]), rowset=16 |
| access([T4.ID]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([T4.__pk_increment]), range(MIN ; MAX)always true |
+------------------------------------------------------------------------------------+
22 rows in set (0.009 sec)
obclient [ZHZBUSI]>
查看真实执行计划/内存执行计划
- gv$ob_sql_audit #查看SQL文本，确认需要查看执行计划的SQL。
- GV$OB_PLAN_CACHE_PLAN_STAT #根据SQL文本查看TENANT_ID、SVR_IP、SVR_PORT、PLAN_ID 等信息
- GV$OB_PLAN_CACHE_PLAN_EXPLAIN #根据TENANT_ID、SVR_IP、SVR_PORT、PLAN_ID 的查看具体执行计划。注意：必须指定 TENANT_ID、SVR_IP、SVR_PORT、PLAN_ID 的等值条件，否则查询结果为空
obclient [ZHZBUSI]> select svr_ip,svr_port,TENANT_NAME,USER_NAME,PLAN_ID,QUERY_SQL from gv$ob_sql_audit where query_sql like '%zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2';
+------------+----------+-------------+-----------+---------+---------------------------------------------------------------------------------------+
| SVR_IP | SVR_PORT | TENANT_NAME | USER_NAME | PLAN_ID | QUERY_SQL |
+------------+----------+-------------+-----------+---------+---------------------------------------------------------------------------------------+
| 10.3.3.166 | 2882 | zhz_oracle | ZHZBUSI | 2585 | select t3.id,t3.name from zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2 |
| 10.3.3.166 | 2882 | zhz_oracle | ZHZBUSI | 2631 | explain select t3.id,t3.name from zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2 |
+------------+----------+-------------+-----------+---------+---------------------------------------------------------------------------------------+
2 rows in set (0.329 sec)
obclient [ZHZBUSI]> select tenant_id,svr_ip,svr_port,QUERY_SQL,STATEMENT,PLAN_ID from GV$OB_PLAN_CACHE_PLAN_STAT where query_sql like '%zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2';
+-----------+------------+----------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+---------+
| TENANT_ID | SVR_IP | SVR_PORT | QUERY_SQL | STATEMENT | PLAN_ID |
+-----------+------------+----------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+---------+
| 1050 | 10.3.3.166 | 2882 | select t3.id,t3.name from zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=2 | select t3.id,t3.name from zhzbusi.t3,zhzbusi.t4 where t3.id=t4.id and t3.id=? | 2585 |
+-----------+------------+----------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+---------+
1 row in set (0.054 sec)
obclient [ZHZBUSI]> select * from GV$OB_PLAN_CACHE_PLAN_EXPLAIN where tenant_id=1050 and svr_ip='10.3.3.166' and svr_port=2882 and plan_id=2585;
+-----------+------------+----------+---------+------------+--------------+----------------------+------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR | NAME | ROWS | COST | PROPERTY |
+-----------+------------+----------+---------+------------+--------------+----------------------+------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1050 | 10.3.3.166 | 2882 | 2585 | 0 | 0 | PHY_NESTED_LOOP_JOIN | NULL | 1 | 8 | NULL |
| 1050 | 10.3.3.166 | 2882 | 2585 | 1 | 1 | PHY_TABLE_SCAN | T3 | 1 | 3 | table_rows:4, physical_range_rows:4, logical_range_rows:4, index_back_rows:0, output_rows:1, avaiable_index_name[T3], estimation info[table_id:500017, (table_type:0, version:-1--1--1, logical_rc:4, physical_rc:4)] |
| 1050 | 10.3.3.166 | 2882 | 2585 | 1 | 2 | PHY_MATERIAL | NULL | 1 | 4 | NULL |
| 1050 | 10.3.3.166 | 2882 | 2585 | 2 | 3 | PHY_TABLE_SCAN | T4 | 1 | 4 | table_rows:6, physical_range_rows:6, logical_range_rows:6, index_back_rows:0, output_rows:1, avaiable_index_name[T4], estimation info[table_id:500018, (table_type:0, version:-1--1--1, logical_rc:6, physical_rc:6)] |
+-----------+------------+----------+---------+------------+--------------+----------------------+------+------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.007 sec)
obclient [ZHZBUSI]>
