Re: Fw: RE: explain plan and the real execution plan
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Tue, 17 Nov 2009 09:22:08 -0800
>> 2) The implicit data type conversion
>>Would you provide a testing case on this?
There are a number of examples on the web. I did a blog post about it here:
http://db-optimizer.blogspot.com/2009/09/explain-plans.html
Example of wrong plan because of bind variables:
http://www.jlcomp.demon.co.uk/wrong.html
Another blog post on Explain Plan giving wrong plan
http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/
The real plan:
select id from t1 where n1 > :m_n1;
select * from table (dbms_xplan.display_cursor());
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)|
|
|* 1 | TABLE ACCESS FULL | T1 | 1000 | 7000 | 5 (0)|
00:00:01 |
-------------------------------------------------------------------------------
the Explain Plan guess (autotrace uses Explain Plan)
set autotrace on
select id from t1 where n1 > :m_n1;
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 350 | 1
(0)|00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 50 | 350 | 1
(0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_I1| 21 | | 1
(0)|00:00:01 |
---------------------------------------------------------------------------------
Best
Kyle Hailey
http://oraclemonitor.com
http://db-optimizer.blogspot.com/
Other related posts:
