执行计划-2：查看更多的信息

在 Part 1中我们了解了两种获取执行计划的方式，第一种是优化器预测它将会怎么执行，通过以下方法查询获取：

explain plan for {sql statement}
select * from table(dbms_xplan.display);

第二种是优化器在我们执行语句后真正的执行路径，通过以下方法查询获取：

set serveroutput off
{sql statement}
select * from table(dbms_xplan.display_cursor);

事实上，在 Part 1 中，我故意使用两种获取执行计划的方法，来证明对同一条存在绑定变量的语句可以生成两种截然不同的执行计划。

Part 2中我们会评估几种查看真实执行计划的方法，但是首先我们关注目前我们接触到函数的其他调用选项，这会使我们对执行计划在最终用户的环境中的复现更加自信。

Format Options

一般来说，如果想要执行计划与最终用户环境一致的话，我们需要他们生产数据的备份，对应的统计信息，系统的参数还有一致的输入。 生产数据以及统计信息一般都是一样的，所以我们真正需要关注的就是客户环境是否不同以及语句输入； 我们可以通过数据库来帮助我们获取在某一刻的执行信息。

这里也有一个小提醒，需要注意数据、统计信息还有谓词条件的及时同步。 举个例子，如果你的生产数据是好几个星期前的，那么你执行语句时要根据几个星期前用户当时执行的语句去执行； 因为如果条件中存在 SYSDATE，那么就不能很好的模拟当时的环境。

在 Part 1中提到过的，调用 dbms_xplan.display_cursor()可以赋值三个参数，它们分别是 sql_id， child_number和 formatting option。 有两个 formatting option参数能有效的帮助你解决最近一次出现的问题， "peeked_binds"和 "outline"。 前者会列出 (只需要一点运气 )优化查询时使用的真实的值，后者会提供一个 hint的列表，如果我们为了这个查询创建了一个存储大纲或者 SQL Plan Baseline，通过这些 hint我们可以发现优化器的环境是否不同。

这里有个小例子，通过对视图 dba_extents 的 copy 表的查询来演示使用和输出。

alter session setworkarea_size_policy = manual;
alter session setsort_area_size = 10485760;
alter session setoptimizer_mode = first_rows_10;
alter session set"_hash_join_enabled" = false;
set serveroutput off
variable m_owner varchar2(32)
variable m_object varchar2(32)
execute :m_owner :='TEST_USER'
execute :m_object := 'T1'
select /*+ tracking */ * from t1 
where owner = :m_owner 
and segment_name =:m_object 
order by extent_id;
select * from table(
dbms_xplan.display_cursor(null,null,'outlinepeeked_binds')
);

我修改了一些优化器相关的参数， set serveroutput off不会导致 dbms_xplan.display_cursor()输出关于 dbms_output.get_lines()的调用，可以在查询时通过定义的一组参数使用绑定变量。 之后我调用 display_cursor(null, null, 'outlinepeeked_binds')来展示我最近的查询并且添加可用的绑定变量的值到 Outline/SQL Pan Management信息中。 这是输出结果：

SQL_ID  0wwbn4bhvrrxj, child number 0
-------------------------------------
select /*+ tracking */  * from t1 where owner = :m_owner and
segment_name = :m_object order by extent_id
Plan hash value: 3684778271
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT ORDER BY               |       |     1 |    65 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    65 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T1_I2 |     2 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      FIRST_ROWS(10)
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."SEGMENT_NAME"))
      END_OUTLINE_DATA
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :M_OWNER (VARCHAR2(30), CSID=178): 'TEST_USER'
   2 - :M_OBJECT (VARCHAR2(30), CSID=178): 'T1'
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNER"=:M_OWNER)
   3 - access("SEGMENT_NAME"=:M_OBJECT)

这个例子中，我们很幸运的获得了优化器在执行计划信息中记录的所有绑定变量的值，包括 CSID(charcater set id) 这种细节，当你开使用 NLS 字符集时，有时候会造成一些意外。

我们先看 ”Outline Data”，可以看到 hint “first_rows(10)”是唯一被记录的我们会话的变更，剩下的变更都没有被捕捉到； format option也给了我们一个提示，我们可以在这个查询开始试验时需要做一些额外的检查，来确认我们能否获得一个更好的计划。 这次我们假设其他人已经执行过这个查询了，在我们分析前，我们需要根据一些特殊的文本来追踪 (这就是我为什么在语句中添加 /*+ tracking */，这个实际上不是 hint)。

column sql_id new_valuem_sql_id
column child_number new_valuem_child_no
select  sql_id,child_number, sql_text 
from   v$sql 
where   sql_textlike '%tracking%' 
and    sql_text not like '%v$sql%'
;
select * from table(
dbms_xplan.display_cursor('&m_sql_id',&m_child_no,'outlinepeeked_binds')
);
select 
name, value 
from 
v$sql_optimizer_env 
where 
sql_id      = '&m_sql_id' 
and child_number =&m_child_no 
and isdefault   = 'NO'
;

这些代码都能在 SQL*PLUS里执行，我已经设置了一些列定义用来捕捉这个查询语句上次返回的值， SQL_ID和 CHILD_NUMBER的值是我们所需要的。 在这里我假设这条语句仅仅只有一个子游标。 就像你看到的，我使用 '&替换 '将获取到的值带入到 display_cursor()的调用与 v$sql_optimizer_env的查询中。 这里不显示对 display_cursor()的调用结果 (输出结果与前面的重复 )，只显示对 v$sql_optimizer_env 的查询结果。

