The Oracle (tm) Users' Co-Operative FAQ

How does Oracle handle PGA memory.


Author's name: Jonathan Lewis

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

Date written: 14th Sept 2005

Oracle version(s): 9.2.0.6

Sorts, hash joins, bitmap creation, and bitmap merges are memory intensive operations, and Oracle introduced a new strategy for handling large memory allocations in 9i. Is there a convenient summary of how the old and new mechanisms differ.

Back to index of questions


This question is addressed by the following documents:

Author

Title/URL

Suggested by

Referee's comments

Christo Kutrovsky

Working_with_Automatic_PGA

Jonathan Lewis

When trawling the internet recently to see what had been published about the pga_aggregate_target, I found this powerpoint presentation (660KB). It was probably one of the most useful documents I found.  Apart from anything else, it started with a good graphic on virtual memory mapping and where the memory goes. Well worth reading. The slides include details from the original paper (below) by Benoit Dageville and Mohamed Zait, and a couple of fascinating little points about how hash joins can play clever games with automatic workarea sizing.

Benoit Dageville and

Mohamed Zait

SQL Memory Management in Oracle 9i

Jonathan Lewis

The definitive guideline to what it’s all about. A pdf file of a paper published for the VLDB conference in 2002. 


Further reading: (probably the best thing here is a reference to the relevant bits of the Oracle manuals)


Back to top

Back to index of questions