SQL Script to Find out Total SGA and Current SGA Utilization

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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
/



Sample Output


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

_____________________________________________________________________________________________________________________

0 comments:

Post a comment

 

acehints.com Copyright 2011-20 All Rights Reserved | Site Map | Contact | Disclaimer