Re: explain plan and the real execution plan
- From: Tanel Poder <tanel@xxxxxxxxxx>
- To: lzeng@xxxxxxxxxxxxx
- Date: Tue, 17 Nov 2009 09:08:13 +0800
Little correction to point number 3 - of course explain plan doesn't really
execute the plan so the implicit datatype conversion you see is in the
explained plan only, but if you actually execute the statement (with correct
bind datatypes) then there's no implicit datatype conversion. And that's
where the difference comes from...
Tanel
On Tue, Nov 17, 2009 at 8:54 AM, Tanel Poder <tanel@xxxxxxxxxx> wrote:
> 3 main reasons:
>
> 1) The optimizer statistics the EXPLAIN PLAN ends up using are different
> from the statistics the other session ended up using
>
> 2) Explain plan does not use bind variable peeking thus will not optimize
> for current bind variable values
>
> 3) Explain plan treats all bind variables as VARCHAR2, thus you ma have
> implicit datatype conversion happening during the plan execution, (meaning
> to_char,to_number functions are added around variables/columns) and this for
> example may make optimizer to ignore some indexes if you get unlucky.
>
>
> Tanel Poder
> http://blog.tanelpoder.com
>
>
> On Tue, Nov 17, 2009 at 7:25 AM, Lei Zeng <lzeng@xxxxxxxxxxxxx> wrote:
>
>> Hi, all:
>>
>> I have a question about the explain plan.
>>
>>
>>
>> Under what conditions, could the plan I get out of ‘explain plan’ differ
>> from what I get from running the sql statement?
>>
>>
>>
>> Suppose the database has no change during I ran ‘explain plan’ and run the
>> sql – I mean, no ddl and dml – You would think this database is static. Or
>> the time gap between I run ‘explain plan’ and run sql statement is too
>> narrow to allow any change (ideal environment). No bind variable peeking
>> because _optim_peek_user_binds is set to FALSE.
>>
>>
>>
>> Any idea? Thanks in advance.
>>
>>
>> Lei
>>
>>
>>
>
>
>
> --
> Tanel Poder
> http://blog.tanelpoder.com
>
>
--
Tanel Poder
http://blog.tanelpoder.com
Other related posts:
