DB Memory/SGA queries

Memory usage and utilization

Query 1:

select round(used.bytes /1024/1024 ,2) used_mb
,round(free.bytes /1024/1024 ,2) free_mb
,round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
,(select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
,(select sum(bytes) bytes
from v$sgastat) tot

Query 2:

select sum(bytes)/1024/1024 " SGA size used in MB" from v$sgastat where name!='free memory'; 

Query 3:

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : perf_sga_usage.sql                                              |
-- | CLASS    : Tuning                                                          |
-- | PURPOSE  : Report on all components within the SGA.                        |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY   off
COLUMN bytes   FORMAT  999,999,999
COLUMN percent FORMAT  999.99999
break on report
compute sum of bytes on report
compute sum of percent on report
SELECT
    a.name
  , a.bytes
  , a.bytes/(b.sum_bytes*100)  Percent
FROM sys.v_$sgastat a
   , (SELECT SUM(value)sum_bytes FROM sys.v_$sga) b
ORDER BY bytes DESC
/