创建索引前后执行计划的变更和软硬解析的比较

创建索引前后执行计划的变更和软硬解析的比较

 

环境：OEL+Oracle 10.2.0.5 Rac

今天在itpub上回答一个网友的提问，RT：我第一次执行了一条sql之后，这条sql通过了硬解析，得到了执行计划，当再次执行这条sql时，会进行软解析是吧，不会通过优化器得到新的执行计划。如果我增加了一条索引，通过索引执行这条sql更好，在执行这条sql是进行软解析吗？（统计信息的改变，会导致sql进行硬解析吗？）

我当时的回答是：有索引了，统计信息变了。走索引了，执行计划变了。 但是软硬解析是对于SQL语句而言的吧？只要共享池中存在此SQL副本，将直接执行软解析；个人认为未经易做图yze表前，会被软解析

答案应该是硬解析；

中午午休的时候，趴那儿回顾了一下这个案例，但是思前想后总感觉有点不对，一切以事实说话，决定起来测试一下；测试过程和结果如下：

[sql] 
SQL> show user  
USER is "SYS"  
SQL> drop index tt_idx;  
drop index tt_idx  
           *  
ERROR at line 1:  
ORA-01418: specified index does not exist  
  
  
SQL> drop table tt purge;  
drop table tt purge  
           *  
ERROR at line 1:  
ORA-00942: table or view does not exist  

 

 

创建一张新表TT：

[sql] 
SQL> create table tt as select * from dba_objects;  

 

  

Table created.  

 

打开autotrace并开始第一次根据条件检索新表tt：

[sql] 
SQL> set autotrace on;    
SQL> select object_id,object_name from tt where object_id=10;    
    
 OBJECT_ID       OBJECT_NAME</span>    
----------       --------------------------------------  
        10       C_USER#    
    
Execution Plan    
----------------------------------------------------------    
Plan hash value: 264906180    
    
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    
|*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    
--------------------------------------------------------------------------    
    
Predicate Information (identified by operation id):    
---------------------------------------------------    
    
   1 - filter("OBJECT_ID"=10)    
    
Note    
-----    
   - dynamic sampling used for this statement    
    
    
Statistics    
----------------------------------------------------------    
         68  recursive calls    
          0  db block gets    
        785  consistent gets    
        701  physical reads    
          0  redo size    
        481  bytes sent via SQL*Net to client    
        400  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 object_id,object_name from tt where object_id=10;    
    
 OBJECT_ID      OBJECT_NAME   
----------      ---------------------------------------    
        10      C_USER#    
    
Execution Plan    
----------------------------------------------------------    
Plan hash value: 264906180    
    
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    
|*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    
--------------------------------------------------------------------------    
    
Predicate Information (identified by operation id):    
---------------------------------------------------    
    
   1 - filter("OBJECT_ID"=10)    
    
Note    
-----    
   - dynamic sampling used for this statement    
    
    
Statistics    
----------------------------------------------------------    
          0  recursive calls    
          0  db block gets    
        707  consistent gets    
          0  physical reads    
          0  redo size    
        481  bytes sent via SQL*Net to client    
        400  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] 
SQL> select object_id,OBJECT_NAME from tt where object_id=10;  
    
 OBJECT_ID          OBJECT_NAME  
----------          -------------------------------------------------    
        10          C_USER#    
    
Execution Plan    
----------------------------------------------------------    
Plan hash value: 264906180    
    
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    
|*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    
--------------------------------------------------------------------------    
    
Predicate Information (identified by operation id):    
---------------------------------------------------    
    
   1 - filter("OBJECT_ID"=10)    
    
Note    
-----    
   - dynamic sampling used for this statement    
    
    
Statistics    
----------------------------------------------------------    
          4  recursive calls    
          0  db block gets    
        779  consistent gets    
          0  physical reads    
          0  redo size    
        481  bytes sent via SQL*Net to client    
        400  bytes received via SQL*Net from client    
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)    
          0  sorts (disk)    
          1  rows processed    
    
SQL> select OBJECT_ID,OBJECT_NAME from tt where object_id=10;    
  
 OBJECT_ID      OBJECT_NAME  
----------      ----------------------------------------------    
        10      C_USER#    
    
Execution Plan    
----------------------------------------------------------    
Plan hash value: 264906180    
    
--------------------------------------------------------------------------    
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |    
--------------------------------------------------------------------------    
|   0 | SELECT STATEMENT  |      |     8 |   632 |   156   (1)| 00:00:02 |    
|*  1 |  TABLE ACCESS FULL| TT   |     8 |   632 |   156   (1)| 00:00:02 |    
--------------------------------------------------------------------------    
    
Predicate Information (identified by operation id):    
---------------------------------------------------    
    
   1 - filter("OBJECT_ID"=10)    
    
Note    
-----    
   - dynamic sampling used for this statement    
    
    
Statistics    
----------------------------------------------------------    
          4  recursive calls    
          0  db block gets    
        779  consistent gets    
          0  physical reads    
          0  redo size    
        481  bytes sent via SQL*Net to client    
        400  bytes received via SQL*Net from client