发现大量ORA-01555
发现大量ORA-01555
ORA-01555 caused by SQL statement below (SQL ID: 2cqskkn0c41kq, Query Duration=3906 sec, SCN: 0x0000.ad2056a9):
ORA-01555 caused by SQL statement below (SQL ID: 2dmk2qf1dxkwt, Query Duration=4332 sec, SCN: 0x0000.acf400a7):
ORA-01555 caused by SQL statement below (SQL ID: 2dmk2qf1dxkwt, Query Duration=5098 sec, SCN: 0x0000.ae793e79):
一般Alert日志里面发现这种，就是回滚段太小了，一般不用管，如果确实影响到业务，可以杀一些占用资源搞的回滚或查询进程。
1.查看大事务 gv$transaction
SQL> select inst_id,addr,used_urec,used_ublk from gv$transaction;
SQL> select start_date,inst_id,addr,used_urec,used_ublk from gv$transaction;
START_DATE INST_ID ADDR USED_UREC USED_UBLK
----------------- ---------- ---------------- ---------- ----------
20121019 06:08:53 2 07000001FCC35D08 1 1
20121019 07:20:15 2 07000001FB884A78 9 1
20121019 07:20:15 2 07000001FDF215A0 6 1
20121019 07:20:15 2 07000001FDF76818 9 1
20121019 06:42:42 1 07000001FDF06398 10 1
20121019 07:16:53 1 07000001FB8A1660 6 1
20121019 06:40:28 1 07000001FCC6EC38 6 1
20121019 07:20:15 1 07000001F9B42A68 9 1
20121019 05:13:54 1 07000001FDF79338 42976487 693162
2.根据上面gv$transaction中ADDR地址与v$session中的taddr字段匹配查找sid,serial#
SQL> select sql_child_number,
sid,
sql_id,last_call_et,
blocking_session,
blocking_instance,
state,
event,
p1,
p2,
seconds_in_wait
from v$session
where
taddr='07000001FDF79338';
3.根据sid可以查找spid
查询得到该session对应的OS进程号:
SQL> select 'kill -9 '||spid from v$process where addr = (select paddr from v$session where sid=&sid);
4.监控undo 使用情况
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
,sum(bytes)/1024/1024 bytes
from dba_data_files
group by tablespace_name) a,
,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;
SQL> select tablespace_name,status,sum(bytes)/1024/1024/1024 from dba_undo_extents group by tablespace_name,status order by 1,3;
5.可以考虑使用
kill -9 spid
或alter system kill session '986,16161';的方式
6.检查会话是否已经被杀掉
select a.sid,a.serial#,b.spid,a.status from v$session a,v$process b where a.paddr=b.addr and a.taddr='07000001FDF79338';
7.事务回滚监控
SQL> select * from v$fast_start_transactions;
USN SLT SEQ STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PID CPUTIME PARENTUSN PARENTSLT PARENTSEQ XID PXID RCVSERVERS
---------- ---------- ---------- ---------------- -------------- --------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ----------
10 38 4314095 RECOVERING 50 906028 67 42 0 0 0 000A00260041D3EF 0000000000000000 32
SQL> select file_id,block_id from dba_rollback_segs where SEGMENT_ID=10;
FILE_ID BLOCK_ID
---------- ----------
2 153