Obtaining Information about the SGA - Clearing the Buffer Cache

Obtaining Information about the SGA

 

select value from v$parameter where upper(name)='MEMORY_TARGET';
select value/1024/1024 mb
from v$parameter where upper(name) = 'SGA_MAX_SIZE'
-- size taken by a memory component
select pool, round(sum(BYTES)/1024/1024) MB
from V$SGASTAT
group by pool
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')
-- 10g
SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
FROM V$SGA_DYNAMIC_COMPONENTS
WHERE CURRENT_SIZE <>0
-- Oracle 11g
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;
-- To know how Oracle has modified the memory area sizes by time
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

Clearing the Buffer Cache

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE ;