Oracle-执行计划生成及查看的几种方法

1. EXPLAIN FOR

语法：

EXPLAIN PLAN FOR
SQL语句

SELECT * FROM TABLE(dbms_xplan.display());

优点：

无需真正执行SQL

缺点：

没有输出相关的统计信息，例如产生了多少逻辑读、物理读、递归调用等情况
无法判断处理了多少行
无法判断表执行了多少次

例子：

EXPLAIN PLAN FOR
SELECT *
  FROM kceb_pzkcun
 WHERE yngyjigo = '9501'
   AND weixdhao = '2001'
   AND pingzhzl = '101'
   AND fenhdaim = '99'
   AND pngzphao = 'LN22'
   AND qishipzh <= 4070660
   AND 4070661 <= zzpzhhao
   AND pzdanwei = '1'
   AND jiluztai = '0';

SELECT * FROM TABLE(dbms_xplan.display());

在这里插入图片描述

2. SET AUTOTRACE ON|TRACEONLY[EXPLAIN]

注：该功能只能在SQLPLUS模式下使用
语法：

SET AUTOTRACE ON|TRACEONLY;
SQL语句;
SET AUTOTRACE OFF;

优点：

可以输出运行时的相关统计信息
虽然要等待语句执行完毕，但是可以通过TRACEONLY选项来控制返回结果不输出

缺点：

必须要等待语句执行完毕
无法看到表被访问了错少次

例子：

SET AUTOTRACE ON;
SELECT * FROM untifa_test t WHERE t.child_id = '1' AND t.relation_type = 'A' AND t.parent_id = '1';
SET AUTOTRACE TRACEONLY;
SELECT * FROM untifa_test t WHERE t.child_id = '1' AND t.relation_type = 'A' AND t.parent_id = '1';
SET AUTOTRACE OFF;

在这里插入图片描述

3. STATISTICS_LEVEL=ALL

上面两种方法，使用AUTOTRACE或者EXPLAIN PLAN FOR 获取的执行计划来自于PLAN_TABLE。PLAN_TABLE是一个会话级的临时表，里面的执行计划并不是SQL真实的执行计划，它只是优化器估算出来的。真实的执行计划不应该是估算的，应该是真正执行过的。SQL执行过的执行计划存在于共享池中，具体存在于数据字典V$SQL_PLAN中，带有A-Time的执行计划来自于V$SQL_PLAN，是真实的执行计划，而通过AUTOTRACE、通过EXPLAIN PLAN FOR获取的执行计划只是优化器估算获得的执行计划。

以下这种方法可以获取真执行计划

语法：

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SQL语句;

SELECT v.last_active_time, v.*
  FROM v$sql v
 WHERE v.last_active_time >
       to_date('日期', '日期格式')
   AND v.parsing_schema_name = 'SCHEMA'
   AND v.sql_text LIKE '%SQL语句%'
 ORDER BY v.last_active_time DESC;
 
SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id          => 'cwn6p7zs926rv',
                                       cursor_child_no => NULL,
                                       format          => 'allstats last'));

优点：

可以得到运行时的相关信息

缺点：

必须要等待语句执行完毕才能得到结果
无法控制结果打印输出

Starts 表示这个操作执行的次数
E-Rows表示优化器估算的行数，就是普通执行计划中的Rows
A-Rows表示真实的行数
A-Time表示累加的总时间。与普通执行计划不同的是，普通执行计划中的Time是假的，而A-Time是真实的。
Buffers表示累加的逻辑读
Reads表示累加的物理读

需要注意的是，普通执行计划估算出来的行数，受直方图统计信息的影响，可能会使优化器对执行计划的选择产生误判（例如本该走HASH JOIN，结果变成NESTED LOOPS）。因此，直方图统计信息应该定期更新。

获取真执行计划需要相应的权限
GRANT SELECT ANY DICTIONARY TO HXAPP;

例子：

SHOW PARAMETER STATISTICS_LEVEL;

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SELECT *
  FROM kceb_pzkcun
 WHERE yngyjigo = '9501'
   AND weixdhao = '2001'
   AND pingzhzl = '101'
   AND fenhdaim = '99'
   AND pngzphao = 'LN22'
   AND qishipzh <= 4070660
   AND 4070661 <= zzpzhhao
   AND pzdanwei = '1'
   AND jiluztai = '0';

SELECT v.last_active_time, v.*
  FROM v$sql v
 WHERE v.last_active_time >
       to_date('2023/11/02 14:00:00', 'yyyy/mm/dd hh24:mi:ss')
   AND v.parsing_schema_name = 'HXAPP'
   AND v.sql_text LIKE '%kceb_pzkcun%'
 ORDER BY v.last_active_time DESC;

SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id          => 'cwn6p7zs926rv',
                                       cursor_child_no => NULL,
                                       format          => 'allstats last'));

在这里插入图片描述

4. dbms_xplan.display_cursor

语法：

SELECT *
  FROM TABLE(dbms_xplan.display_cursor(sql_id          => '',
                                       cursor_child_no => n,
                                       format          => 'allstats last'));

select * from table( dbms_xplan.display_awr(‘&sql_id’) ); --该方法是从awr性能视图里面获取
如果有多个执行计划，可用以下方法查出：
select * from table(dbms_xplan.display_cursor(‘&sql_id’,0));
select * from table(dbms_xplan.display_cursor(‘&s ql_id’,1));

5. 事件10046 trace跟踪(未验证)

步骤1：alter session set events ‘10046 trace name context forever,level 12’; --开启追踪
步骤2：执行sql语句；
步骤3：alter session set events ‘10046 trace name context off’; --关闭追踪
步骤4：select tracefile from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum<=1)); --找到跟踪后产生的文件
步骤5：tkprof trc文件生成目标文件 sys=no sort=prsela,exeela,fchela --格式化命令