Re: Explain plan accuracy and bind variables

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 6 Feb 2004 13:18:49 +0000 (GMT)
Message-ID: <20040206131849.12891.qmail@web25204.mail.ukl.yahoo.com>

I think this comes the various methods people use to take an existing SQL and attempt to determine an explain plan.

eg you might see in a trace file a slow query

select * from table where col = :b1

You run this through explain plan and voila! Looks great - no problem. But what really happened on the live system was that 'col' was char, and :b1 was numeric, and thus the index on 'col' was not used. The execution plan in a trace file *will* be the one that was used. This is quite different to the plan you would see if you ran 'tkprod explain=..'

etc etc etc

hth
connor

ryan.gaffuri_at_cox.net wrote: > I read somewhere that explain plans are not always accurate. Does this include autotrace? What
> about if I do a 10046 or just a sql_trace?
>
> Are explain plans more apt to be inaccurate with bind variables(I think I read that somewhere).
> If so, why?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------

Connor McDonald

Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net

email: connor_mcdonald_at_yahoo.com

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 06 2004 - 07:18:49 CST