Oracle Memory Management
From dbawiki
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[edit]
V$SGAINFO V$SGASTAT V$SGA_DYNAMIC_COMPONENTS
col name for a40 select * from v$sgainfo
produces something like this...
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.
or
select name, (bytes/1024/1024) megs,resizeable from v$sgainfo; NAME MEGS RESIZEABLE ----------------------------------- ---------- ------------ Fixed SGA Size 2.1346283 No Redo Buffers 381.84375 No Buffer Cache Size 4608 Yes Shared Pool Size 18432 Yes Large Pool Size 512 Yes Java Pool Size 512 Yes Streams Pool Size 1024 Yes Shared IO Pool Size 0 Yes Granule Size 512 No Maximum SGA Size 764799.984 No Startup overhead in Shared Pool 4617.96307 No Free SGA Memory Available 739328 12 rows selected.
How memory is used (assuming conventional path reads - not direct path and dedicated server)[edit]
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.
