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

环境：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          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  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed  
  
SQL>



如上，SQL语句不同，便会执行硬解析。

ps：由于没有对表TT做分析，所以以上执行计划中的ROWS值为8而不是1（只要一条返回记录）；

继续测试：

在表TT的object_id列添加索引TT_IDX，并执行上面一样的SQL语句进行检索：

SQL> create index tt_idx on tt(object_id);

Index created.

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: 2072537773
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    79 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TT     |     1 |    79 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TT_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


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

   2 - access("OBJECT_ID"=10)

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         79  consistent gets
          1  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共享区有该语句的副本，但是依然执行了硬解析，且优化器走了索引；
在10g及以上版本中，如果表没有经过分析，oracle 自动通过动态采样的方式来收集分析数据。



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

版权所有，转载请注明作者及原文链接，否则追究法律责任!

QQ:      584307876

作者:    Seven

原文链接:http://blog.csdn.net/sunjiapeng/article/details/10592205

邮箱:     seven_ginna@foxmail.com

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/james1207/p/3292235.html