漫画戴夫·艾伦（Dave Allen）曾经讲过一个古老的笑话，一个旅行者问路人去某个城镇的路，路人只是说：“如果我是你，我就不会从这里开始。”当涉及到SQL执行计划时，如果你从错误的地方开始，那么你可能不会到达目的地。这篇文章的目的是盘点一下我认为收集SQL执行计划的最佳方法，这些方法可以确保你捕获真正有用的信息， 澄清一下我指的“有用的信息”是什么意思：我的意思是捕获一个SQL执行计划，它可以帮助你了解SQL是如何执行的，并弄清楚SQL语句执行慢的根源。SQL 执行计划揭示了 Oracle 数据库计划如何执行SQL 语句的大量信息。您并不需要熟悉 SQL 执行计划才能成为 Oracle 数据库专家，但我们大多数人都喜欢学习新事物，有时看看机器内部的东西很有趣。这篇文章中有很多观点，所以非常欢迎评论。
这又是一篇关于 SQL 执行计划的文章？
我知道书中有很多关于生成SQL执行计划的文章和章节，但我这篇文章的目的是要把众多方法提炼成在大多数情况下都管用的少数几种方法，大多数的执行计划钟经常存在3类问题：
- 缺少的重要信息
- 存在一些盲点——经常被提及但又经常被忽略的有效方法
- 缺少如果你不要求它，你就不会看到的有用的信息
您可能会认为自己是唯一一个认为SQL执行计划难以理解的人，但实际上，它们的确很难理解——它们的庞大规模可能令人生畏。如果缺少某些细节，则几乎无法对执行计划进行评估。某些SQL的转换和操作给出的数字（例如行数）与预期不一致，这会让你很困惑。下面的示例生成了许多有用的信息，但刚开始您可能会觉得有些信息没有必要。这些信息被分解为若干个子集（或通过企业管理器的图像界面展示），因此便于分开解读或干脆先忽略。我没有在下面列出所有示例的输出，因为它会占用太多空间，所以我将一些自包含脚本上传到 GitHub（https://github.com/oracle/oracle-db-examples/tree/master/optimizer/execution_plans/part0）。
例子
下面的例子是我对不同的情况给出的不同的建议。
示例 A
如果可以在SQL Plus 或者 SQLcl 里执行SQL：
select e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); SELECT *FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALL +OUTLINE'));
或者，如果您不想执行SQL：
explain plan forselect e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); SELECT *FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE'));
这个例子的一个重要特点是我使用的是 FORMAT=>'ALL +OUTLINE'，它记录了“ALL”格式，并简要提到了“OUTLINE”。
示例 B
如果无法单独运行SQL，仍可以使用如下所示的SQL从游标缓存中获取计划信息：
SELECT *FROM table(DBMS_XPLAN.DISPLAY_CURSOR( SQL_ID=>'the_SQL_ID', CHILD_NUMBER=>the_child_number, FORMAT=>'ALL +OUTLINE'));
这里需要SQL的SQL_ID和CHILD_NUMBER做为输入参数，有很多方法可以得到这两个参数，如果您有DBA权限，那么您可以在V$SQL中找到这两个参数：
select /* MY_TEST_QUERY */ e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); select sql_id, child_number, sql_textfrom v$sql where sql_text like '%MY_TEST_QUERY%'and sql_text not like '%v$sql%';
上面的计划不包含任何运行时信息，因此您不会看到计划的每个部分执行所花费的时间或实际处理的行数。例如，“Rows”是估计值，它不会告诉您实际处理了多少行。如果您能收集到运行时信息，对了解SQL的实际执行效率会有很大的帮助，那么如何去得到它呢？
示例 C
您可以使用hint来收集运行时信息：
select /*+ gather_plan_statistics */ e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); SELECT *FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));
这将向您显示统计信息，例如实际处理的行数（A-Rows），而不仅仅是估计值（E-Rows），它还包括一个名为“Starts”的列，该列告诉您每个步骤执行了多少次。如果您想了解计划，A-Rows, E-Rows 和Starts都非常有用。
示例 D
如果不想更改SQL文本以添加hint，可以设置一个参数：
alter session set statistics_level='ALL'; select e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); SELECT *FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));
示例 E
DBMS_XPLAN 'ALLSTATS LAST' 不会在执行SQL时为您提供运行时统计信息的连续视图，但 SQL Monitor 解决了这个问题。它需要Oracle Tuning包，因此请参考数据库版本的许可证用户指南（https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dblic/Licensing-Information.html）。此工具非常适合生成计划和监视 SQL，它可通过企业管理器的性能中心的图像界面调用，也可以在命令行上调用：
select /*+ MONITOR */ e.ename,r.rnamefrom employees ejoin roles r on (r.id = e.role_id)join departments d on (d.id = e.dept_id)where e.staffno <= 10and d.dname in ('Department Name 1','Department Name 2'); -- Get the SQL ID of the query we just executedselect prev_sql_idfrom v$sessionwhere sid=userenv('sid')and username is not nulland prev_hash_value <> 0; PREV_SQL_ID-------------an05rsj1up1k5 set linesize 250 pagesize 0 trims on tab off long 1000000column report format a220 select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id=>'an05rsj1up1k5',report_level=>'ALL') reportfrom dual;
输出：
SQL_ID 参数是可选的，但我通常显式设置它，因为系统中可能有多个长时间运行的SQL，因此默认报告有时会选取不同的 SQL 语句。数据库会自动调用SQL Monitor 监控长时间运行的SQL，但这里我使用了 MONITOR 提示，因为这个SQL执行的时间很短。在SQL执行时监视SQL可能很有用，因为您可以观察其进度，您可以在另一个会话中监视SQL，并查看其统计信息不断更新。您不必等待它完成才能弄清楚SQL的哪个部分花费了很长时间。请注意，您可以像上面的示例 B一样使用“ALL +OUTLINE ”获得更加详细的信息。您甚至可以使用命令行生成活动的 HTML 报告。只需像这样运行报告：
-- spool output to a file, then…select DBMS_SQL_MONITOR.REPORT_SQL_MONITOR (sql_id =>'an05rsj1up1k5', report_level =>'all', type =>'ACTIVE') reportfrom dual;
然后在浏览器中打开生成的报告，则会得到如下所示的交互式 HTML 页面：
请注意，浏览器需要访问互联网，因为 HTML 报告会下载一些外部资源。
示例 F
我知道你们中的许多人都喜欢命令行（我也一样），但您也应该在Oracle Enterprise Manager Performance Hub中尝试一下SQL Monitor。在这里访问SQL Monitor的报告要容易得多，并且它们将随着SQL执行而不断刷新。此外，保存这些报告并将其发送给其他人也很容易。只需使用“保存”按钮（下面用红色圈出）。
如果您点击“Plan”选项卡，如果计划不是太大，看图形视图会更易读。我喜欢选择“Rotate”来给我一棵垂直方向的树。啊哈！现在我可以看到连接的左侧和右侧的实际意义了！总的来说，您应该从左下角开始阅读这个执行树。我以后可能会另外写篇文章阐述这个问题。在下面的示例中，与上面的示例一样，数据库首先读取 DEPARTMENTS表，再把读取的记录与 EMPLOYEE表进行连接，再将输出的结果和 ROLES表进行连接。
示例 G
最后，还有SQL Developer工具！
使用DBMS_XPLAN：
总结
如果您想保存和分析一个执行计划，您可以对比以下一些选项：
