The Oracle (tm) Users' Co-Operative FAQ

Where is the UGA located, and how does sorting affect its use ?


Author's name: Jonathan Lewis

Author's Email: Jonathan@jlcomp.demon.co.uk

Date written: 7th Mayy 2001

Oracle version(s): 8.1.7.0

Where is the UGA located, and how does sorting affect its use ?

Back to index of questions


The UGA is the user global area, which holds session-based information.

If you think about Oracle's architecture of sessions and processes, particularly in multi-threaded server (or shared server as it has been renamed for Oracle 9), you will realise that session-based information has to be handled a little carefully.

When you are running dedicated servers (one session = one process) then the session information can be stored inside the process global area - i.e. in the memory space of the dedicated server.

However, when you are running with MTS/shared servers, a single session can migrate from one server (Snnn) process to another. Consequently the session-based information can not be stored in the process (Snnn) memory space, or it would be lost on migration. Consequently session-based information is stored in the SGA (shared global area).

In particular, when running MTS/shared servers and your session does some sorting, some of the memory allocated for sorting - specifically the amount defined by parameter sort_area_retained_size - comes from the SGA and the rest (up to sort_area_size) comes from the PGA (Snnn). This is because the sort_area_retained_size may have to be held open as the pipeline to return results to the front-end, so it has to be located where the session can find it again as the session migrate from server to server.m On the other hand, the sort_area_size is a complete throwaway, and by locating it in the PGA, Oracle can make best use of available memory without soaking the SGA.

To avoid sessions grabbing too much memory in the SGA when running MTS/shared server, you can set the private_sga value in the resource_limit for the user. This ensures that any particularly greedy SQL that (for example) demands multiple allocations of sort_area_retained_size will crash rather than flushing and exhausting the SGA.


Further reading: N/A


Back to top

Back to index of questions