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

From: Dirschel, Steve <Steve.Dirschel_at_bestbuy.com>
Date: Thu, 10 Mar 2005 13:05:13 -0600
Message-ID: <BC335D10F1C0884CB3C3E8AF30D448A8EFB4AB@rs61mail.na.bestbuy.com>

I don't know I'd recommend they go to SIMILAR from FORCE if they're using a lot of histograms. SIMILAR causes Oracle to reparse sql statements if they're considered "unsafe" (i.e. <, >, between, those types of conditions in the where clause).

After much pain we found Oracle has a feature where if SIMILAR is used and histograms exist on WHERE clause columns Oracle will consider that statement=20
"unsafe" and reparse (version_count increases for the statement). Even if there is a histogram on a PK column and the WHERE clause has WHERE pk_column =3D value Oracle still reparses it...(every time it executes, not every time the plan changes). If they're running FORCE now and they change to SIMILAR I'd expect them to all of a sudden experience (assuming a relatively active system) latching related to the parsing and possible shared pool issues (4031). It took us 3+ months of working with Oracle to determine SIMILAR + HISTOGRAMS =3D REPARSING. But once = it
was determined that was the case Oracle came back saying the behavior was expected

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Wolfgang Breitling Sent: Thursday, March 10, 2005 12:28 PM
To: john.kanagaraj_at_hds.com
Cc: oracle-l_at_freelists.org
Subject: Re: Mutating (SQL) execution plan!?...is that possible...stranges t thing I have every seen

John Kanagaraj wrote:
> Chris,=20

>=20
>=20
>>>>I have the feeling you are setting yourself up for the=3D3D20 =

bind=3D20=20

>>>>variable peeking trap and appear to be falling into it.
>=20
>=20

> I am NOT a fan of using hidden parameters, but just be aware of the
hidden
> parameter '_optim_peek_user_binds' (which is set to TRUE by default
and
> enables bind peeking), that when set to FALSE in this case would take
care
> of your "problem". Histograms *are* a useful tool when used
appropriately,
> and can be a great help (and also a hindrance as you have seen).
>=20

While that is correct, you would already be at at least two layers of=20 band-aids. First using cursor_sharing=3Dforce to patch up the = application=20
not using bind variables correctly ( being written in perl there is=20 really no excuse for that ) and then use an underscore parameter to=20 patch up the bind variable peeking trap because of the histogram created

  by the broad "for all indexed columns ...". There are too many broad=20 assumptions at work here.
I hate to point it out because it could delay or avoid fixing the real=20 problem, but another possible workaround would be to use=20 cursor_sharing=3Dsimilar - and that is at least a documented parameter.

--=20
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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


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

Received on Thu Mar 10 2005 - 14:09:48 CST