The Oracle (tm) Users' Co-Operative FAQ

Where is the UGA, and how does sorting affect it ?


Author's name: Jonathan Lewis

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

Date written: 26th July 2001

Oracle version(s): 7.3 - 8.1.7.0

It is possible to define a number so that it is stored in the database with 38 digits of precision. If I do this, how much space does a number really use if it doesn't actually use this degree of precision. Strangely, the internal storage for number (dba_tab_columns) suggests that the worst case usage for a number is 22 bytes, so how much space does it really take to store a number ?.

Back to index of questions


 

Initially, though, I believe memory is allocated in the UGA for sorting, until the demand exceeds the sort_area_retained_size, at which point the excess (up to sort_area_size) is allocated from the PGA.

 

The retained_size has to be in the UGA, and the UGA is in the SGA for shared_servers, because data if FETCHED from the UGA, and a session may migrate from one shared server to another between fetches. The rest of sort_area_size can be in the PGA, because that is local to a shared_server, and discardable as soon as the actual sort phase is complete (but this is why a large sort process locks a session to a shared server, hence why heavy duty reports should generally not be executed through shared servers).

.


Further reading: N/A


Back to top

Back to index of questions