Oracle执行计划显示 (网上的参考文章）

   生成和显示SQL语句的执行计划，是大多数DBA、SQL开发人员以及性能优化专家经常做的工作，因为执行计划能够提供SQL语句性能相关的信息。
   执行计划解释了SQL语句执行的详细过程，这个过程记录了一系列的数据库操作以及每个操作涉及到的数据行数和生成的数据行数。
   优化器使用查询转换和物理优化技术决定数据库操作顺序和过程实现。

执行计划通常以扁平的表格形式呈现，是一个事实树形结构。下面是一个基于SH模式中SALES、PRODUCTS表生成的查询

       1   HASH GROUP BY
       2    HASH JOIN
       3     TABLE ACCESS FULL   PRODUCTS
       4     PARTITION RANGE ALL
       5      TABLE ACCESS FULL  SALES
    ——————————————

dbms_xplan.display使用的参数有：

    plan table name (默认 'PLAN_TABLE')
    statement_id (默认 null),
    format (默认 'TYPICAL')

更详细的信息可以查看$ORACLE_HOME/rdbms/admin/dbmsxpln.sql。

例2：生成和显示会话中最后执行的SQL语句的执行计划

    SQL> select prod_category, avg(amount_sold)
     2   from sales s, products p
     3   where p.prod_id = s.prod_id
     4   group by prod_category;

    no rows selected

    SQL> select plan_table_output
     2   from table(dbms_xplan.display_cursor(null, null, 'basic'));

    ——————————————
     Id   Operation              Name
    ——————————————
       0  SELECT STATEMENT
       1   HASH GROUP BY
       2    HASH JOIN
       3     TABLE ACCESS FULL   PRODUCTS
       4     PARTITION RANGE ALL
       5      TABLE ACCESS FULL  SALES
    ——————————————

dbms_xplan.display_cursor使用的参数有：

    SQL ID (默认 null, null意味着当前会话中最后执行的SQL语句)
    child number (默认 0),
    format (默认 'TYPICAL')

例3：显示任何其他语句的执行计划

直接提供SQL_ID：

    SQL> select plan_table_output from
     2   table(dbms_xplan.display_cursor('fnrtqw9c233tt',null,'basic'));

间接查询获取SQL_ID：

    SQL> select plan_table_output
     2   from v$sql s,
     3   table(dbms_xplan.display_cursor(s.sql_id,
     4                                  s.child_number, 'basic')) t
     5   where s.sql_text like 'select PROD_CATEGORY%';

例4：根据SQL Plan Baseline显示执行计划。SQL Plan Baseline是Oracle 11g中引入的新概念，用于支持SQL Plan Management (SPM)特性。

    SQL> alter session set optimizer_capture_sql_plan_baselines=true;

    Session altered.

    SQL> select prod_category, avg(amount_sold)
     2   from sales s, products p
     3   where p.prod_id = s.prod_id
     4   group by prod_category;

    no rows selected

假如上述语句执行超过一次，将会产生一个该语句的SQL Plan Baseline，可以通过下面的查询进行确认：

    SQL> select SQL_HANDLE, PLAN_NAME, ACCEPTED
     2   from dba_sql_plan_baselines
     3   where sql_text like 'select prod_category%';

    SQL_HANDLE                     PLAN_NAME                      ACC
    —————————— —————————— —
    SYS_SQL_1899bb9331ed7772       SYS_SQL_PLAN_31ed7772f2c7a4c2  YES

上面创建的SQL Plan Baseline可以通过下面的方式显示：

直接提供SQL_HANDLE：

    SQL> select t.* from
     2   table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_1899bb9331ed7772',
     3                                              format => 'basic')) t;

间接查询获取SQL_HANDLE：

    SQL> select t.*
     2       from (select distinct sql_handle
     3             from dba_sql_plan_baselines
     4             where sql_text like 'select prod_category%') pb,
     5       table(dbms_xplan.display_sql_plan_baseline(pb.sql_handle,
     6                                                  null,'basic')) t;

这两条语句的输出结果如下：

    —————————————————————————-
    SQL handle: SYS_SQL_1899bb9331ed7772
    SQL text: select prod_category, avg(amount_sold) from sales s, products p
              where p.prod_id = s.prod_id group by prod_category
    —————————————————————————-
    Plan name: SYS_SQL_PLAN_31ed7772f2c7a4c2
    Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
    —————————————————————————-

    Plan hash value: 4073170114
    ———————————————————
     Id   Operation                 Name               
    ———————————————————
       0  SELECT STATEMENT                             
       1   HASH GROUP BY                               
       2    HASH JOIN                                  
       3     VIEW                   index$_join$_002   
       4      HASH JOIN                                
       5       INDEX FAST FULL SCAN PRODUCTS_PK        
       6       INDEX FAST FULL SCAN PRODUCTS_PROD_CAT_IX
       7     PARTITION RANGE ALL                       
       8      TABLE ACCESS FULL     SALES              
    ———————————————————

