Oracle Memory usage Queries

Some Important Oracle database Memory management queries.

SGA usage by Oracle Instance:

select  round(sum(bytes)/1024/1024,2)||' MB' total_sga,round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used,round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;

SGA Stat:

SELECT pool, name, bytes / 1024 / 1024 AS "MB"

FROM v$sgastat

WHERE pool IN ('java pool', 'shared pool', 'large pool', 'streams pool')

ORDER BY pool, bytes DESC;

Total SGA:

SELECT sum(bytes)/1024/1024 AS "Total SGA Size (MB)"

FROM v$sgastat;

Using V$PROCESS to see individual process memory usage

SELECT p.spid, p.program, p.pga_max_mem, p.pga_alloc_mem, p.pga_used_mem, p.pga_freeable_mem

FROM v$process p;

Using V$PGASTAT for overall PGA statistics

SELECT name, value

FROM v$pgastat;

 

 

Best Wishes!!!