RE: Explain plan accuracy and bind variables

From: Pete Sharman <peter.sharman_at_oracle.com>
Date: Sat, 7 Feb 2004 04:42:45 +1100
Message-ID: <007101c3ecd8$a4b43370$6f4f8790@PETE>

Well, having seen Jared's response, one thing I would REALLY recommend is to trim down the reply trail as much as possible. Seems the list server attaches the files as inline text at the END of the email thread, and this one is getting really long. If you don't realize the list server does that, you might wonder where the attachments supposedly are.

Pete

"Controlling developers is like herding cats."

Kevin Loney, Oracle DBA Handbook

"Oh no, it's not. It's much harder than that!"

Bruce Pihlamae, long-term Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap Sent: Saturday, February 07, 2004 3:22 AM To: oracle-l_at_freelists.org
Subject: RE: Explain plan accuracy and bind variables

Okay. Jared, can I send .txt and .sql attachments to the list? Size of each is <10KB.

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Daniel Fink Sent: Friday, February 06, 2004 9:37 AM
To: oracle-l_at_freelists.org
Subject: Re: Explain plan accuracy and bind variables

Could we see it?

Cary Millsap wrote:

> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
> Sent: Friday, February 06, 2004 9:02 AM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
ryan.gaffuri_at_cox.net
> Sent: Friday, February 06, 2004 7:37 AM
> To: oracle-l_at_freelists.org
> Subject: Re: Re: Explain plan accuracy and bind variables
>
> what about autotrace? that occurs after the query is run? =
>
> > =
>
> > From: Connor McDonald <hamcdc_at_yahoo.co.uk>
> > Date: 2004/02/06 Fri AM 08:18:49 EST
> > To: oracle-l_at_freelists.org
> > 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_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
> 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_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
> > > -----------------------------------------------------------------
=
>
> > =
>
> > =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_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
> =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_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
> > -----------------------------------------------------------------
> > =
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------

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

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

----------------------------------------------------------------
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 - 11:42:45 CST