SQL Script to Find out Total SGA and Current SGA Utilization

This query gives you the total SGA and its current usage by combining v$sga, v$sgastat and v$sga_dynamic_components dynamic views. 

Query

Select round(tot.bytes /1024/1024 ,2) total_SGA_mb,
round(DyComp.bytes /1024/1024 ,2) Dynamic_SGA_COMP_MB,
	round(USED.bytes /1024/1024 ,2) USED_mb,
	round((DyComp.bytes-USED.bytes)/1024/1024,2) free_mb
from 	(select sum(bytes) bytes from v$sgastat where name != 'free memory') USED,
	(select sum(value) bytes from v$sga) tot,
      (select sum(current_size) bytes FROM v$sga_dynamic_components) DyComp
/

advertisements

 

Sample Output

TOTAL_SGA_MB DYNAMIC_SGA_COMP_MB    USED_MB    FREE_MB
------------ ------------------- ---------- ----------
	   800		     468     444.38	 23.62