Re: Parameter affecting Memory Usage for sorts

It's not actually per connection. It's per concurrent sort. A user starts off with zero sort_area_size, whatever the parameter says. Only when he starts a sort does he start to acquire chunks of memory for that sort, up to the limit of the sort_area_size. When the sort has finished, his sort_area_size returns to zero.

So, the only worry is that heaps of concurrent sorts could chew through memory at a rate of knots.

But if you've 1000 users doing nothing but inserts, then you could very comfortably set sort-area_size to, say 10M, and you would not be using any sort_area_size at all.

As to whether fiddling with the sort_area_size is the best thing to be doing with a load of extra memory -well, I'd rather spend that extra RAM on things like the shared_Pool_size and the db_block_buffers.... and whether any or all of these things is worth doing depends entirely on what you hit ratios are like.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
news:FAMP7.2908$ER5.245866_at_rwcrnsc52...


> In the init.ora file called sort_area_size  The larger it is the less it

> will sort to disk.  It is in bytes and it is PER connection.  So if you

> increase it by 2,000,000 and you have 100 connections then you could use

up


> to 200 meg more ram.  Also look at the drive the temporary tablespace is

on;


> make sure you minimize contention there.

> Jim

> "Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote

in


> message news:3c0f9188.1473297750_at_news.alt.net...

> > Oracle 8.1.6

> >

> > We just added some memory to out server, and have not really noticed

> > Oracle making use of that memory. I assume there are a number of

> > parameters to be tweaked, and I'll need to do some reading.

> >

> > In the meanwhile, we'd specifically like to speed up sorts. Is there

> > one parameter that tweaks its usage, or is it more than than one?

> > Would someone be kind enough to point out their names (so I can read

> > what they are in the documentation on initialization parameters)  or

> > with a pointer to something in the documentation that deals

> > specifically with physical memory usage?

> >

> > Brian

>

>