V$MEMORY_DYNAMIC_COMPONENTS
V$MEMORY_CURRENT_RESIZE_OPS
V$MEMORY_RESIZE_OPS
V$MEMORY_TARGET_ADVICE
V$MEMORY_DYNAMIC_COMPONENTS
col COMPONENT format a25
set line 300
set pagesize 50
SQL> SELECT component, current_size, min_size, max_size
FROM v$memory_dynamic_components
WHERE current_size != 0; 2 3
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE
------------------------- ------------ ---------- ----------
shared pool 150994944 67108864 150994944
large pool 8388608 8388608 125829120
java pool 4194304 4194304 4194304
SGA Target 218103808 218103808 339738624
DEFAULT buffer cache 41943040 41943040 163577856
Shared IO Pool 4194304 0 4194304
PGA Target 146800640 29360128 146800640
7 rows selected.
V$MEMORY_TARGET_ADVICE
SELECT * FROM v$memory_target_advice ORDER BY memory_size;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION CON_ID
----------- ------------------ ------------ ------------------- ---------- ----------
88 .25 32 1 8 0
176 .5 32 1 8 0
264 .75 32 1 8 0
352 1 32 1 8 0
440 1.25 32 1 8 0
528 1.5 32 1 8 0
616 1.75 32 1 8 0
704 2 32 1 8 0
8 rows selected.
V$MEMORY_CURRENT_RESIZE_OPS
note:
Too many resize operations with AMM can hurt your performance.
this happens when there is not enough RAM.
v$...._ops show the last 800 resizes
set lines 100
col end_time format a16 head "Time Stamp"
col init format 999,999,999 head "Initial (MB)"
col target format 999,999,999 head "Target (MB)"
col final format 999,999,999 head "Final (MB)"
col component format a25 head "Component"
col status head "Status"
select to_char(end_time,'dd-Mon hh24:mi') end_time, component,
(initial_size/1024/1024) init, (target_size/1024/1024) target,
(final_size/1024/1024) final, status
from v$memory_resize_ops order by end_time;
col mns format 999,999,999 head "Min Size"
col mxs format 999,999,999 head "Max Size"
col granule format 999,999,999 head "Granule Size"
select component, (min_size/1024/1024) mns,
(max_size/1024/1024) mxs, (granule_size/1024/1024) granule
from v$memory_dynamic_components;
Time Stamp Component Initial (MB) Target (MB) Final (MB) Status
---------------- ------------------------- ------------ ------------ ------------ ---------
03-Oct 13:26 shared pool 0 120 120 COMPLETE
03-Oct 13:26 shared pool 120 124 124 COMPLETE
03-Oct 13:26 PGA Target 0 28 28 COMPLETE
03-Oct 13:26 In-Memory Area 0 0 0 COMPLETE
03-Oct 13:26 RECYCLE buffer cache 0 0 0 COMPLETE
03-Oct 13:26 KEEP buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT 32K buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT 16K buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT 8K buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT 4K buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT 2K buffer cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 52 48 48 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 56 52 52 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 60 56 56 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 64 60 60 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 68 64 64 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 72 68 68 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 72 72 72 COMPLETE
03-Oct 13:26 ASM Buffer Cache 0 0 0 COMPLETE
03-Oct 13:26 DEFAULT buffer cache 0 72 72 COMPLETE
03-Oct 13:26 SGA Target 0 324 324 COMPLETE
03-Oct 13:26 streams pool 0 0 0 COMPLETE
03-Oct 13:26 java pool 0 4 4 COMPLETE
03-Oct 13:26 large pool 0 120 120 COMPLETE
03-Oct 13:26 shared pool 136 140 140 COMPLETE
03-Oct 13:26 shared pool 132 136 136 COMPLETE
03-Oct 13:26 shared pool 124 128 128 COMPLETE
03-Oct 13:26 shared pool 128 132 132 COMPLETE
03-Oct 13:27 DEFAULT buffer cache 48 44 44 COMPLETE
03-Oct 13:27 shared pool 140 144 144 COMPLETE
03-Oct 13:28 PGA Target 28 32 32 COMPLETE
03-Oct 13:28 DEFAULT buffer cache 44 156 156 COMPLETE
03-Oct 13:28 DEFAULT buffer cache 156 152 152 COMPLETE
03-Oct 13:28 large pool 120 8 8 COMPLETE
03-Oct 13:28 SGA Target 324 320 320 COMPLETE
03-Oct 13:30 SGA Target 320 316 316 COMPLETE
03-Oct 13:30 PGA Target 32 36 36 COMPLETE
03-Oct 13:30 DEFAULT buffer cache 152 148 148 COMPLETE
03-Oct 13:32 SGA Target 316 312 312 COMPLETE
03-Oct 13:32 DEFAULT buffer cache 148 144 144 COMPLETE
03-Oct 13:32 PGA Target 36 40 40 COMPLETE
03-Oct 13:34 PGA Target 40 44 44 COMPLETE
03-Oct 13:34 SGA Target 312 308 308 COMPLETE
03-Oct 13:34 DEFAULT buffer cache 144 140 140 COMPLETE
03-Oct 13:36 SGA Target 308 304 304 COMPLETE
03-Oct 13:36 PGA Target 44 48 48 COMPLETE
03-Oct 13:36 DEFAULT buffer cache 140 136 136 COMPLETE
03-Oct 13:38 DEFAULT buffer cache 136 132 132 COMPLETE
03-Oct 13:38 SGA Target 304 300 300 COMPLETE
03-Oct 13:38 PGA Target 48 52 52 COMPLETE
03-Oct 13:40 PGA Target 52 56 56 COMPLETE
03-Oct 13:40 SGA Target 300 296 296 COMPLETE
03-Oct 13:40 DEFAULT buffer cache 132 128 128 COMPLETE
03-Oct 13:42 SGA Target 296 292 292 COMPLETE
03-Oct 13:42 DEFAULT buffer cache 128 124 124 COMPLETE
03-Oct 13:42 PGA Target 56 60 60 COMPLETE
03-Oct 13:44 SGA Target 292 288 288 COMPLETE
03-Oct 13:44 DEFAULT buffer cache 124 120 120 COMPLETE
03-Oct 13:44 PGA Target 60 64 64 COMPLETE
03-Oct 13:46 PGA Target 64 68 68 COMPLETE
03-Oct 13:46 DEFAULT buffer cache 120 116 116 COMPLETE
03-Oct 13:46 SGA Target 288 284 284 COMPLETE
03-Oct 13:48 SGA Target 284 280 280 COMPLETE
03-Oct 13:48 DEFAULT buffer cache 116 112 112 COMPLETE
03-Oct 13:48 PGA Target 68 72 72 COMPLETE
03-Oct 13:50 PGA Target 72 76 76 COMPLETE
03-Oct 13:50 SGA Target 280 276 276 COMPLETE
03-Oct 13:50 DEFAULT buffer cache 112 108 108 COMPLETE
03-Oct 13:53 SGA Target 276 272 272 COMPLETE
03-Oct 13:53 PGA Target 76 80 80 COMPLETE
03-Oct 13:53 DEFAULT buffer cache 108 104 104 COMPLETE
03-Oct 13:55 DEFAULT buffer cache 104 100 100 COMPLETE
03-Oct 13:55 SGA Target 272 268 268 COMPLETE
03-Oct 13:55 PGA Target 80 84 84 COMPLETE
03-Oct 13:57 SGA Target 268 264 264 COMPLETE
03-Oct 13:57 PGA Target 84 88 88 COMPLETE
03-Oct 13:57 DEFAULT buffer cache 100 96 96 COMPLETE
03-Oct 13:59 SGA Target 264 260 260 COMPLETE
03-Oct 13:59 PGA Target 88 92 92 COMPLETE
03-Oct 13:59 DEFAULT buffer cache 96 92 92 COMPLETE
03-Oct 14:01 SGA Target 260 256 256 COMPLETE
03-Oct 14:01 DEFAULT buffer cache 92 88 88 COMPLETE
03-Oct 14:01 PGA Target 92 96 96 COMPLETE
03-Oct 14:03 DEFAULT buffer cache 88 84 84 COMPLETE
03-Oct 14:03 SGA Target 256 252 252 COMPLETE
03-Oct 14:03 PGA Target 96 100 100 COMPLETE
03-Oct 14:05 SGA Target 252 248 248 COMPLETE
03-Oct 14:05 DEFAULT buffer cache 84 80 80 COMPLETE
03-Oct 14:05 PGA Target 100 104 104 COMPLETE
03-Oct 16:11 DEFAULT buffer cache 80 76 76 COMPLETE
03-Oct 16:11 PGA Target 104 108 108 COMPLETE
03-Oct 16:11 SGA Target 248 244 244 COMPLETE
03-Oct 16:13 DEFAULT buffer cache 76 72 72 COMPLETE
03-Oct 16:13 PGA Target 108 112 112 COMPLETE
03-Oct 16:13 SGA Target 244 240 240 COMPLETE
03-Oct 16:15 DEFAULT buffer cache 72 68 68 COMPLETE
03-Oct 16:15 PGA Target 112 116 116 COMPLETE
03-Oct 16:15 SGA Target 240 236 236 COMPLETE
03-Oct 16:17 SGA Target 236 232 232 COMPLETE
03-Oct 16:17 DEFAULT buffer cache 68 64 64 COMPLETE
03-Oct 16:17 PGA Target 116 120 120 COMPLETE
03-Oct 16:19 PGA Target 120 124 124 COMPLETE
03-Oct 16:19 SGA Target 232 228 228 COMPLETE
03-Oct 16:19 DEFAULT buffer cache 64 60 60 COMPLETE
03-Oct 16:21 PGA Target 124 128 128 COMPLETE
03-Oct 16:21 DEFAULT buffer cache 60 56 56 COMPLETE
03-Oct 16:21 SGA Target 228 224 224 COMPLETE
03-Oct 16:23 SGA Target 224 220 220 COMPLETE
03-Oct 16:23 DEFAULT buffer cache 56 52 52 COMPLETE
03-Oct 16:23 PGA Target 128 132 132 COMPLETE
03-Oct 16:25 PGA Target 132 136 136 COMPLETE
03-Oct 16:25 SGA Target 220 216 216 COMPLETE
03-Oct 16:25 DEFAULT buffer cache 52 48 48 COMPLETE
03-Oct 16:27 PGA Target 136 140 140 COMPLETE
03-Oct 16:27 SGA Target 216 212 212 COMPLETE
03-Oct 16:27 DEFAULT buffer cache 48 44 44 COMPLETE
03-Oct 16:29 DEFAULT buffer cache 44 40 40 COMPLETE
03-Oct 16:29 SGA Target 212 208 208 COMPLETE
03-Oct 16:29 PGA Target 140 140 140 COMPLETE
119 rows selected.
SQL> SQL> SQL> SQL> SQL> SQL> 2 3
Component Min Size Max Size Granule Size
------------------------- ------------ ------------ ------------
shared pool 64 144 4
large pool 8 120 4
java pool 4 4 4
streams pool 0 0 4
SGA Target 208 324 4
DEFAULT buffer cache 40 156 4
KEEP buffer cache 0 0 4
RECYCLE buffer cache 0 0 4
DEFAULT 2K buffer cache 0 0 4
DEFAULT 4K buffer cache 0 0 4
DEFAULT 8K buffer cache 0 0 4
DEFAULT 16K buffer cache 0 0 4
DEFAULT 32K buffer cache 0 0 4
Shared IO Pool 0 4 4
Data Transfer Cache 0 0 4
In-Memory Area 0 0 4
PGA Target 28 140 4
ASM Buffer Cache 0 0 4
18 rows selected.
SQL>