NAME                                    VALUE
-----------------------------------------------------------------
hash_area_size                          20971520
sort_area_size                          10485760
optimizer_mode                          first_rows_10
_hash_join_enabled                      false
workarea_size_policy                    manual
_smm_auto_cost_enabled                  false

v$sql_optimizer_env视图保存了优化器环境的参数，这些参数在优化每一个子游标时都是生效的。 我选择了那些没有默认值，可以看到 Oracle输出了 6个参数，其中 4个是我们设置的，还有两个 :hash_area_size， _smm_auto_cost_enabled。 hash_area_size如果没有明确的设置过，它会等于两倍的 sort_area_size，当我们将 workarea_size_policy设置为 manual时， _smm_auto_cost_enabled参数也会自动的设置为 false。 在 11.2.0.4中有 330个优化器参数 (12.1.0.1中增加至 415个，这也是为什么很难从二手信息中获取到给定查询到执行路径原因之一 ),其中 50个参数是非隐藏参数，其余的大部分都是隐藏参数，只有在改变他们的默认值时才会展示在查询结果里。

我在简单的提下另一个 formatting 选项  "advanced'，奇怪的是他会给我们比 "all'选项更多的信息。 如果你仅仅想看执行查询时内存里所有的信息，那么你可以调用 display_cursor({sql_id},{child_number},’all’)(如果有许多子游标，可以将 child_number值设为 NULL，则会输出所有的语句 )。 之后如果需要解决问题时我们还会继续学习更多 formatting选项，不过目前我们要暂时先放下，来看看一些可以 "事后 "从生产系统获取执行计划的选项。 如果想获得更多可用的选项，可以查看 $ORACLE_HOME/rdbms/admin/dbmsxpln.sql.

AWR and Statspack

如果一个查询属于 "资源密集型 "的，那么他会出现在 statspack或 AWR里，并且它的执行计划以及一些统计会保存在资源库里。 在 OEM(Grid Control， Cloud Control)中有关于 AWR的图形化界面， $ORACLE_HOME/rdbms/admin中有 statspack和 AWR的 SQL接口。 statspack的脚本为 sprepsql.sql， AWR的为 awrsqrpt.sql。

如果你对于用命令行生成 AWR或者 statspack很熟悉，那么除了需要 SQL_ID(AWR)或者 "old hashvalues"(statspack)以外，几乎都是一样的。 报告会给你语句的全文，一些执行统计信息，在指定期间所有不同的执行计划，还有不同计划进入和离开 library cache的时间表。 这个计划不会输出绑定值， outline information，甚至谓词信息，就是很基础的执行计划还有一些性能的统计。 AWR在这方面比 statspack好，因为它分别聚合了不同执行计划的统计数据，所以更容易比较不同的执行计划。

另一个 AWR的优点是你可以通过调用 dbms_xplan.display_awr()获取 AWR里历史的执行计划。 这个调用需要 4个参数， SQL_ID， plan_hash_value， database id和 format optino； 最简单的调用就是只给定 SQL_ID的值，结果会输出 AWR中保存的所有这条 SQL的执行计划，你也可以选择输出你想要的执行计划。 就像调用 dbms_xplan.display_cursor() 一样，你也可以得到 outline information和优化器使用的绑定值，下面是个例子：

select * from table(
dbms_xplan.display_awr('9yaqj3djhwxa2',null, null,'outline peeked_binds')
);

关于使用 dbms_xplan查找和操作执行计划的内容方面还有很多变化，也随着 Oracle的新版本变得更多样化，最近的版本甚至可以输出计划中的不同点，其中一个最有用的小特性与如何填充 AWR有关而不是从 AWR中获取报告。 在 11g中，你可以将一个 SQL_ID标记为 "感兴趣 "，当 AWR快照进程在创建快照时会捕获任何关于该语句可用的信息。 因为除非该语句是 "top N"的语句，否则它不会出现在 AWR报告中，但是你可以使用 awrsqrpt.sql或者 dbms_xplan.display_awr()来获得计划。

如果你想标记某条 SQL ，你可以调用 dbms_workload_repository 包：

executedbms_workload_repository.add_colored_sql({sql_id})
--
-- 输出当前被标记的语句
--
select * fromwrm$_colored_sql;
--
-- 解除语句标记
--
executedbms_workload_repository.remove_colored_sql({sql_id})

这个过程的好处是 ( 甚至可以针对一些效率很高的语句 ) ，当性能较好的应用突然变慢了，如果你标记了所有进程执行的语句，那么你可以很轻松的找到出现什么问题以及执行计划是什么时候改变的。

Conclusion

尽管我们没有列举出所有生成或者获取执行计划的方法，但是我们学习了足够的原理保证我们可以十分精确地获得我们需要检查的语句执行时的环境，也学习了如何获得一些历史的、不在内存里的执行计划； 还有一种保证我们所需要的语句总是会被 AWR快照捕捉的方法。

下一篇文章我们会来学习一些基本的解释执行计划的原理，从简单的 select语句开始，使用视图来区分连接顺序，访问方式和连接方式。  

原文链接 : https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-2-things-to-see/

原文作者 : Jonathan Lewis

| 译者简介

林锦森·沃趣科技数据库技术专家

沃趣科技数据库工程师，多年从事Oracle数据库，较丰富的故障处理、性能调优、数据迁移及备份恢复经验