RE: explain plan and the real execution plan

  • From: "Lei Zeng" <lzeng@xxxxxxxxxxxxx>
  • To: "Tanel Poder" <tanel@xxxxxxxxxx>
  • Date: Mon, 16 Nov 2009 21:41:34 -0800



Would you please help me understand your points better?


1)       The optimizer statistics

I assume this database has no change - no ddl and dml. Under this
situation, I would say the optimizer statistics would remain the same,


2)       The implicit data type conversion

Would you provide a testing case on this? I tried some queries but I
could not produce the effects.


Thanks very much,


From: Tanel Poder [mailto:tanel@xxxxxxxxxx] 
Sent: Monday, November 16, 2009 5:08 PM
To: Lei Zeng
Subject: Re: explain plan and the real execution plan


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...


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


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.



Tanel Poder

Tanel Poder

Other related posts: