Nevermind; I've done it. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap Sent: Friday, February 06, 2004 9:02 AM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Re: Explain plan accuracy and bind variables Can someone provide an example of a query that emits STAT lines that contradict the output of EXPLAIN PLAN because of the use of bind variables? Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com * Nullius in verba * Upcoming events: - Performance Diagnosis 101: 2/24 San Diego, 3/23 Park City, 4/6 Seattle - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ryan.gaffuri@xxxxxxx Sent: Friday, February 06, 2004 7:37 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Re: Explain plan accuracy and bind variables what about autotrace? that occurs after the query is run? = > = > From: Connor McDonald <hamcdc@xxxxxxxxxxx> > Date: 2004/02/06 Fri AM 08:18:49 EST > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: Explain plan accuracy and bind variables > = > I think this comes the various methods people use to take an existing S= QL and attempt to determine > an explain plan. > = > eg you might see in a trace file a slow query > = > select * from table where col =3D :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 th= us the index on 'col' was > not used. The execution plan in a trace file *will* be the one that wa= s used. This is quite > different to the plan you would see if you ran 'tkprod explain=3D..' > = > etc etc etc > = > hth > connor > = > --- ryan.gaffuri@xxxxxxx 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 thi= nk 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@xxxxxxxxxxxxx > > put 'unsubscribe' in the subject line. > > -- > > Archives are at //www.freelists.org/archives/oracle-l/ > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > > ----------------------------------------------------------------- = > = > =3D=3D=3D=3D=3D > 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@xxxxxxxxx > = > "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 =A3= 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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > = ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------