RE: 10g slowdown

The link below is the perfect explanation of why all y'all should vote yes whenever presented with a chance to vote for (under various names and aliases ever since 7.0 - it was the only behavior possible for 6.x and earlier) "private parse" aka "non-shared parse", "reparse", that has always be deferred because it went against the tide of maximum simultaneous user scalability.

Imagine rejecting the use of the shared pool! That simply will not scale as well as using the shared pool in the general case if even a small percentage of queries are reused. But when parse storms make searching for the existing sql text a latch nightmare, when skewed data with binds (as the article points out in the clearest [as usual for TK] explanation and documentation I've ever seen), or various and sundry other special cases, directing the use of a private sql area would in fact be a panacea. Even if they only implemented it in shared memory with some kind of canard like sql_trace=psuedo to mark the cursor fresh and unshared this would solve all the cases I can think of except the parse storm (and of course since you know you're not going to reuse the shared text sql_trace in general could skip the search anyway to solve that problem).

I'm against the routine use of hints and the like unless you have to to get around a problem - in the long haul that puts future improvements to the CBO in a strait jacket - but the quantitative knowledge of predictive skewness of bind variable choices is a time machine question unlikely to be solved any time soon now, so make up your own version of this enhancement request, file it, and vote for it in all the forums you see.

Or explain to me a better way to solve the problem.

Thanks in advance, and regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Johnson, William L (TEIS)
Sent: Friday, December 12, 2008 2:41 PM
To: William.Blanchard_at_kohler.com; mfontana_at_enkitec.com; oracle-l_at_freelists.org
Subject: RE: 10g slowdown

Check out this article - it has some good information to help explain what you are seeing...

www.oracle.com/technology/oramag/oracle/08-jan/o18asktom.html

<SNIP>

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