Scripts:诊断sga diag信息auto_sga_diag.sql

最新推荐文章于 2024-04-24 15:26:57 发布

转载最新推荐文章于 2024-04-24 15:26:57 发布 · 691 阅读

文章标签：

#sga #diag #诊断

Scripts 专栏收录该内容

136 篇文章

订阅专栏

本文提供了一组SQL查询，用于分析SGA内存组件的当前大小、最小大小、用户指定大小及类型，并展示了SGA内存组件的粒度大小。此外，文章还介绍了SGA内存调整的操作历史、最低和最高大小记录，以及SGA当前的调整状态和建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;