HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

ORA-01555错误处理一例

[English]

作者: fuyuncat

来源: www.HelloDBA.com

日期: 2008-07-07 14:50:13

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

近来生产系统反馈,时常有作业被异常中止,导致应用程序被hung住。经过检查日志,是某个作业在运行时发生了1555错误,导致程序无法返回结果:

* 
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 9 with name "RBS08" too small
ORA-06512: at "SQLUSER.EXT_PKG", line 4917
ORA-06512: at line 1

 

相关程序记录下的日志:

STAT-S.20060313185536.lg = 2 processed =20060313185536 end date Error = ORA-01555: snapshot too old: rollback segment number 9 with name "RBS08" too small BEGIN ext_pkg.main('extfiles','50','EAT'); END;

错误分析解决

这是一个典型的1555错误。检查引发该错误的PACKAGE,发现它只有一个入口函数main(及程序日志中记录的函数),但这个函数还调用了其他NPACKAGE里面的函数。这是一个大作业,执行时,设置它使用了一个大的回滚段:RBS_BATCH1

先看看相关配置:rollback tablespace空间为8Gundo_retention1800

看看回滚段的统计数据:

SQL> Select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
  2         rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
  3         rs.Shrinks "# Shrinks", rs.Extends "# Extends"
  4  from   sys.v_$RollName rn, sys.v_$RollStat rs
  5  where  rn.usn = rs.usn;
 
Rollback Segment                Size (KB)       Gets      Waits    % Waits  # Shrinks  # Extends                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
SYSTEM                                952       1189          0          0          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
RBS_BATCH1                         409592     681422        667 .097883544          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
... ...S_BATCH1 getshould be provided by DEV Team.r.and CSS_EMAN_INDX exist.eir 
RBS10                              204792     478502         10 .002089855          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
RBS11                              204792     477366          8 .001675863          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
RBS12                              204792     491070          6 .001221822          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
RBS_BATCH2                         409592     650088        644 .099063511          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
RBS_BATCH3                         409592     243849          3  .00123027          0          0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 
16 rows selected.

 

注意到RBS_BATCHT1wait%0.098%,这个值应该是比较好的一个值。

 

回过头再来看依法错误的语句:调用ext_pkg.main函数。在程序日志中已经记录下了输入参数,这就比较好办了:作一个trace,看看到底哪条语句的性能最差:

SQL> alter system set events ‘10046 trace name context forever, level 1’;