Re: Sort area size

First thought -- sounds like you are tuning by ratios ... you have 99% sorts in memory already but you want a higher number.

Is anyone complaining about slow response? Are your applications running satisfactorily? Who is getting impacted and by what extent?

Those are the questions that you want to address.

If you decide that you still want to address the situation, the next recommendation is to understand what kind of queries are using big amounts of sort space. If you possibly can, fix the application before changing oracle parameters.

If you are running in an OLTP environment, "most of the time" aka "application dependent" people should NOT be running stuff that requires so much sort space. If it's mixed OLTP and OLAP ... well then you may not have a choice.

What I recommend is instead of worrying about less than 1% of the sorts, identifying things if any that are "runaways" and (like cartesian products) will not do anything useful anyway. It's not hard to periodically run something that identifies anyone that has more than
(pick your number=it depends on your site and application) 2 m, 100 m,
500 m, 1 gig etc of sort space allocated.

Then you identify the sql that is running, find somebody to fix it, and
(maybe) kill the process using that much.
Received on Sat Jan 08 2005 - 12:29:30 CST