Scripts:诊断sga diag信息auto_sga_diag.sql

spool auto_sga_diag.log
 
set line 190 pagesize 1400
 
 SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY"
    FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM"
            FROM v$sga_dynamic_components,
                 (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM"
                    FROM v$process) a
           WHERE component IN ('shared pool',
                               'large pool',
                               'java pool',
                               'streams pool',
                               'DEFAULT buffer cache')) a,
         (SELECT SUM(pga_alloc_mem) / 1024 / 1024 "PGA_MEM" FROM v$process) b;
 
select component,
       current_size / 1024 / 1024 "CURRENT_SIZE",
       min_size / 1024 / 1024 "MIN_SIZE",
       user_specified_size / 1024 / 1024 "USER_SPECIFIED_SIZE",
       last_oper_type "TYPE"
  from v$sga_dynamic_components;
 
select component, granule_size / 1024 / 1024 "GRANULE_SIZE(Mb)"
  from v$sga_dynamic_components;
 
col component for a25
col status format a10 head "Status"
col initial_size for 999,999,999,999 head "Initial"
col parameter for a25 heading "Parameter"
col final_size for 999,999,999,999 head "Final"
col changed head "Changed At"
col low format 999,999,999,999 head "Lowest"
col high format 999,999,999,999 head "Highest"
col lowMB format 999,999 head "MBytes"
col highMB format 999,999 head "MBytes"
 
SELECT component,
       parameter,
       initial_size,
       final_size,
       status,
       to_char(end_time, 'mm/dd/yyyy hh24:mi:ss') changed
  FROM v$sga_resize_ops
 ORDER BY component;
 
SELECT component,
       min(final_size) low,
       (min(final_size / 1024 / 1024)) lowMB,
       max(final_size) high,
       (max(final_size / 1024 / 1024)) highMB
  FROM v$sga_resize_ops
 GROUP BY component
 ORDER BY component;
 
SELECT name, bytes FROM v$sgainfo;
 
select * from v$sgastat order by bytes asc;
 
select name, trunc(bytes / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is null
union
select pool, trunc(sum(bytes) / 1024 / 1024, 2) "size(MB)"
  from v$sgastat
 where pool is not null
 group by pool;
 
 select * from V$SGA_CURRENT_RESIZE_OPS;
 
 select * from v$sga_target_advice;
 
show parameter size
show parameter statistics        
 
spool off;