以下脚本可以用于收集自动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;
