RE: Need advice on tuning slippery queries
- From: "Schultz, Charles" <sac@xxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 7 Jun 2006 14:55:36 -0500
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14211/opti
mops.htm#sthref1254
Ahh, I see how Bind variable Peeking becomes a bit of a problem. Thanks
Wolfgang, for the heads up.
Now, how do I fix this? =) Sometimes peeking is good, some times not.
> _____________________________________________
> From: Schultz, Charles
> Sent: Wednesday, June 07, 2006 2:17 PM
> To: oracle-l
> Subject: Need advice on tuning slippery queries
>
> On several occasions, we have had a "bad" plan generated by the CBO (I
> know this is old hat for you veterans in the field *grin*). A few in
> particular will demonstrate these symptoms:
> * At some point in time (time A), the query is freshly parsed
> (hard parsed) and a "less-than-optimal" plan is generated
> * At some other point B (anytime > A), all other queries are soft
> parsed (libcache hit) with the same grisly plan
> * Further later on after B, the users finally get fed up and call
> in the problem. DBAs get the call but cannot reproduce the problem in
> Test. Altering a test query in Production with a dummy comment (to
> change the signature), the query parses freshly (libcache miss) with a
> "good" plan.
> * Lastly, the shared pool is flushed and the original query parses
> once again, but this time with a "good" plan
>
> I have at least two different SRs open on cases like this, and Oracle
> Support wants me to send them a test case, or generate 10053 and 10046
> traces on a "bad" query. How do I do that? Ideally, in a perfect
> world, I would have baseline profiles on all query statements and know
> when a query is going against a new (whether "bad" or "good") plan.
> Alas, we are stuck in an imperfect world and woefully must wait for
> the blessed user to complain to us. (I am sure many of you will have
> helpful advice on how to stop depending on users in such an
> embarrassing way.) Ironically, after reading a little about the HotSOS
> Profiler and OraSRP, I could not find OraSRP on Egor's site.
>
> My one thought, which causes me to cringe, is to turn on a 10053 trace
> at the system level to make sure we catch the dastardly plans in
> action. But surely there is a better way. I am hoping that I am simply
> missing something really obvious which would shame me, but at least
> should be a simple solution. I thought about login triggers, but that
> requires that you know which login conditions to watch for. While we
> have "usual suspects", they are not consistent. We have tried setting
> up tracing after the fact, but we never catch that initial bugger. I
> looked at the various dba_hist_sql% views, but I was having a hard
> time getting hard information out of them.
>
> Where do I go from here?
>
> charles schultz
> oracle dba
> aits - adsd
> university of illinois
>
>
Other related posts:
