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

灰太狼 2023-06-05 12:54 4阅读 0赞

在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

**| 译者简介**

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