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
/

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-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google