Oracle Memory Management

Options are:

• Leave it completely up to the database

then just set memory_max_size and memory_target

• Set minimum values for some components

then set values for pga_aggregate_target and/or sga_target

• Set maximum values for some components

then set values for things like sga_max_size

Handy views to look at

V$SGAINFO
V$SGASTAT
V$SGA_DYNAMIC_COMPONENTS

How memory is used (assuming conventional path reads - not direct path and dedicated server)

A user runs a query which either finds the blocks it needs already in the SGA or fetches the blocks
needed from disk into the SGA.
The desired rows/data is taken from those blocks in the SGA and pulled over to the PGA for sorting and/or grouping.
If more than a "reasonable amount" of memory is required then the sorting and/or grouping spills over to TEMP space on disk and ultimately returns the rows to the user.

References

asktom