获取SQL执行计划的常见几种方法

时间：2024-01-03 18:10:41 买帖  | 投诉/举报

篇首语：本文由小编为大家整理，主要介绍了获取SQL执行计划的常见几种方法相关的知识，希望对你有一定的参考价值。

参考技术A 1. 预估执行计划 - Explain Plan
Explain plan以SQL语句作为输入，得到这条SQL语句的执行计划，并将执行计划输出存储到计划表中。
首先，在你要执行的SQL语句前加explain plan for，此时将生成的执行计划存储到计划表中，语句如下：
explain plan for SQL语句
然后，在计划表中查询刚刚生成的执行计划，语句如下：
select * from table(dbms_xplan.display);
注意：Explain plan只生成执行计划，并不会真正执行SQL语句，因此产生的执行计划有可能不准，因为：
1）当前的环境可能和执行计划生成时的环境不同；
2）不会考虑绑定变量的数据类型；
3）不进行变量窥视。
2. 查询内存中缓存的执行计划 （dbms_xplan.display_cursor）
如果你想获取正在执行的或刚执行结束的SQL语句真实的执行计划（即获取library cache中的执行计划），可以到动态性能视图里查询。方法如下：
1）获取SQL语句的游标
游标分为父游标和子游标，父游标由sql_id(或联合address和hash_value)字段表示，子游标由child_number字段表示。
如果SQL语句正在运行，可以从v$session中获得它的游标信息，如：
select status, sql_id, sql_child_number from v$session where status='ACTIVE' and ....
如果知道SQL语句包含某些关键字，可以从v$sql视图中获得它的游标信息，如：
select sql_id, child_number, sql_text from v$sql where sql_text like '%关键字%"
2）获取库缓存中的执行计划
为了获取缓存库中的执行计划，可以直接查询动态性能视图v$sql_plan和v$sql_plan_statistics_all等，但更方便的方法是以sql_id和子游标为参数，执行如下语句：
select * from table(dbms_xplan.display_cursor('sql_id',child_number));
3）获取前一次的执行计划：
set serveroutput off
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
3. 查询历史执行计划（dbms_xplan.display_awr）
AWR会定时把动态性能视图中的执行计划保存到dba_hist_sql_plan视图中，如果你想要查看历史执行计划，可以采用如下方法查询：
select * from table(dbms_xplan.display_awr('sql_id');
4. 在用sqlplus做SQL开发是（Autotrace）
set autotrace是sqlplus工具的一个功能，只能在通过sqlplus连接的session中使用，它非常适合在开发时测试SQL语句的性能，有以下几种参数可供选择：
SET AUTOTRACE OFF ---------------- 不显示执行计划和统计信息，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ 只显示优化器执行计划
SET AUTOTRACE ON STATISTICS -- 只显示统计信息
SET AUTOTRACE ON ----------------- 执行计划和统计信息同时显示
SET AUTOTRACE TRACEONLY ------ 不真正执行，只显示预期的执行计划，同explain plan

5. 生成Trace文件查询详细的执行计划 （SQL_Trace, 10046）
SQL_TRACE作为初始化参数可以在实例级别启用，也可以只在会话级别启用，在实例级别启用SQL_TRACE会导致所有进程的活动被跟踪，包括后台进程及所有用户进程，这通常会导致比较严重的性能问题，所以在一般情况下，我们使用sql_trace跟踪当前进程，方法如下：
SQL>alter session set sql_trace=true;
...被跟踪的SQL语句...
SQL>alter session set sql_trace=false;
如果要跟踪其它进程，可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION来实现，例如：
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,true) --开始跟踪
SQL> exec dbms_system.set_sql_trace_in_session(sid,serial#,false) --结束跟踪
生成trace文件后，再用tkprof 工具将sql trace 生成的跟踪文件转换成易读的格式，语法如下：
tkprof inputfile outputfile
10046事件是SQL_TRACE的一个升级版，它也是追踪会话，生成Trace文件，只是它里面的内容更详细，

Oracle 查询真实执行计划

什么是真实执行计划

获取Oracle的执行计划，有几种方式。（本文使用Oracle 11g XE版本，以及普通用户scott登录）

  • explain plan for
    有两个步骤：
    • explain plan for $SQL
    • select * from table(dbms_xplan.display);

    这一个方法可以在PLSQLDev的cmd窗口和sql窗口执行，同时不需要给用户授权。
    示例：

  • autotrace
    有两个步骤：
    • set autot on
    • 执行$SQL

    但普通用户需要授权，才能执行。不了解授权过程，知道的同学可以留言。
    并且在我的PLSQLDev里无法执行，必须得到SqlPlus才能执行。
    示例：

    可以看到多了一些统计信息，不过不是十分直观。

但是，上面两种方法

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

这里说的带有A-Time的执行计划，即是本文所说的真实执行计划。

  • 真实执行计划
    这种方法需要对普通用户授权，可用以下语句一次性授权。
grant select any dictionary to scott;

这种执行计划结果如下：

可以看到多了A-Rows、A-Time等字段。

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

Starts、A-Rows、A-Time这几个字段很直观，对于非数据库开发人员来说，容易理解。
真实执行计划的获取方式 ，下面会介绍。

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

如何获取真实的执行计划

首先要有访问动态性能视图的权限，可用以下语句授权

grant select any dictionary to scott;

有了权限之后，分以下几步走

1.alter session set statistics_level = all;
（这一步对当前会话窗口有效，可以不做，下面解释）

2.执行语句;
（如果上一步不做，则需要在语句中添加 /+ gather_plan_statistics /。 例如：select /+ gather_plan_statistics / * from dual; 但这样做比较麻烦 ，每条语句都要加上，不推荐）

3.找出执行语句的SQL ID，例如：
select v.last_active_time, v.* from v$sql v where v.last_active_time > to_date("2019/10/02 17:00:00", "yyyy/mm/dd hh24:mi:ss") and v.sql_text like "select * from %" and v.parsing_schema_name = "SCOTT" order by v.last_active_time desc;

参数根据实际情况修改，越精确越好。
可以得到如下计划，把SQL ID取出

4.根据SQL ID查出执行计划

select * from table(dbms_xplan.display_cursor('b8x994z12hax9',null,'allstats last'));

其中第一个参数就是第3步获得的SQL ID。可得到执行计划

复制出来贴到notepad++

可以看到已经有了实际执行的信息。
这个例子还比较简单，可以试试下面这个例子。

select d.dname, d.loc, e.empno, e.ename? from emp e, dept d?where e.deptno = d.deptno?order by d.dname, e.empno;

执行计划如下：

下面还有更多的信息，可以知道每一步操作的具体信息，比如两个表之间通过什么关联等。

总结

真实执行计划提供了SQL执行的真实信息，包括A-Time（真实时间）、A-Rows（真实行数）、Starts（步骤执行次数）等，对于非数据库开发人员来说，十分直观方便。我也借此在工作优化了10+ SQL，收获满满~
在这里要推荐一本书《SQL 优化核心思想》，罗炳森 黄超 钟侥 著。本文的所有文字引用，均摘自此书。我没有完全看懂这本书，但目前在工作中，已经够用了。

以上是关于获取SQL执行计划的常见几种方法的主要内容，如果未能解决你的问题，请参考以下文章