oracle 执行计划分步解读

参考案例一：

SQL> set  autotrace traceonly
SQL> create table t(col varchar2(20),col2 number);

表已创建。

SQL>
SQL> insert into t select rownum,rownum+1 from dual connect by level<=1000000;

已创建1000000行。

执行计划
----------------------------------------------------------
Plan hash value: 1731520519

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
|   1 |  COUNT                        |      |       |            |          |
|   2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
        892  recursive calls
      24493  db block gets
       4477  consistent gets
          0  physical reads
   22946168  redo size
        667  bytes sent via SQL*Net to client
        610  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

SQL> create index idx_t on t(col);

索引已创建。

SQL> select * from t where col=2 and col2=3;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   103 |  2575 |   553   (8)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| T    |   103 |  2575 |   553   (8)| 00:00:07 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)

Note
-----
   - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       2406  consistent gets
          0  physical reads
          0  redo size
        459  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL> select * from t where col='2' and col2=3;

执行计划
----------------------------------------------------------
Plan hash value: 1594971208

--------------------------------------------------------------------------------
-----

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
    |

--------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT            |       |   103 |  2575 |     8   (0)| 00:00
:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |   103 |  2575 |     8   (0)| 00:00
:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T |    26 |       |     3   (0)| 00:00
:01 |

--------------------------------------------------------------------------------
-----

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=3)
   2 - access("COL"='2')

Note
-----
   - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        112  recursive calls
          0  db block gets
         92  consistent gets
          2  physical reads
          0  redo size
        459  bytes sent via SQL*Net to client
        384  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

分析：

执行sql ：select * from t where col=2 and col2=3; 未走索引，逻辑读2406，获取一行，2406/1=2406 ,不符合获取单行记录小于5个逻辑读的标准。由此可见，该sql 还存在很大的调优空间。从谓词部分的执行计划可以看出使用了to_number类型转换。

predicate information 信息中有两种取值，分别是filter和access,一般而言索引读和hash连接的时候，体现为access。此时我们看到的filter。

通过修改查询sql ，重新查看执行计划发现predicate information部分发生了变化，逻辑读和获取行数的比率也发生了变化。

 

参考案例二：

SQL> set autotrace traceonly;
SQL> select * from t where col=2 and col2=3;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   103 |  2575 |   553   (8)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| T    |   103 |  2575 |   553   (8)| 00:00:07 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)

Note
-----
   - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement

统计信息
----------------------------------------------------------
        139  recursive calls
          0  db block gets
       2425  consistent gets
          3  physical reads
          0  redo size
        459  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> analyze table t compute statistics for table for all indexes for all indexed colu

表已分析。

SQL> select * from t where col=2 and col2=3;

执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |   552   (8)| 00:00:07 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    15 |   552   (8)| 00:00:07 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("COL2"=3 AND TO_NUMBER("COL")=2)

Note
-----
   - 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
         32  recursive calls
          0  db block gets
       2337  consistent gets
          0  physical reads
          0  redo size
        459  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

分析：

在执行计划中看到“   - dynamic sampling used for this statement” 说明进行了动态采样。但我们提前收集表的信息是，可以看到能够减少一些额外的开销。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24349972/viewspace-759076/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24349972/viewspace-759076/