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

环境：OEL+Oracle 10.2.0.5 Rac

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

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

答案应该是硬解析；

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

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> create table tt as select * from dba_objects;

Table created.

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

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> 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> select object_id,OBJECT_NAME from tt where object_id=10;
  
 OBJECT_ID