Differences

This shows you the differences between two versions of the page.

--- oracle_memory_management [2018/12/08 12:49] – created 0.0.0.0
+++ oracle_memory_management [2020/10/22 10:13] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Oracle_Memory_Management ======
 Options are:
-  * Leave it completely up to the database
+  *  Leave it completely up to the database (AMM)
-then just set memory_max_size and memory_target
+then just set memory_max_size and memory_target (and sga_max_size and sga_target to 0)\\
-  * Set minimum values for some components
+This allows the database to manipulate both sga and pga as it feels necessary. Latest recommendation is to use this only if the total amount of memory available for the database is less than 4G.
-then set values for pga_aggregate_target and/or sga_target
+  *  Set maximum values for SGA and PGA (ASMM)
-  * Set maximum values for some components
+then just set sga_max_size and sga_target (and memory_max_size and memory_target to 0)
-then set values for things like sga_max_size
-====Handy views to look at====
-<code>0@@</code>
-<code>1@@</code>
+Set pga_aggregate_target and eventually ringfence it with pga_aggregate_limit.
+Setting any of the pool sizes means this value become a minimum.
+==== Queries for the sga ====
+<code>
+select * from v$sga;
+select sum(value) from v$sga;
+select sum(bytes) from v$sgastat;
+select sum(current_size) from v$sga_dynamic_components;
+select * from v$sga_dynamic_free_memory;
+select component, current_size from v$sga_dynamic_components;
+select pool, sum(bytes)/1024/1024 from v$sgastat group by pool;
+alter system flush buffer_cache;
+alter system flush shared_pool;
+select
+  component,
+  oper_type,
+  oper_mode,
+  initial_size/1024/1024 "Initial",
+  TARGET_SIZE/1024/1024 "Target",
+  FINAL_SIZE/1024/1024 "Final",
+  status
+from
+  v$sga_resize_ops order by component;
+select
+  component,
+  current_size/1024/1024 "CURRENT_SIZE",
+  min_size/1024/1024 "MIN_SIZE",
+  user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
+  last_oper_type "TYPE"
+from
+  v$sga_dynamic_components order by component;
+select * from v$sgainfo where name = 'Granule Size';
+</code>
+==== Show PGA Memory Allocation/Usage for Database Sessions ====
+<code>
+SET PAGESIZE 60
+SET LINESIZE 300
+COLUMN username FORMAT A20
+COLUMN module FORMAT A50
+COLUMN program FORMAT A50
+SELECT NVL(a.username,'(oracle)') AS username,
+       a.module,
+       a.program,
+       Trunc(b.value/1024) AS Memory_KB
+FROM   v$session a,
+       v$sesstat b,
+       v$statname c
+WHERE  a.sid = b.sid
+AND    b.statistic# = c.statistic#
+AND    c.name = 'session pga memory'
+AND    a.program IS NOT NULL
+ORDER BY b.value DESC
+/
+</code>
+=== Handy views to look at ===
+<code>
+V$SGAINFO
+V$SGASTAT
+V$SGA_DYNAMIC_COMPONENTS
+</code>
+<code>
+col name for a40
+select * from v$sgainfo
+</code>
 produces something like this...
-<code>2@@</code>
+<code>
+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
+rows selected.
+</code>
 or
-<code>3@@</code>
+<code>
+select name, (bytes/1024/1024) megs,resizeable from v$sgainfo;
-====How memory is used (assuming conventional path reads - not direct path and dedicated server)====
+NAME                                      MEGS RESIZEABLE
-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 />
+Fixed SGA Size                       2.1346283 No
-The desired rows/data is taken from those blocks in the SGA and pulled over to the PGA for sorting and/or grouping.<br />
+Redo Buffers                         381.84375 No
-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 />
+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
-====References====
+rows selected.
+</code>
+=== 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 ===
 [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1137385000346840516|asktom]]