10 Replies Latest reply: Jul 23, 2009 10:36 AM by sb92075 RSS

    ORA-01555 -- Tuning for UNDO tablespace.

    Gowin_dba
      Dear DBA kings/Queens,

      I frequently getting "ORA-01555 snapshot too old error" due to particular delete statement. Which will delete about 50,000 rows daily as a batch process. Eventhough, i have reduced from 50,000 to 10,000 and 1000. No luck.
      Can anyone tell me the way to tune UNDO tablespace.
      -----------------------------------------------------------------------------
      Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
      PL/SQL Release 9.2.0.7.0 - Production
      CORE 9.2.0.7.0 ; Production
      TNS for Solaris: Version 9.2.0.7.0 - Production
      NLSRTL Version 9.2.0.7.0 - Production
      -----------------------------------------------------------------------------
      SQL> show parameter undo

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      undo_management string AUTO
      undo_retention integer 10800
      undo_suppress_errors boolean TRUE
      undo_tablespace string UNDOTBS1
      -----------------------------------------------------------------------------

      Size in GB :

      TABLESPACE_NAME TOTAL USED FREE USED_PCNT
      ------------------------------ ---------- ---------- ---------- ----------
      UNDOTBS1 13.5 ; 1.34 ; 12.17 ; 9.89
        • 1. Re: ORA-01555 -- Tuning for UNDO tablespace.
          sb92075
          Eventhough, i have reduced from 50,000 to 10,000 and 1000
          reduced what?
          Number of rows before COMMIT?

          COMMIT inside LOOP causes ORA-01555; not fix it.

          remove COMMIT from inside LOOP

          http://asktom.oracle.com

          visit above & do keyword search on ORA-01555
          • 2. Re: ORA-01555 -- Tuning for UNDO tablespace.
            Gowin_dba
            Reduced no of rows going to delete by changing "where id < 50000" to "where id < 1000"
            I didnt removed any COMMIT from inside LOOP.
            • 3. Re: ORA-01555 -- Tuning for UNDO tablespace.
              Surachart Opun
              Which will delete about 50,000 rows daily as a batch process
              Did you commit often?
              - don't commit frequently.

              - increase "undo_retention" parameter

              - reduce about running time (use execution plan to check your query) ... perhaps may create index. Or commit every 5000 rows

              - Gather stats on table

              Edited by: Surachart Opun (HunterX) on Jul 23, 2009 12:04 AM
              • 4. Re: ORA-01555 -- Tuning for UNDO tablespace.
                sb92075
                Most folks, including me, can not debug code they can not see.
                Go read ASKTOM
                • 5. Re: ORA-01555 -- Tuning for UNDO tablespace.
                  Daniel W. Fink
                  Read up on 'FETCH across COMMIT'. This might be your problem.

                  I second 'we cannot fix what we cannot see' response. Show the code, or at least a reproducible example if you are concerned about security.
                  • 6. Re: ORA-01555 -- Tuning for UNDO tablespace.
                    Gowin_dba
                    Thanks Surachart,

                    It was resolved by creating indexes.

                    Now the below query encountered the same error,

                    SELECT DISTINCT PTP.SYNTAX_KEY, PTP.USER_DB_KEY FROM PLAN_TABLE_PREV PTP, SYNTAX_PLAN_LAST SPL WHERE PTP.INSTANCE_KEY = :instance_key AND SPL.INSTANCE_KEY = PTP.INSTANCE_KEY AND SPL.SYNTAX_KEY = PTP.SYNTAX_KEY AND NOT EXISTS (SELECT PTL.UNIQUE_ROW FROM PLAN_TABLE_LAST PTL
                    WHERE PTP.INSTANCE_KEY = PTP.INSTANCE_KEY AND
                    PTL.SYNTAX_KEY = PTP.SYNTAX_KEY AND
                    PTL.UNIQUE_ROW = PTP.UNIQUE_ROW);

                    Respective error message from alert_x.log :
                    ORA-01555 caused by SQL statement below (Query Duration=16088 sec, SCN: 0x0000.8a3d9c90):
                    Thu Jul 23 04:56:08 2009
                    • 7. Re: ORA-01555 -- Tuning for UNDO tablespace.
                      Surachart Opun
                      Perhaps you may check execution plans again about your SQL Statement.

                      SQL> set autot trace explain
                      ORA-01555 caused by SQL statement below (Query Duration=16088 sec, SCN: 0x0000.8a3d9c90
                      if you have many data and get a long time:

                      Can you change "undo_retention" initialization parameter ?

                      If you use SPFILE, you can change it anyway:
                      SQL> alter system set undo_retention=xxxxxxx;

                      By the way, you queried a long time... check about it;)
                      • 8. Re: ORA-01555 -- Tuning for UNDO tablespace.
                        Santosh Kumar
                        You may consider tuning undo retention:

                        Optimal undo retention=(Actual Undo Size) */* (DB Block Size x Undo Block Per Second)

                        Similarly you may check whether the size of your Undo tablespace is optimum or not:

                        Undo Size = Undo Retention * Db Block Size * Undo Block Per Second

                        Actual Undo Size:

                        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#;

                        Undo Blocks per Second:

                        SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
                        "UNDO_BLOCK_PER_SEC"
                        FROM v$undostat;

                        DB Block Size

                        SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
                        FROM v$parameter
                        WHERE name = 'db_block_size';

                        Regards,
                        S.K.
                        • 9. Re: ORA-01555 -- Tuning for UNDO tablespace.
                          Gowin_dba
                          Santhosh,

                          Thanks for query..

                          Can you please confirm the UNITS(ie., secs or mins ) for Optimal undo retention from below,

                          I have used Actual Undo Size "in KB". Bcoz of DB Block Size interms of KB.

                          Optimal undo retention = 14158848/(8192*454.135)=*3.80586169 ?* , My guess is 3.80586169 mins.

                          Optimal undo retention=(Actual Undo Size) / (DB Block Size x Undo Block Per Second).

                          For Undo Size = Undo Retention * Db Block Size * Undo Block Per Second,
                          I got Undo Size = (10800*8192*454.135)/1024/1024/1024 = 37.4195709 GB.
                          • 10. Re: ORA-01555 -- Tuning for UNDO tablespace.
                            sb92075
                            SELECT DISTINCT ptp.syntax_key,
                                            ptp.user_db_key
                            FROM   plan_table_prev ptp,
                                   syntax_plan_last spl
                            WHERE  ptp.instance_key = :instance_key
                                   AND spl.instance_key = ptp.instance_key
                                   AND spl.syntax_key = ptp.syntax_key
                                   AND NOT EXISTS (SELECT ptl.unique_row
                                                   FROM   plan_table_last ptl
                                                   WHERE  ptp.instance_key = ptp.instance_key
                                                          AND ptl.syntax_key = ptp.syntax_key
                                                          AND ptl.unique_row = ptp.unique_row); 
                            Code above is victim.
                            ORA-01555 caused by other session doing DML & COMMIT against a table in FROM clause above