Difference between revisions of "Oracle Memory Management"
From dbawiki
(→How memory is used (assuming conventional path reads (not direct path), dedicated server) |
(→How memory is used (assuming conventional path reads - not direct path and dedicated server)) |
||
| Line 13: | Line 13: | ||
</pre> | </pre> | ||
| + | <pre> | ||
| + | col name for a40 | ||
| + | select * from v$sgainfo | ||
| + | </pre> | ||
| + | <pre> | ||
| + | NAME BYTES RESIZEABLE | ||
| + | ---------------------------------------- ---------- ------------ | ||
| + | Fixed SGA Size 2163592 No | ||
| + | Redo Buffers 17948672 No | ||
| + | Buffer Cache Size 1493172224 Yes | ||
| + | Shared Pool Size 1644167168 Yes | ||
| + | Large Pool Size 16777216 Yes | ||
| + | Java Pool Size 16777216 Yes | ||
| + | Streams Pool Size 16777216 Yes | ||
| + | Shared IO Pool Size 0 Yes | ||
| + | Granule Size 16777216 No | ||
| + | Maximum SGA Size 3207790592 No | ||
| + | Startup overhead in Shared Pool 365946152 No | ||
| + | Free SGA Memory Available 0 | ||
| + | |||
| + | 12 rows selected. | ||
| + | </pre> | ||
====How memory is used (assuming conventional path reads - not direct path and dedicated server)==== | ====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 /> | ||
Revision as of 09:39, 2 May 2014
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
col name for a40 select * from v$sgainfo
NAME BYTES RESIZEABLE ---------------------------------------- ---------- ------------ Fixed SGA Size 2163592 No Redo Buffers 17948672 No Buffer Cache Size 1493172224 Yes Shared Pool Size 1644167168 Yes Large Pool Size 16777216 Yes Java Pool Size 16777216 Yes Streams Pool Size 16777216 Yes Shared IO Pool Size 0 Yes Granule Size 16777216 No Maximum SGA Size 3207790592 No Startup overhead in Shared Pool 365946152 No Free SGA Memory Available 0 12 rows selected.
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.
