RE: Mutating (SQL) execution plan!?...is that possible...strangest thing I have every seen

From: Marquez, Chris <CMarquez_at_aarp.org>
Date: Thu, 10 Mar 2005 10:40:52 -0500
Message-ID: <7E412C164E6ECB468834A39F31E6E0D4074B5E1D@mbs06dc.na.aarp.int>

Thank you Wolfgang...I am forever grateful.

I'm going to rebuild the stats without the use of histograms / "skewonly" options.

This goes to my core belief that Oracle has many options and we don't need to use them all.
Unfortunately developers see an article about a new index, or a new feature, and they think all of there problems will be solved. I may be slow paced DBA, but I never (want) use an Oracle option, feature or parameter without some expectation of what does...good *and* bad.
Nothing if free...

Thanks again,

Chris

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com]=20 Sent: Thursday, March 10, 2005 10:02 AM
To: Marquez, Chris
Cc: oracle-l_at_freelists.org
Subject: Re: Mutating (SQL) execution plan!?...is that possible...strangest thing I have every seen

Marquez, Chris wrote:

>>>Do you per chance gather histograms ( "for=3D20
>>>all indexed columns size skewonly" or somethink like it?).

>=20

> Yes, Yes, Yes, we do!
> At the recommendation of Oracle *guru*? (web site article).

>=20
>=20

>>>(you also seem to run with cursor_sharing=3D3Dforce)=3D20

>=20

> Yes again.

Fix the application (obviously you can change the sql) to use bind=20 variables where appropriate and dump that cursor_sharing=3Dforce crutch.

>=20
>=20

>>>I have the feeling you are setting yourself up for the=3D20 bind=20
>>>variable peeking trap and appear to be falling into it.

>=20

> What the heck is "bind variable peeking trap"?

The Oracle 9 (and higher) optimizer uses the bind value of the variable=20
when it parses the plan as if the sql contained the value rather than a=20
bind variable. Bind variables (even faked ones through c_s=3Dforce) let=20

the optimizer reuse the sql without parsing (that's the idea of=20 binding). Therefore, the first bind value - the one that causes the=20 parse - determines the access path for all other executions. Now if you=20 have a skewed data distribution and a histogram and that first bind=20 value is such that a full scan is appropriate all the other executions=20 will use a full scan as well - until the sql is parsed again. Maybe=20 because it aged out, maybe because someone changed "AND" to "and", maybe

because one of the tables or indexes got somehow modified or analyzed,=20 maybe someone issued an "alter system flush shared_pool". Then things start over again.
Read up on bind variable peeking in the concepts or performance guide.
>=20

> Thanks...any suggestions?

1. Stop collecting histograms on "all indexed columns". Not all indexed=20 columns should have a histogram and some non-indexed columns DO need=20 one. Collect histograms specifically and only for those columns that=20 need it.
2. Don't listen to *guru*s

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 10 2005 - 10:44:10 CST