Oracle uodo使用率查询
5墨值下载
-- 整体 undo 使用情况
select b.tablespace_name,
nvl(used_undo, 0) "USED_UNDO(M)",
total_undo "Total_undo(M)",
trunc(nvl(used_undo, 0) / total_undo * 100, 2) used_PCT
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status in ('ACTIVE', 'UNEXPIRED')
and tablespace_name in
(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
from dba_data_files
where tablespace_name in
(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name) b
where a.tablespace_name(+) = b.tablespace_name;
-- 各状态 undo 使用情况统计
select tablespace_name,status,sum(bytes)/1024/1024 MB from dba_undo_extents
where tablespace_name like 'UNDOTBS%'
group by tablespace_name,status
order by 1;
======
select tablespace_name, status, sum(bytes/1024/1024) "MB"
from dba_undo_extents
group by tablespace_name, status
order by 1, 2
========
--UNDO 使用率
select AA.TABLESPACE_NAME,
ROUND((nvl(BB.UNEXPIRED_USED_GB, 0) + nvl(BB.ACTIVE_USED_GB, 0)) /
AA.TOTAL_GB * 100,
2) USED_PCT,
ROUND(nvl(BB.ACTIVE_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_ACTIVE,
ROUND(nvl(BB.UNEXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_UNEXPIRED,
ROUND(nvl(BB.EXPIRED_USED_GB, 0) / AA.TOTAL_GB * 100, 2) PCT_EXPIRED,
maxquerylen,
tuned_undoretention,
pct_retention,
AA.TOTAL_GB,
nvl(BB.ACTIVE_USED_GB, 0) ACTIVE_USED_GB,
nvl(BB.UNEXPIRED_USED_GB, 0) UNEXPIRED_USED_GB,
nvl(BB.EXPIRED_USED_GB, 0) EXPIRED_USED_GB,
nvl(BB.ACTIVE_EXTENT_CNT, 0) ACTIVE_EXTENT_CNT,
nvl(BB.UNEXPIRED_EXTENT_CNT, 0) UNEXPIRED_EXTENT_CNT,
nvl(BB.EXPIRED_EXTENT_CNT, 0) EXPIRED_EXTENT_CNT
from (select tablespace_name, sum(bytes) / 1024 / 1024 / 1024 TOTAL_GB
from DBA_DATA_FILES
where tablespace_name IN ('UNDOTBS2', 'UNDOTBS1')
GROUP BY tablespace_name) AA,
(select lix.TABLESPACE_NAME,
max(case
when lix.STATUS = 'ACTIVE' then
USED_GB
end) ACTIVE_USED_GB,
max(case
when lix.STATUS = 'UNEXPIRED' then
USED_GB
end) UNEXPIRED_USED_GB,
max(case
when lix.STATUS = 'EXPIRED' then
USED_GB
end) EXPIRED_USED_GB,
max(case
when lix.STATUS = 'ACTIVE' then
EXTENT_CNT
end) ACTIVE_EXTENT_CNT,
max(case
when lix.STATUS = 'UNEXPIRED' then
EXTENT_CNT
end) UNEXPIRED_EXTENT_CNT,
max(case
when lix.STATUS = 'EXPIRED' then
EXTENT_CNT
end) EXPIRED_EXTENT_CNT
from (SELECT TABLESPACE_NAME,
STATUS,
SUM(BYTES) / 1024 / 1024 / 1024 USED_GB,
COUNT(*) EXTENT_CNT
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS) lix
group by lix.TABLESPACE_NAME) BB,
(select max(maxquerylen) maxquerylen,
max(tuned_undoretention) tuned_undoretention,
round(max(maxquerylen) / max(tuned_undoretention) * 100, 2)
pct_retention
from DBA_HIST_UNDOSTAT) CC
WHERE AA.TABLESPACE_NAME = BB.TABLESPACE_NAME
ORDER BY 1;
=======
select b.tablespace_name,
nvl(used_undo, 0) "USED_UNDO(M)",
total_undo "Total_undo(M)",
trunc(nvl(used_undo, 0) / total_undo * 100, 2) used_PCT
from (select nvl(sum(bytes / 1024 / 1024), 0) used_undo, tablespace_name
from dba_undo_extents
where status in ('ACTIVE', 'UNEXPIRED')
and tablespace_name in
(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name) a,
(select tablespace_name, sum(bytes / 1024 / 1024) total_undo
of 3
5墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论