Oracle获取执行计划的七种方法以及使用场景-摩杜云开发者社区

一.explain plan for

select * from t1,t2
	where t1.id=t2.id
	and t1.id in(5,6);
select * from table(dbms_xplan.display());

Oracle获取执行计划的七种方法以及使用场景_执行计划

优点

无需真正执行，快捷方便

缺点

1.没有输出运行时的相关统计信息(产生多少逻辑读，多少次物理读，多少次递归调用等)；
2.无法判断是处理了多少行；
3.无法判断表被访问了多少次。

使用场景

跟踪某条SQL最简单的方法；
如果某SQL执行时间很长才能出结果，或无返回结果，这时使用此方法；
如果想要确保看到真实的执行计划，不能用此方法；

二 .set autotrace on

Oracle获取执行计划的七种方法以及使用场景_oracle_02

优点

1.可以输出运行时的相关统计信息(产生多少逻辑读，多少次物理读，多少次递归调用等)；
2.虽然必须要等语句执行完毕后才可以输出执行计划，但可以使用traceonly控制返回结果不打印。

缺点

1.必须要等语句真正执行完毕后，才可以输出结果；
2.无法看到表被访问了多少次。

使用场景

跟踪某条SQL最简单的方法；
如果想要确保看到真实的执行计划，不能用此方法；

三 .statistics_level=all;

alter session set statistics_level=all;
执行SQL
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Oracle获取执行计划的七种方法以及使用场景_oracle_03

优点

1.可以清晰的从STARTS得出表被访问多少次；
2.可以清晰的从E_ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确的判断Oracle评估是否准确；
3.虽然没有专门的输出统计信息，但是执行计划中的BUFFERS就是真实的逻辑读次数，Reads表示物理读（本次SQL执行并未发生物理读，因此没有显示）。

缺点

1.必须要等到语句真实执行完毕后才可以输出结果；
2.无法控制记录打印输出运行时的相关统计信息；
3.无法看出递归调用的次数。

使用场景

想要获取表被访问的次数，只能用此方法；

四 .通过dbms_xplan.display_cursor输入sql_id参数直接获取

从共享池获得sql_id
select sql_id,sql_text from v$sql where sql_text like '%t1,t2%';
--或者通过其他方式获取到sql_id（比如awr）

select * from table(dbms_xplan.display_cursor('&sq_id'));

Oracle获取执行计划的七种方法以及使用场景_执行计划_04

优点

1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
2.可以得到准确的执行计划

缺点

1.没有输出运行时的相关统计信息(产生多少逻辑读，多少次物理读，多少次递归调用等)；
2.无法判断处理了多少行；
3.无法判断表被访问了多少次。

使用场景

观察某条SQL有多条执行计划的情况，这时使用该方法；
如果某SQL执行时间很长才能出结果，可以使用此方法直接调用；

五. 使用10046跟踪

alter session set events '10046 trace name context forever,level 12';(开启跟踪)
执行SQL 
alter session set events '10046 trace name context off'; (关闭跟踪)

到$ORACLE_BASE/diag/rdbms/orcl11g/orcl11g/trace目录下找trace文件，
或者通过该SQL查询当前session的trace

select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

tkprof prod_ora_69722.trc output=/home/oracle/20220419_prod_t1t2.txt sys=no waits=yes explain=fr/** (格式化命令)

level 1:SQL 语句，执行计划和执行状态
level 4:(level 1)的内容加上绑定变量信息
level 8:(level 1)的信息加上等待事件信息
level 12:(level 1)＋(level 4)＋(level 8)

Oracle获取执行计划的七种方法以及使用场景_执行计划_05

优点

1.可以看出SQL语句对应的等待事件；
2.如果SQL语句中有函数调用，SQL中有SQL，都会被列出来；
3.可以方便的看出处理的行数，产生的物理逻辑读；
4.可以方便的看出解析时间和执行时间；
5.可以跟踪整个程序包。

缺点

1.步骤繁琐，比较麻烦；
2.无法判断表被访问了多少次；
3.执行计划中的谓词部分不能清晰的展现出来。

使用场景

如果SQL中含有函数，函数中套有SQL等多层调用,想准确分析只能使用此方法；
如果想要查看SQL产生的等待事件，需要使用此方法

六 .awrsqrpt.sql

在sqlplus中执行@?/rdbms/admin/awrsqrpt.sql，按照提示，输入相关信息即可获得报告。

Oracle获取执行计划的七种方法以及使用场景_sql_06

Oracle获取执行计划的七种方法以及使用场景_oracle_07

优点

可以观察具有多条执行计划的SQL，并且有详细的执行时间以及资源开销

缺点

操作比较繁琐，需要将报告导出查看，有些环境限制无法导出。

使用场景

同一条SQL有多个执行计划时，若进行分析，可以使用该方法；另外如果SQL含有中文字符，在sqlplus中显示乱码，可以使用该方式生成html格式的报告，即可正常显示中文字符。

七、使用sql_monitor 查看执行计划

使用v$sql_monitor,$sql_monitor是Oracle数据库中的一个视图，用于监控和分析正在执行的SQL语句的性能信息。该视图提供了丰富的性能信息，包括SQL语句的执行计划、执行时间、等待事件、I/O操作、锁等待、PGA和SGA使用情况等等。使用该视图可以帮助DBA或开发人员识别和定位SQL语句的性能瓶颈，并进行必要的优化和调整。

在使用该视图时，需要注意以下几点：

该视图只对正在执行的SQL语句提供性能信息，因此需要在SQL语句执行期间进行监控；
可以根据需要对该视图进行过滤，以便只显示特定的SQL语句或特定的会话信息；
该视图中的性能信息可能会对系统性能产生一定的影响，因此需要谨慎使用，并根据需要进行采样或调整。总之，v$sql_monitor 是Oracle数据库中非常有用的性能监控工具，可以帮助DBA或开发人员快速定位和解决SQL语句性能问题。

SELECT *
FROM v$sql_monitor
WHERE sql_id = 'your_sql_id';

在指定目录开启 http 服务，就可以通过 http://server-ip 在线查询生成的 HTML 报告：

set trimspool on
set arraysize 512
set trim on
set pagesize 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool active_sqlmon.html
select dbms_sqltune.report_sql_monitor(
  sql_id => 'bra35zf15vswf' , 
  report_level=>'ALL', 
  type=>'ACTIVE')
from dual;
spool off


#开启http服务
[root@Oracle19C oracle]# python -m SimpleHTTPServer 80
Serving HTTP on 0.0.0.0 port 80 ...
10.111.73.1 - - [15/Jun/2023 15:39:58] "GET /active_sqlmon.html HTTP/1.1" 200 -
10.111.73.1 - - [15/Jun/2023 15:40:02] "GET / HTTP/1.1" 200 -
10.111.73.1 - - [15/Jun/2023 15:40:04] "GET /active_sqlmon.html HTTP/1.1" 200 -
10.111.73.1 - - [15/Jun/2023 15:40:11] "GET /active_sqlmon.html HTTP/1.1" 200 -
10.111.73.1 - - [15/Jun/2023 15:40:13] "GET /active_sqlmon.html HTTP/1.1" 200 -

Oracle获取执行计划的七种方法以及使用场景_sql_08

Oracle获取执行计划的七种方法以及使用场景_oracle_09