RE: 10g slowdown
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <WLJohnson@xxxxxxxxxxxxxxxxxxx>, <William.Blanchard@xxxxxxxxxx>, <mfontana@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Dec 2008 10:34:51 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Johnson, William L (TEIS)
Sent: Friday, December 12, 2008 2:41 PM
To: William.Blanchard@xxxxxxxxxx; mfontana@xxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
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>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
