RBS monitor to catch ORA-1555 errors

One of the most vexing errors in Oracle is this one: 

ORA-1555: snapshot too old: rollback segment number 9 with name "R07" too small.

What causes this error? Oracle fails to return a consistent set of results (often called a snapshot) for a long-running query. This occurs because not enough information remains in the rollback segments to reconstruct the older data.

What are the reasons for the snapshot being too old?

  1. Too many transactions on the same rollback segment (after it reached its maximum size)
  2. Not enough space in the tablespace
  3. Tablespace reached its max extents number (max number of extents = TS extent size * TS max extents no / RBS extent size)
  4. Rollback segment reached its max extents number
  5. If the parameter MAXEXTENTS for the RBs is not unlimited (but has a value) then the RBs cannot grow to the maximum size of the TBS, rather to the multiple of extents_size by maxextents number.

You can avoid this error by creating more or larger rollback segments. Alternatively, long-running queries can be issued when there are few concurrent transactions, or you can obtain a shared lock on the table you are querying, thus prohibiting any other exclusive locks during the transaction.

Another reason is that you may be encountering the 'fetch across commits' problem. If you are committing within a cursor then you should close and re-open the cursor when you commit.

    Requires Free Membership to View

    Login

So what can you do to alert you to this situation? Here I introduce my RBS monitor. This monitor runs in the background, collecting information. If a 'snapshot too old' error occurs, you can return to the table's monitor, querying it to detect the exact problem and the time the error occurd.

Restrictions: The monitor is not magic. It can only detect errors occuring from using the RBS. It cannot detect problems occuring because of dblink to another database, or the sneaky 'fetch across commit' problem. If you don't find anything in the monitor, try checks in those directions.

The code consist of three parts: 1) A table in any schema in the database; 2) A shell script run on the Unix machine; and 3)Querying the table.

1. Create the table 

create table CHECK_USED_OF_RBS
(
  my_date     DATE,
  NAME        VARCHAR2(30) not null,
  EXTENTS     NUMBER,
  MAX_EXTENTS NUMBER,
  RSSIZE      NUMBER,
  OPTSIZE     NUMBER,
  USERNAME    VARCHAR2(30) not null,
  SID         NUMBER,
  STATUS      VARCHAR2(8),
  MACHINE     VARCHAR2(64),
  SQL_TEXT    VARCHAR2(1000)
)
tablespace DATA_TBS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 256K
    next 256K
    minextents 1
    maxextents 505
    pctincrease 0
  );
2. Shell & SQL scripts. You should create two files on your Unix machine: monitor_rbs.csh and monitor_rbs.sql. Here is the shell script monitor_rbs.csh:

#!/bin/csh
setenv ORACLE_SID vanprod8
setenv ORACLE_HOME /export/home/oracle8
setenv PATH $ORACLE_HOME/bin:.:${PATH}
cd /export/home/oracle8/scripts/db_control

sqlplus db_control/db_control @monitor_rbs
exit

Here is the SQL script run by the shell script: monitor_rbs.sql:

set echo on;
set timing on;
insert into check_used_of_rbs
select sysdate, rn.name, rs.extents, 
       ds.max_extents, rs.rssize,
       rs.optsize, du.username,
       se.sid, se.status,
       se.machine, sq.sql_text
  from v$transaction tr,
       v$rollstat rs,
       v$sql sq,
       v$session se,
       v$rollname rn,
       dba_users du,
       dba_segments ds
 where tr.xidusn = rn.usn
   and sq.PARSING_USER_ID = du.user_id
   and se.sql_address = sq.address
   and rs.usn = rn.usn
   and se.username = du.username
   and rn.name = ds.segment_name
   and tr.addr = se.taddr
   and sq.parse_calls = (select max(parse_calls) 
                           from v$sql sq2
                          where sq2.parsing_user_id = sq.parsing_user_id
                            and sq2.address = sq.address);
exit
3. Querying the table. This query checks used of RBS in the last hour[s]. Change the time limit to suite your needs. Run it to check what hapened during the time of failure.

select
       to_char(my_date,'dd/mm/yy hh24mi') "date",
       name "rollback", extents, 
       max_extents "max_extents", ROUND(rssize/1024/1024,0) "size_MB",
       optsize/1024/1024 "optimal_MB", username "user_name",
       sid "session_id", status,
       machine "machine_name", sql_text
  from check_used_of_rbs
 where my_date > sysdate-1/24

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in April 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top