Comments below.
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================
"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
news:3c9dc2d8.1150096_at_news-vip.optusnet.com.au...
> Howard J. Rogers doodled thusly:
>
> >So, to answer the original poster, no... stick to the exact setting of
> >sort_area_size (or an integer multiple thereof), and don't pad it with
extra
> >bytes at all. You will pay a performance penalty if you do.
> >
>
> G'day, Howard:
>
> I've been meaning to ask these ones, you might know the answers?
>
>
> With the per session sort area size available now, isn't there a
> danger that some over-zealous developer will bring the temp tablespace
> calcs out the window with a changing sort area size?
>
> I'm in two minds about this one. Should we set a large sort area size
> (say, 5M) to reduce I/O to temporary and set the uniform allocation to
> same size? Or set uniform to a sub-multiple? Or set sort area size
> smaller (say 500K) and set uniform to it exactly and wear a bit of I/O
> in exchange for better management of temporary?
>
> See what I mean? Sure, the recommendations from Oracle make sense.
> What doesn't make sense is how people use the databases... ;-)
>
This is certainly an issue. There need to be some development rules: only
use multiples of 1M, say, for sort_area_size And the maximum it can ever be
should be (say) 16M. At which point I would create the temporary tablespace
with uniform size of 16M. Small sorts would fit in there 16 times. The
largest sorts would fit in there once. But yes, get a developer coding for
a sort_area_size of 9.5M and the nice setup is instantly stuffed, agreed.
But in general, I'd suggest an agreed maximum sort_area_size, and set
uniform to that.
Of course, it gets even murkier in 9i. You set a PGA_AGGREGATE_TARGET and
leave it up to Oracle. God knows who gets what in that scenario, and I've
yet to work out any real recommendations for the temporary tablespace as a
result!
>
> Come to think of it: with the per session sort area size, what's to
> stop a third party app from going waco on this and having each session
> with its own size? Can we see the currently used sort area size
> anywhere?
>
I'm not actually convinced that it's a bad thing to have each session with a
different s_a_s setting. Nor even changing it on the fly as a user moves
from one part of the app to another. Seems to me that this is actually
making good use of the feature. But yes, it depends on developers being
moderately sensible in the set of sizes they make available to users, and if
it's third party, I agree that it's then in the lap of the gods as to
whether they do things sensibly.
As to whether you can see what s_a_s is set to in a given session.... that's
an excellent question! (Hint: that's coded language for 'I've never thought
of that before, and I don't have an answer'). I'll have to get back to you
on that one.
Regards
HJR
> Sorry for the "bombardment" but I've been musing about these and can't
> find them discussed anywhere. Do you have anything on this?
>
>
>
> Cheers
> Nuno Souto
> nsouto_at_optushome.com.au.nospam
Received on Sun Mar 24 2002 - 07:22:40 CST
