---检查undo
show parameter undo_
---检查undo rollback segment 使用情况
select name, rssize, extents, latch, xacts, writes, gets, waits
from v$rollstat a, v$rollname b
where a.usn = b.usn
order by waits desc;
---每个事务产生的redo 块大小
select a.redoblocks / b.trancount
from (select value redoblocks
from v$sysstat
where name = 'redo blocks written') a,
(select value trancount from v$sysstat where name = 'user commits') b;
---计算每秒钟产生的undoblk数量
select sum(undoblks) / sum((end_time - begin_time) * 24 * 60 * 60)
from v$undostat;
---查询undo具体信息
COL undob FORMAT 99990;
COL trans FORMAT 99990;
COL snapshot2old FORMAT 9999999990;
SELECT t.BEGIN_TIME BEGIN_TIME,
t.END_TIME END_TIME,
undoblks "UndoB",
txncount "Trans",
maxquerylen "LongestQuery",
maxconcurrency "MaxConcurrency",
ssolderrcnt "Snapshot2Old",
nospaceerrcnt "FreeSpaceWait"
FROM v$undostat t;
--查询rollback 段详细信息(收缩次数,扩展次数,平均活动事务等)
--COL RBS FORMAT a4;
SELECT n.name "RBS",
s.extends "Extends",
s.shrinks "Shrinks",
s.wraps "Wraps",
s.aveshrink "AveShrink",
s.aveactive "AveActive"
FROM v$rollname n
JOIN v$rollstat s
USING (usn)
WHERE n.name != 'SYSTEM';
---查询当前rollback segment使用情况
COL RBS FORMAT a4;
SELECT n.name "RBS",
s.status,
s.waits,
s.gets,
s.writes,
s.xacts "Active Trans"
FROM v$rollname n
JOIN v$rollstat s
USING (usn)
WHERE n.name != 'SYSTEM';
---查询使用rollback segment时等待比率
SELECT ROUND(SUM(waits/gets)*100,2)||'%' "Contention" FROM
v$rollstat;
Oracle ----undo 与回滚段
最新推荐文章于 2023-01-19 10:59:28 发布