5 Replies Latest reply: Jan 17, 2011 3:14 PM by 726263 RSS

    ORA-01555  Snapshot too old

    user8686720
      Hi,

      At one of our production database we are observing ORA-0155 error quite frequently. Alert log reflects that this is caused by number of select and insert statements. These statements are not always identical. Sometime we are also getting this ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS1'.

      The database version is 10.2.0.4 64 bit. Undo retention is set with 1800 sec, though from the view v$undostat it has been observed that the TUNED_UNDORETENTION is achieving quite high than the value mentioned in the parameter file.

      Any suggestion regarding this will be highly appreciated.

      DB Version: 10.2.0.4 64 bit
      Platform: HP-UX itanium

      Thanks in advance for your valuable time.

      Regards,
      Joy

      Edited by: user8686720 on Jan 17, 2011 11:58 AM
        • 1. Re: ORA-01555  Snapshot too old
          sb92075
          Any suggestion regarding this will be highly appreciated.
          increase size of UNDOTBS1
          • 2. Re: ORA-01555  Snapshot too old
            Chinar
            user8686720 wrote:
            Hi,

            At one of our production database we are observing ORA-0155 error quite frequently. Alert log reflects that this is caused by number of select and insert statements.
            You need increase UNDO_RETENTION parameter.Also use UNDO adviser and determine optimal size of undo tablespace.
            These statements are not always repeatable. Sometime we are also getting this ORA-30036: unable to extend segment by 4 in undo tablespace 'UNDOTBS1'.
            Set auto extensible on for undo tablespace.
            • 3. Re: ORA-01555  Snapshot too old
              user8686720
              Thanks to both of u Sb and Chinar for ur suggestion...
              • 4. Re: ORA-01555  Snapshot too old
                726263
                Sometimes it may not be good to set the UNDO tablespace to auto extensible.
                This helps to prevent large running away SQL from happening.
                • 5. Re: ORA-01555  Snapshot too old
                  CKPT
                  It is not depend only in undo size but also

                  Post.

                  1) what is the max query length in you database?
                  select MAXQUERYID,MAXQUERYLEN from v$undostat;
                  2) what is the undo retention period?
                  show paraemter undo
                  1) either need tuning SQL query.
                  2) Increase undo retention ..
                  3) go for fixed UNDO.

                  i suggest you to read tom article as mentioned by SB. that is the best analysis document

                  *ORA-01555 "Snapshot too old" - Detailed Explanation [ID 40689.1]*

                  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:1441804355350




                  SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
                  SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
                  ROUND((d.undo_size / (to_number(f.value) *
                  g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
                  FROM (
                  SELECT SUM(a.bytes) undo_size
                  FROM v$datafile a,
                  v$tablespace b,
                  dba_tablespaces c
                  WHERE c.contents = 'UNDO'
                  AND c.status = 'ONLINE'
                  AND b.name = c.tablespace_name
                  AND a.ts# = b.ts#
                  ) d,
                  v$parameter e,
                  v$parameter f,
                  (
                  SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
                  undo_block_per_sec
                  FROM v$undostat
                  ) g
                  WHERE e.name = 'undo_retention'
                  AND f.name = 'db_block_size'


                  Thanks.