-- In Oracle database, SGA represents shared memory structures, 
-- which includes server and background processes.  Typically
-- cached data blocks and shared SQL area will be stored in 
-- SGA

Show SGA => show SGA output

-- Listing of all SGA structures
SELECT * FROM V$SGASTAT;

SELECT pool, bytes/1024/1024 bytes_mb
FROM V$SGASTAT
WHERE NAME = 'free memory';

=> Listing of SGA Memory

SELECT * FROM v$sgainfo;

=> Listing of SGAInfo

-- Queries to compute SGA_Target using 
-- v$sga and v$sga_dynamic_free_memory

SELECT name, value, ROUND(value/1024/1024,4) size_mb
FROM v$sga;

SELECT * FROM v$sga_dynamic_free_memory;

WITH q_sga AS
 (SELECT SUM(value) v_sga FROM v$sga)
SELECT
 v_sga-current_size  sga_target 
FROM   q_sga, v$sga_dynamic_free_memory;

=> SGA/SGA_Dynamic_Free_Memory

SELECT * FROM v$sga_target_advice ORDER BY sga_size;

=> v$SGA_Target_Advice

SELECT  DECODE(GROUPING(status), 0, status,
 'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')')
  sga_Resize_Ops_details,
 COUNT(*) counts
From V$sga_Resize_Ops
GROUP BY ROLLUP(status);

Above Query => v$SGA_Resize_Ops Details

SELECT  DECODE(Grouping_ID(oper_type, NVL(oper_mode,'--')), 
 0,  NVL(oper_mode,'--'),
 1, '   ['||oper_type||'] Total ....',
 'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')')
  sga_Resize_Ops_details,
 count(*) COUNTs
FROM V$sga_Resize_Ops
Group By  ROLLUP(oper_type, NVL(oper_mode,'--'));

SELECT  DECODE(Grouping_ID(oper_type, status), 0, status,
 1, '   ['||oper_type||'] Total ....',
 'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')')
  sga_Resize_Ops_details,
 count(*) COUNTs
FROM V$sga_Resize_Ops
Group By  ROLLUP(oper_type, status);

Above queries => v$SGA_Resize_Ops Details


SELECT * FROM V$sga_Current_Resize_Ops;


SELECT
 Component,
 Last_Oper_Type,
 Min_Size,
 Max_Size,
 Current_Size,
 granule_size,
 Oper_Count,
 Last_Oper_Mode,
 to_char(Last_Oper_Time,'mm/dd/yyyy hh24:mi:ss') last_op_time
FROM V$sga_Dynamic_Components;

=> V$sga_Dynamic_Components Details