Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Explain plan accuracy and bind variables

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


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US