12 Replies Latest reply on Mar 9, 2010 2:27 PM by Anil Malkai

    undo retention

      hi everyone

      i have a problem regarding undo retention

      my DB is oracle OS is HP UX11.11

      i upgraded it from

      earlier my undo used to be under 1 GB and now its never under 7GB

      i checked Undo_retention which was 10000 sec

      then tuned_autoretention which is around 70hrs

      how can i override undo retention over this parameter
        • 1. Re: undo retention
          Anil Malkai
          i am not sure why you are using so much undo retention. may be you can use undo advisory feature from enterprise manager.
          i have been using following script to optimize our undo size and undo retention.

          SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
          SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
          (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
          g.undo_block_per_sec) / (1024*1024)
          "NEEDED UNDO SIZE [MByte]"
          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))
          FROM v$undostat
          ) g
          WHERE e.name = 'undo_retention'
          AND f.name = 'db_block_size'

          hope it will help you.

          Anil Malkai
          1 person found this helpful
          • 2. Re: undo retention
            i executed this query

            it showed needed undo 3GB

            my present undo is arnd 15 GB and 13 GB is getting used

            out of which 5 GB is expired 7 GB unexpired and 1 GB active

            undo retention is 7200 sec

            why am i unable to retain undo space after 2 hrs?
            • 3. Re: undo retention
              Post the result of this query:
              select tablespace_name, retention from dba_tablespaces order by 1;
              and the DDL you are using to change undo retention.

              Also give serious consideration, given how old your database is, to patching to at least or upgrading to a currently supported version.
              • 4. Re: undo retention
                ------------------------------ -----------
                APPS_TS_MEDIA NOT APPLY
                APPS_TS_QUEUES NOT APPLY
                APPS_TS_SEED NOT APPLY
                APPS_TS_TX_DATA NOT APPLY
                APPS_TS_TX_IDX NOT APPLY
                CTXD NOT APPLY
                CUST_KPM NOT APPLY
                CUST_KPM2 NOT APPLY
                DISCOTBS NOT APPLY
                KPMTEAM NOT APPLY
                ODM NOT APPLY
                OLAP NOT APPLY
                OWAPUB NOT APPLY
                PERFSTAT NOT APPLY
                PORTAL NOT APPLY
                SYSAUX NOT APPLY
                SYSTEM NOT APPLY
                TEMP NOT APPLY

                database is upgraded to

                can there be any kind of bug for the database

                as there is a huge difference between
                maxquerylen (12000) and tuned_undoretention (345000)

                undoretention is 7200

                alter system set undo_retention=7200 scope=both;
                • 5. Re: undo retention
                  Lakmal Rajapakse
                  With 10g the undo_retention parameter auto tunes - so the parameter you set is not really relevant. So if you have autoextend on the UNDO tablespace then it will grow the datafiles.
                  1 person found this helpful
                  • 6. Re: undo retention
                    Anil Malkai
                    you can go through following link which gives the complete detail how undo works


                    Anil Malkai
                    1 person found this helpful
                    • 7. Re: undo retention
                      Lakmal Rajapakse
                      Do you have autoextend datafiles or not. If you do not have autoextend datafiles then the undo_retention value will auto-tune to fill the whole tablespace....
                      • 8. Re: undo retention
                        i have added an autoextensible datafile with maxsize , lets see what happens
                        • 9. Re: undo retention
                          Lakmal Rajapakse
                          I think it should not extend the tablespace - that is grow the datafile - because your max query length is much smaller that the tuned undo retention - but it will still attempt to fill the whole of the allocated space.
                          • 10. Re: undo retention
                            Anil Malkai
                            It is not recommended to keep undo tablespace in autoextend mode. you can estimate depend on type of transaction and set the value.

                            Anil Malkai

                            Edited by: Anil Malkai on Mar 9, 2010 6:19 AM
                            • 11. Re: undo retention
                              i knw, that is why i have added maxsize to it, i guess it wont grow beyond maxsize
                              • 12. Re: undo retention
                                Anil Malkai
                                oops sorry.. when you said maxsize for tablespace .. i thought maximum size of any tablespace (apart from big tablespace) is 32gb. :)

                                Anil Malkai