Difference between revisions of "Oracle Memory Management"
From dbawiki
(Created page with "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_aggregat...") |
|||
| Line 12: | Line 12: | ||
V$SGA_DYNAMIC_COMPONENTS | V$SGA_DYNAMIC_COMPONENTS | ||
</pre> | </pre> | ||
| + | |||
| + | ====How memory is used (assuming conventional path reads (not direct path), dedicated server==== | ||
| + | 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 /> | ||
| + | The desired rows/data is taken from those blocks in the SGA and pulled over to the PGA for sorting and/or grouping.<br /> | ||
| + | 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.<br /> | ||
| + | |||
| + | ====References==== | ||
| + | [http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516 asktom] | ||
Revision as of 17:17, 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), 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.
