第一部分：http://space.itpub.net/16689238/viewspace-551803

Explain plan is blind to the bind.

Explain plan does not "bind peek". This is pretty easy to observe:

  2  as

  3  select a.*, 1 id

  4    from all_objects a

  5   where rownum <= 5000;

Table created.

ops$tkyte%ORA10GR2> update t

  2     set id = 99

  3   where rownum = 1;

1 row updated.

ops$tkyte%ORA10GR2> create index t_idx on t(id);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats

( user, 'T', method_opt=> 'for all indexed columns size 254' );

PL/SQL procedure successfully completed.

So we have created some skewed data.  If we say "where id=1", we would expect a full scan (index would be inefficient).  If we say "where id = 99", we would expect an index range scan - as id=99 returns a single row.  Using two queries that differ only in bind names (which is sufficient to prevent cursor sharing - these are two DIFFERENT queries to Oracle!), we'll execute a query with a bind set to the value 99 and then another with a bind set to 1.  

ops$tkyte%ORA10GR2> variable x_is_99_first number

ops$tkyte%ORA10GR2> variable x_is_1_first number

ops$tkyte%ORA10GR2> exec :x_is_99_first := 99; :x_is_1_first := 1;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA10GR2> select id, object_name from t where id = :x_is_99_first;

        ID OBJECT_NAME

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

        99 ICOL$

ops$tkyte%ORA10GR2> select id, object_name from t where id = :x_is_1_first;

        ID OBJECT_NAME

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

         1 I_USER1

....

         1 USER_SCHEDULER_PROGRAM_ARGS

4999 rows selected.

Now we'll just flip flop the values and re-execute the queries.  Note that they will soft parse, just reuse the existing plans generated from above.

ops$tkyte%ORA10GR2> exec :x_is_99_first := 1; :x_is_1_first := 99;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select id, object_name from t where id = :x_is_99_first;

        ID OBJECT_NAME

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

         1 I_USER1

...

         1 USER_SCHEDULER_PROGRAM_ARGS

4999 rows selected.

ops$tkyte%ORA10GR2> select id, object_name from t where id = :x_is_1_first;

        ID OBJECT_NAME

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

        99 ICOL$

ops$tkyte%ORA10GR2> alter session set sql_trace=false;

Session altered.

Reviewing the TKPROF report first for the "x is 99 first" query we see:

select id, object_name from t where id = :x_is_99_first

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0          3          0           1

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

total        4      0.00       0.00          0          3          0           1

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 120  (OPS$TKYTE)

Rows     Row Source Operation

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

      1  TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=59 us)

      1   INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=32 us)(object id 70394)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

select id, object_name from t where id = :x_is_99_first

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      335      0.02       0.02          0        739          0        4999

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

total      337      0.02       0.02          0        739          0        4999

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 120  (OPS$TKYTE)

Rows     Row Source Operation

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

   4999  TABLE ACCESS BY INDEX ROWID T (cr=739 pr=0 pw=0 time=50042 us)

   4999   INDEX RANGE SCAN T_IDX (cr=344 pr=0 pw=0 time=30018 us)(object id 70394)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

   4999   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

So, the "real plan" used is an index range scan - both times.  But, explain plan - which cannot, does not bind peek - will say "full scan".  The reason?  explain plan is optimizing "select * from t where id = ?" - and it says "5,000 rows, 2 values of id, id is not selective, full scan".  But the optimizer is optimizing the query "select * from t where id = 99" - because it peeked at the bind the first time!  The soft parse won't peek (else it would be a hard parse!) and just reused the existing plan - the inefficient range scan to read every row out.

On the other hand, looking at the "x is 1 first" query:

select id, object_name from t where id = :x_is_1_first

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      335      0.01       0.01          0        398          0        4999

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

total      337      0.01       0.01          0        398          0        4999

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 120  (OPS$TKYTE)

Rows     Row Source Operation

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

   4999  TABLE ACCESS FULL T (cr=398 pr=0 pw=0 time=15094 us)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

   4999   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

********************************************************************************

select id, object_name from t where id = :x_is_1_first

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.00       0.00          0         67          0           1

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

total        4      0.00       0.00          0         67          0           1

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 120  (OPS$TKYTE)

Rows     Row Source Operation

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

      1  TABLE ACCESS FULL T (cr=67 pr=0 pw=0 time=82 us)

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

Explain plan appears to have gotten it right - but only by accident.  It is just a coincidence that the plans "match" - they were arrived at by very different thought processes.  The optimizer optimized 'where id=1' and said "about 5,000 rows, about 4,999 will be returned, full scan".  The explain plan optimized "where id=?" and said "about 5,000 rows in the table, two values for ID, about 50% of the table will be returned, full scan".

So, that example shows explain plan "getting it wrong" because it is blind to the bind - and shows the effect of bind variable peeking (which you can read more about on asktom using the link above...)

第三部分：http://space.itpub.net/16689238/viewspace-551806