Oracle10中如何用dbms_xplan分析执行计划

Oracle CBO中选择执行计划，有时候不是最优执行计划，造成该原因通俗讲是因为CBO计算出来的处理行数量，与实际处理行的数量相差很大。在9i中，两者的值需要分别从v$sql_plan.cardinality和v$sql_plan_statistics.last_output_rows提取，或者通过tkprof分析10046输出rows。 10g中引入了dbms_xplan.display_cursor很好地解决了这个问题。

       案例：
             SQL>select /*+ gather_plan_statistics */  distinct owner
                       from v$access
           
                Elapsed: 00:03:22.45
           
              说明：gather_plan_statistics主要是收集语句执行的时候，行的统计信息。

       分析：

           SQL> select * from table(dbms_xplan.display_cursor(
                       '6ht2q648nq5xa', '0', 'ALL ALLSTATS'));

           说明：dbms_xplan.display_cursor( sql_id, cursor_child_no, format)中
                        format表达式
                             IOSTATS: IO统计
                             MEMSTATS: 使用的PGA统计
                             ALLSTATS: IOSTATS+MEMSTATS
                             LAST: 游标中最后执行
                             ALL: 输出中显示Query block/Object Alias、Predicate 信息和         
                                       Column Projection 信息；
                             Advanced: ALL + Outline信息；
                             Outline: 输出显示Outline 信息和Predicate信息；

           输出执行计划：                 
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |    105 | 14175 |     1 (100)| 00:00:01 |      3 |00:00:27.72 |       |       |          |
|   2 |   NESTED LOOPS             |                 |      1 |    105 | 14175 |     0   (0)|          |    108 |00:00:23.35 |       |       |          |
|   3 |    NESTED LOOPS            |                 |      1 |     10 |   820 |     0   (0)|          |    108 |00:00:23.34 |       |       |          |
|   4 |     MERGE JOIN CARTESIAN   |                 |      1 |    100 |  5700 |     0   (0)|          |  24650 |00:00:00.11 |       |       |          |
|*  5 |      FIXED TABLE FULL      | X$KSUSE         |      1 |      1 |    19 |     0   (0)|          |    170 |00:00:00.01 |       |       |          |
|   6 |      BUFFER SORT           |                 |   170|   100|  3800 |     0   (0)|          | 24650|00:00:00.06 | 18432 | 18432 |     1/0/0|
|   7 |       FIXED TABLE FULL     | X$KGLDP         |      1 |    100 |  3800 |     0   (0)|          |    145 |00:00:00.01 |       |       |          |
|*  8 |     FIXED TABLE FIXED INDEX| X$KGLLK (ind:1) |  24650 |      1 |    25 |     0   (0)|          |    108 |00:00:27.60 |       |       |          |
|*  9 |    FIXED TABLE FIXED INDEX | X$KGLOB (ind:1) |    108 |     10 |   530 |     0   (0)|          |    108 |00:00:00.01 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

           说明：
             1、E-ROWs: 执行计划中所预测的行处理数，与explain plan for中rows的数
                                      量相同；

             2、A-ROWS:实际处理的行数量与10046中rows source operation值相同

             3、0Mem: 与v$sql_worarea.ESTIMATED_OPTIMAL_SIZE值相同

              4、1Mem:与v$sql_worarea.ESTIMATED_ONEPASS_SIZE值相同
           
             分析结果：
                    我们看到buffer sort中 E-ROWs=100，A-ROWS=24650，如果这两个比
            值超过100以上就说明CBO采用的统计分析计算和实际相差很大，有可能是
            统计分析不对，或者是统计分析正确，但采用的模型计算与实际情况相差比
            较大。
                    本案例中是因为fixed table没有进行统计分析，具体处理步骤见前一篇博
            客。

     处理后结果：
         
            SQL> set serveroutput off
            SQL> SElect /*+ gather_plan_statistics */ distinct owner from v$access;
 
               Elapsed: 00:00:00.07
           
             SQL> select * from table(
                        dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Starts | E-Rows | A-Rows |   A-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH UNIQUE               |                 |      1 |      4 |      3 |00:00:00.06 |       |       |          |
|   2 |   HASH JOIN                |                 |      1 |    403 |    495 |00:00:00.06 |  1236K|  1236K| 1474K (0)|
|   3 |    FIXED TABLE FULL        | X$KSUSE         |      1 |    170 |    170 |00:00:00.01 |       |       |          |
|   4 |    NESTED LOOPS            |                 |      1 |    403 |    495 |00:00:00.05 |       |       |          |
|   5 |     HASH JOIN              |                 |      1 |    403 |    495 |00:00:00.03 |   963K|   963K| 1258K (0)|
|   6 |      FIXED TABLE FULL      | X$KGLLK         |      1 |    403 |    407 |00:00:00.01 |       |       |          |
|   7 |      FIXED TABLE FULL      | X$KGLDP         |      1 |    567 |   1390 |00:00:00.02 |       |       |          |
|   8 |     FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |    495 |      1 |    495 |00:00:00.01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------
                                                           
              从输出结果中没有出现(A-ROWS/ E-ROWs)>100             
from: http://space.itpub.net/354732/viewspace-606354