13 Replies Latest reply: Jan 13, 2011 8:49 PM by Hemant K Chitale RSS

    ORA-01555: snapshot too old

    Yoav
      Hello,

      I have an application which shows in its APPLICATION LOG ONLY every few minute :
      util.DataStoreException: ORA-01555: snapshot too old: rollback segment number  with name "" too small
      ORA-22924: snapshot too old
      Failing ora-01555 statment generally is written also into the alert log .
      In this case i see no entry in the DATABASE alert log.

      Also the undo tablespace is 27 GB and it is 99% free - ALWAYS , so i dont see any reason to increase its size.
      The longest transaction that i succeeded to monitor took not more that 5 secs.

      The application itself its an 3rd party closed application.No change was done in the application/DB .

      Any advice ?

      Thnaks
        • 1. Re: ORA-01555: snapshot too old
          731759
          Increase the UNDO_RETENTION parameter value.

          Also refer,

          ORA-01555: snapshot too old: rollback segment number

          Thanks
          • 2. Re: ORA-01555: snapshot too old
            829885
            Hi,

            Execute following query to get the optimal undo_retention size to be increased.

            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'
            • 3. Re: ORA-01555: snapshot too old
              sb92075
              The application itself its an 3rd party closed application.
              Then we can not assist & you need to talk to application vendor.
              • 4. Re: ORA-01555: snapshot too old
                Yoav
                Hi,
                Thanks but its seems that the number of optimal seconds are far from being valid
                ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]                                                        OPTIMAL UNDO RETENTION [Sec]
                ------------------------ --------------------------------------------------------------------------- ----------------------------
                                   27000 10800                                                                                            2519563
                • 5. Re: ORA-01555: snapshot too old
                  CKPT
                  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

                  Thanks.
                  • 6. Re: ORA-01555: snapshot too old
                    829885
                    Then you have increase your current undo retention to 2519563... Undo tablespace with a size of 27 GB and %99 availability does not solve ORA-01555 error unless you have an optimal undo retention size depending your transaction needs.
                    • 7. Re: ORA-01555: snapshot too old
                      Yoav
                      Hi,
                      SQL> show parameter undo
                      
                      NAME                                 TYPE                              VALUE
                      ------------------------------------ --------------------------------- ------------------------------
                      undo_management                      string                            AUTO
                      undo_retention                       integer                           10800
                      undo_suppress_errors                 boolean                           FALSE
                      undo_tablespace                      string                            UNDOTBS1
                      SQL> select max(maxquerylen) from v$undostat;
                      
                      MAX(MAXQUERYLEN)
                      ----------------
                                  1271
                      As you can see the maxquerylen is about 20 minute while the undo retention is set to 3 hour.

                      Thanks
                      • 8. Re: ORA-01555: snapshot too old
                        Taral
                        What is the output for this

                        select * from (
                        select begin_time, txncount, maxquerylen,ssolderrcnt, nospaceerrcnt ,unxpstealcnt ,expstealcnt from v$undostat order by begin_time desc ) where rownum <=20
                        • 9. Re: ORA-01555: snapshot too old
                          CKPT
                          as per your output, you need to increase undo retention, but need to check any tuning need on the query ?
                          • 10. Re: ORA-01555: snapshot too old
                            Yoav
                            Hi,
                            Unfortunately i cant change any statment in this 3rd party application.

                            You wrote:
                             
                            as per your output, you need to increase undo retention 
                            But my output shows that the undo_retention is set to 10800 sec , while the max query length was just 1200 sec.
                            And currently as you can see bellow its just 292 seconds.
                            Why do you think i should increase the undo_retention size ?

                             
                            SQL> select * from ( 
                              2  select begin_time, txncount, maxquerylen,ssolderrcnt, nospaceerrcnt ,unxpstealcnt ,expstealcnt 
                              3  from v$undostat 
                              4  order by begin_time desc ) 
                              5  where rownum <=20; 
                            
                            BEGIN_TIME        TXNCOUNT MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT UNXPSTEALCNT EXPSTEALCNT 
                            --------------- ---------- ----------- ----------- ------------- ------------ ----------- 
                            12-JAN-11           187642          12           0             0            0           0 
                            12-JAN-11           185983          15           0             0            0           0 
                            12-JAN-11           184487           5           0             0            0           0 
                            12-JAN-11           178631          13           0             0            0           0 
                            12-JAN-11           177040          13           0             0            0           0 
                            12-JAN-11           171938           4           0             0            0           0 
                            12-JAN-11           169354          13           0             0            0           0 
                            12-JAN-11           167101          11           0             0            0           0 
                            12-JAN-11           165556           5           0             0            0           0 
                            12-JAN-11           164072          14           0             0            0           0 
                            12-JAN-11           162265          14           0             0            0           0 
                            12-JAN-11           159769           4           0             0            0           0 
                            12-JAN-11           157408          14           0             0            0           0 
                            12-JAN-11           155380         292           0             0            0           0 
                            12-JAN-11           153208           4           0             0            0           0 
                            12-JAN-11           151554          14           0             0            0           0 
                            12-JAN-11           149466           5           0             0            0           0 
                            12-JAN-11           135778          13           0             0            0           0 
                            12-JAN-11           134289          13           0             0            0           0 
                            12-JAN-11           132888           5           0             0            0           0 
                            
                            20 rows selected. 
                            • 11. Re: ORA-01555: snapshot too old
                              Taral
                              Ok we just produced 20 rows. But don't know look at all the results and see the timeframe when you have received this error and look if any of this columns are non-zero

                              ssolderrcnt, nospaceerrcnt ,unxpstealcnt ,expstealcnt
                              • 12. Re: ORA-01555: snapshot too old
                                Raipur
                                Hi ,
                                What is your database version if it is higher then 9i you can enable undo Retention Guarantee.

                                Regards,
                                Abhi
                                • 13. Re: ORA-01555: snapshot too old
                                  Hemant K Chitale
                                  What version are you running ? Earlier 9.2.0.x patchset versions had bugs where ORA-1555s would be caused by a timer error.

                                  Hemant K Chitale