DBAORACLE

Explain plan is always doing a hard parse.

ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select /*+ dynamic_sampling(4) */ * from gtt where x = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2946670127
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX | 1 | 13 | 1 (0)| 00:00:01
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"=1)
Note
-----
- dynamic sampling used for this statemen

It says "you would do a index range scan IF YOU WERE TO HARD PARSE RIGHT NOW"

²Î¿¼Temp tables and CBO statistics

ÏÝÚå°¸ÀýCBO dynamic sample °ó¶¨±äÁ¿ÓëPEEKING --CBO µÄÍ´

»¹ÊÇsql_trace×î׼ȷ¡£

An important thing to understand about the EXPLAIN PLAN command is that it shows you the execution plan Oracle might use if the statement were to be hard parsed right now¡ªwhich could be different from the true execution plan currently in use for the very same statement.

When you use the EXPLAIN PLAN command to see the execution plan for a SQL statement, Oracle appears to always do a hard parse, ignoring what may already be in the library cache. I¡¯ve also heard that Oracle does not peek at the bind values in this case. So hopefully you can see how the execution plan generated by an EXPLAIN PLAN command could be quite different from the true execution plan being used by the database.

Ask DB Specialists: When EXPLAIN PLAN Gives the Wrong Plan