Script:收集自动SGA内存管理ASMM诊断信息

以下脚本可以用于收集自动SGA(sga_target>0)内存管理ASMM下的实例诊断信息：

 
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;