Difference between revisions of "Oracle Memory Management"
From dbawiki
(→How memory is used (assuming conventional path reads (not direct path), dedicated server) |
|||
| Line 13: | Line 13: | ||
</pre> | </pre> | ||
| − | ====How memory is used (assuming conventional path reads | + | ====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<br /> | A user runs a query which either finds the blocks it needs already in the SGA or fetches the blocks<br /> | ||
needed from disk into the SGA.<br /> | needed from disk into the SGA.<br /> | ||
Revision as of 17:38, 29 March 2013
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.
