All about SGA
SYS @ catdb > select value/1024/1024 mb from v$parameter where upper(name) = 'SGA_MAX_SIZE';
MB
----------
800
SYS @ catdb > select pool, round(sum(BYTES)/1024/1024) MB from V$SGASTAT group by pool;
POOL MB
------------ ----------
292
java pool 4
shared pool 208
large pool 12
SYS @ catdb > select name , value/1024/1024 MB
from v$parameter
where upper(name) in (
'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE'); 2 3 4 5
NAME MB
-------------------------------------------------------------------------------- ----------
db_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
SYS @ catdb > SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0; 2 3
COMPONENT MB
---------------------------------------------------------------- ----------
shared pool 208
large pool 12
java pool 4
DEFAULT buffer cache 284
SYS @ catdb > SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS; 2 3
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool 208 132 208
large pool 12 12 72
java pool 4 4 4
streams pool 0 0 0
SGA Target 520 520 520
DEFAULT buffer cache 284 284 356
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 280 280 280
ASM Buffer Cache 0 0 0
16 rows selected.
SYS @ catdb > select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ; 2 3 4 5
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- --------------- -------------- -------------
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE KEEP buffer cache STATIC db_keep_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache STATIC db_cache_size 0 300 300
24-JUN-20 24-JUN-20 COMPLETE ASM Buffer Cache STATIC db_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE PGA Target STATIC pga_aggregate_target 0 280 280
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE SGA Target STATIC sga_target 0 520 520
24-JUN-20 24-JUN-20 COMPLETE streams pool STATIC streams_pool_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE java pool STATIC java_pool_size 0 4 4
24-JUN-20 24-JUN-20 COMPLETE large pool STATIC large_pool_size 0 72 72
24-JUN-20 24-JUN-20 COMPLETE shared pool STATIC shared_pool_size 0 132 132
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache INITIALIZING db_cache_size 300 300 300
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW IMMEDIATE shared_pool_size 132 136 136
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 300 296 296
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache GROW DEFERRED db_cache_size 296 356 356
24-JUN-20 24-JUN-20 COMPLETE large pool SHRINK DEFERRED large_pool_size 72 12 12
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 136 140 140
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 356 352 352
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 352 348 348
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 140 144 144
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 348 344 344
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 144 148 148
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 344 340 340
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 148 152 152
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 340 336 336
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 152 156 156
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 156 160 160
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 336 332 332
26-JUN-20 26-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 160 168 168
26-JUN-20 26-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 332 324 324
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 324 316 316
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 168 176 176
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 316 308 308
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 176 184 184
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 184 192 192
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 308 300 300
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 192 200 200
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 300 292 292
03-JUL-20 03-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 292 284 284
03-JUL-20 03-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 200 208 208
44 rows selected.
SYS @ catdb > select value/1024/1024 mb from v$parameter where upper(name) = 'SGA_MAX_SIZE';
MB
----------
800
SYS @ catdb > select pool, round(sum(BYTES)/1024/1024) MB from V$SGASTAT group by pool;
POOL MB
------------ ----------
292
java pool 4
shared pool 208
large pool 12
SYS @ catdb > select name , value/1024/1024 MB
from v$parameter
where upper(name) in (
'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE'); 2 3 4 5
NAME MB
-------------------------------------------------------------------------------- ----------
db_cache_size 0
db_keep_cache_size 0
db_recycle_cache_size 0
SYS @ catdb > SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0; 2 3
COMPONENT MB
---------------------------------------------------------------- ----------
shared pool 208
large pool 12
java pool 4
DEFAULT buffer cache 284
SYS @ catdb > SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
FROM V$MEMORY_DYNAMIC_COMPONENTS; 2 3
COMPONENT CURRENT_SIZE MIN MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool 208 132 208
large pool 12 12 72
java pool 4 4 4
streams pool 0 0 0
SGA Target 520 520 520
DEFAULT buffer cache 284 284 356
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
PGA Target 280 280 280
ASM Buffer Cache 0 0 0
16 rows selected.
SYS @ catdb > select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ; 2 3 4 5
START_TIM END_TIME STATUS COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ---------------------------------------------------------------- ------------- --------- -------------------------------------------------------------------------------- --------------- -------------- -------------
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE KEEP buffer cache STATIC db_keep_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache STATIC db_cache_size 0 300 300
24-JUN-20 24-JUN-20 COMPLETE ASM Buffer Cache STATIC db_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE PGA Target STATIC pga_aggregate_target 0 280 280
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE SGA Target STATIC sga_target 0 520 520
24-JUN-20 24-JUN-20 COMPLETE streams pool STATIC streams_pool_size 0 0 0
24-JUN-20 24-JUN-20 COMPLETE java pool STATIC java_pool_size 0 4 4
24-JUN-20 24-JUN-20 COMPLETE large pool STATIC large_pool_size 0 72 72
24-JUN-20 24-JUN-20 COMPLETE shared pool STATIC shared_pool_size 0 132 132
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache INITIALIZING db_cache_size 300 300 300
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW IMMEDIATE shared_pool_size 132 136 136
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size 300 296 296
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache GROW DEFERRED db_cache_size 296 356 356
24-JUN-20 24-JUN-20 COMPLETE large pool SHRINK DEFERRED large_pool_size 72 12 12
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 136 140 140
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 356 352 352
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 352 348 348
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 140 144 144
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 348 344 344
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 144 148 148
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 344 340 340
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 148 152 152
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 340 336 336
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 152 156 156
24-JUN-20 24-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 156 160 160
24-JUN-20 24-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 336 332 332
26-JUN-20 26-JUN-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 160 168 168
26-JUN-20 26-JUN-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 332 324 324
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 324 316 316
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 168 176 176
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 316 308 308
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 176 184 184
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 184 192 192
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 308 300 300
02-JUL-20 02-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 192 200 200
02-JUL-20 02-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 300 292 292
03-JUL-20 03-JUL-20 COMPLETE DEFAULT buffer cache SHRINK DEFERRED db_cache_size 292 284 284
03-JUL-20 03-JUL-20 COMPLETE shared pool GROW DEFERRED shared_pool_size 200 208 208
44 rows selected.
No comments:
Post a Comment