v$undostat //undo虽然自动管理,但是原理还是v$undostat,历史是 dba_hist_undostat
应急步骤
1.@undousage –看undo使用率
TABLESPACE STATUS USED_SIZE TBS_SIZE USED_PCT
———- ——— ——— ——– ——–
UNDOTBS1 ACTIVE 16 73712 0.02
UNDOTBS1 EXPIRED 13436 73712 18.23
UNDOTBS1 UNEXPIRED 53310 73712 72.32
UNDOTBS2 EXPIRED 2641 73712 3.58
UNDOTBS2 UNEXPIRED 114 73712 0.15
2.@asql
SID EVENT SQL_ID PHV EXECS ELAS WAITED BLKSID SQL_TEXT
——- ———————– ————— ———- ——— —- —— —— —————————————-
15192,3 db file sequential read fba7p04w86w61,1 3923078372 375560025 0 0 SELECT v_uvr_id, v_uvr_key, v_multi_org_
11028,1 db file sequential read fba7p04w86w61,1 3923078372 375560024 0 0 SELECT v_uvr_id, v_uvr_key, v_multi_org_
3.@trans
SID USERNAME MACHINE PROGRAM USED_UBLK USED_UREC START_TIME STATUS
——- ——– ————— —————- ——— ——— —————– ——
10537,1 SASOSE by18atfcoap2001 JDBC Thin Client 1 2 10/26/20 10:14:41 ACTIVE
undo看三方面：
0.active的undo看使用率
1.@asql 看当时的语句 看是否有时间长的dml
2.@trans 看事务看undo使用块数 USED_UBLK、行数 USED_UREC 和开始时间 START_TIME。
3.看 session 状态是否正常，是不是ap长时间没提交。
===更多脚本请私信博主：
>>>>>>|undousage.sql|<<<<<<
select case round((total_used_undo/undo_tbs_size)*100)
when 0 then 1
else round((total_used_undo/undo_tbs_size)*100) end as undo_used_percent,
total_used_undo,
undo_tbs_size
from (select (sum(bytes)/1024/1024) as undo_tbs_size
from dba_data_files
where tablespace_name = (select value from v$parameter where name=’undo_tablespace’))
join
(select (sum(undoblks)*8)/1024 as total_used_undo
from v$undostat us
where us.begin_time >= sysdate – (select case when to_number(value) < 3600
then to_number(value)/60/1440
else to_number(value)/3600/24 end
from v$parameter where name=’undo_retention’))
on 1=1;
CLEAR COLUMNS BREAKS COMPUTES
COLUMN tablespace FORMAT a20 HEADING ‘Tablespace Name’
COLUMN sizeb FORMAT 999,999,999 HEADING ‘Used Size’
COLUMN sizea FORMAT 999,999,999 HEADING ‘Tablespace Size’
COLUMN status FORMAT a12 HEADING ‘Status’
COLUMN pct FORMAT a8 HEADING ‘Used Pct’
CLEAR COMPUTES BREAKS
BREAK ON tablespace on report
COMPUTE sum LABEL “Total: ” OF sizeb ON report
select a.tablespace_name tablespace
,b.status status
,b.bytes sizeb
,a.bytes sizea
,round(100*(b.bytes/a.bytes),2)||’%’ pct
from
(select tablespace_name
,sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name
,status
,sum(bytes)/1024/1024 bytes
from dba_undo_extents
group by tablespace_name,status) b
where a.tablespace_name=b.tablespace_name
order by 1,2;
原文地址:https://blog.csdn.net/weixin_49889731/article/details/135402004
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_53256.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