格式化

格式化参数可以进行高度定制，可以根据实际需要输出适当的信息。高级别的选项有：

    Basic     输出的执行计划包括操作、选项和对象名（表、索引、物化视图等）
    Typical   输出的执行计划包括Basic内容，加上和优化器相关的内部信息，如代价、大小、基数等，具体包括执行计划中的每个操作，
           优化器计算出来的每个操作的代价，每个操作返回的数据行数等，同时还会包括操作中所使用的评估谓词。Oracle CBO中存在两种谓词：
           ACCESS和FILTER。ACCESS表示根据查询条件，使用索引提取相关的数据块。FILTER表示数据块提取之后的评估。
    All       输出的执行计划包括Typical内容，加上每个操作生成的表达式列表、提示别名、查询块的名称。

低级选项允许包括或者排除诸如谓词、代价之类的细节，举例如下：

    SQL> select plan_table_output
     2   from table(dbms_xplan.display('plan_table',null,'basic +predicate +cost'));

    ——————————————————-
     Id   Operation              Name      Cost (%CPU)
    ——————————————————-
       0  SELECT STATEMENT                    17  (18)
       1   HASH GROUP BY                      17  (18)
    *  2    HASH JOIN                         15   (7)
       3     TABLE ACCESS FULL   PRODUCTS      9   (0)
       4     PARTITION RANGE ALL               5   (0)
       5      TABLE ACCESS FULL  SALES         5   (0)
    ——————————————————-

    Predicate Information (identified by operation id):
    —————————————————
    2 – access("P"."PROD_ID"="S"."PROD_ID")

    select plan_table_output from
    table(dbms_xplan.display('plan_table',null,'typical -cost -bytes'));

    —————————————————————————-
     Id   Operation              Name      Rows  Time      Pstart Pstop
    —————————————————————————-
       0  SELECT STATEMENT                    4  00:00:01             
       1   HASH GROUP BY                      4  00:00:01             
    *  2    HASH JOIN                       960  00:00:01             
       3     TABLE ACCESS FULL   PRODUCTS   766  00:00:01             
       4     PARTITION RANGE ALL            960  00:00:01      1     16
       5      TABLE ACCESS FULL  SALES      960  00:00:01      1     16
    —————————————————————————-

    Predicate Information (identified by operation id):
    —————————————————
    2 – access("P"."PROD_ID"="S"."PROD_ID")

注解部分

dbms_xplan包生成执行计划时会将注释显示在NOTE部分，比如查询优化过程中使用了动态抽样或者星形转换应用在查询中。比如SALES表没有统计信
息，优化器在分析代价时将会使用动态抽样，执行计划将会把这一过程记录在NOTE部分。

    SQL> select plan_table_output
     2   from table(dbms_xplan.display('plan_table',null,'basic +note'));

    ——————————————
     Id   Operation              Name   
    ——————————————
       0  SELECT STATEMENT              
       1   HASH GROUP BY                
       2    HASH JOIN                   
       3     TABLE ACCESS FULL   PRODUCTS
       4     PARTITION RANGE ALL        
       5      TABLE ACCESS FULL  SALES  
    ——————————————

    Note
    —–
    - dynamic sampling used for this statement

绑定窥视

在生成执行计划时，优化器会考虑绑定变量的实际值，这一过程就是所谓的绑定变量窥视。就像我们前面提到的那样，
V$SQL_PLAN中记录的执行计划考虑了绑定变量值而EXPLAIN PLAN命令生成的执行计划并没有考虑这一点。从Oracle10gR2开始，dbms_xplan包可以
显示用于生成特定计划或者游标的绑定变量值，只需要在display_cursor()函数时加上'+peeked_binds'参数即可。

下面的例子展示了一点：

    SQL> variable pcat varchar2(50)
    SQL> exec :pcat := 'Women'

    SQL> select PROD_CATEGORY, avg(amount_sold)
     2   from sales s, products p
     3   where p.PROD_ID = s.PROD_ID
     4   and prod_category != :pcat
     5   group by PROD_CATEGORY;

    SQL> select plan_table_output
     2   from table(dbms_xplan.display_cursor(null,null,'basic +PEEKED_BINDS'));

    ——————————————
     Id   Operation              Name   
    ——————————————
       0  SELECT STATEMENT              
       1   HASH GROUP BY                
       2    HASH JOIN                   
       3     TABLE ACCESS FULL   PRODUCTS
       4     PARTITION RANGE ALL        
       5      TABLE ACCESS FULL  SALES  
    ——————————————

    Peeked Binds (identified by position):
    ————————————–

    1 – :P CAT (VARCHAR2(30), CSID=2): 'Women'