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 优化核心思想》,罗炳森 黄超 钟侥 著。本文的所有文字引用,均摘自此书。我没有完全看懂这本书,但目前在工作中,已经够用了。

posted @ 2019-10-02 18:04  kingsleylam  阅读(1948)  评论(0编辑  收藏