Re: [beginner] application with large sorts

Francesco,

If you were to use Oracle 9i with dedicated connections, you wouldn't have to worry about setting the sort_area_size. Instead you'll need to look at setting pga_aggregate_target. I'd recommend using 9i because it is much better at handling the memory allocation for sort processes.

However, if you are planning to use the Shared Server option, then you will still have to mess with sort_area_size/hash_area_size.

In either case, when memory runs out, I believe the sort segments spill over to your TEMP tablespace. So, you'll need to ensure that you have enough space in TEMP to handle whatever spills over.

A word of advice...

Try to architect your system so that it never uses TEMP for sorting. Sorting on disk is *terribly* slow.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

"Francesco" <FrancescoNOSPAM_at_libero.it> wrote in message news:<G5Y2c.55364$Kc3.1740749_at_twister2.libero.it>...
> Hi all !
> I'm going to write an application that make uses of lots of sorts
> in its queries. I don't have a deep knowdledge of Oracle Administration
> but I'm aware that an init parameter (SORT_AREA_SIZE if I remember well)
> states how much space to reserve for sorts. So I wonder, what if that space
> won't be enough : the segments reserved will "explode" like rollback
> segments do,
> or Oracle will enlarge it by itself ?
> One more question : How do I monitor if this space is going to be exausted ?
> Thanks a lot
> Francesco
Received on Mon Mar 08 2004 - 10:34:51 CST