1 2 3 Previous Next 31 Replies Latest reply on Sep 30, 2015 10:36 PM by jgarry

    undo tablespace auto extenadable=yes

    Jhil

      Hello everyone

       

       

      >> Different points for same process - i am highly confused.

       

       

      1)  Docs clearly says ,  If undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION  **

      2)   If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time.

            Again, this tuned retention period can be greater than the  specified minimum retention period.  **

            source : http://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm

       

      SYS>show parameter undo;

       

      NAME                                 TYPE        VALUE

      ------------------------------------ ----------- ------------------------------

      undo_management                      string      AUTO

      undo_retention                            integer     2800

      undo_tablespace                         string      UNDOTBS1

       

      SYS>select tablespace_name , file_name , AUTOEXTENSIBLE from dba_data_files where tablespace_name like 'UNDOTBS1';

       

      TABLESPACE_NAME                FILE_NAME              AUT

      ------------------------------ ---------------------- ---

      UNDOTBS1                       /u03/undotbs1.dbf               YES

       

      Can anyone clarify , why oracle states two different points for same process ?

       

      1 )  If undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION

      2)   If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time.

        • 1. Re: undo tablespace auto extenadable=yes
          Pini Dibask

          Hello,

           

          There is no conflict between these 2 statements.

           

          The first statement means that when when the UNDO TABLESPACE is AUTOEXTEND and you set the UNDO_RETENTION to a non-zero value, then Oracle will attempt to honor the minimum retention period specified by the UNDO_RETETNTION initializtion parameter. The reason that it only attempts to honor that value is because When space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. If the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information, therefore Oracle won't be able to honor the minimum retention period specified by the UNDO_RETENTION initialization parameter.

           

          As for the second statement, Oracle automatically tunes (This feature was introduced in 10g) the retention period based on the longest running query on the system. The undo_retention will set minimal threshold but the actual TUNED_UNDORETENTION (can be obtained from V$UNDOSTAT) can be higher than the specified minimum retention period (i.e. than the UNDO_RETENTION value).


          Summary:

          When the Undo tablespace is autoextended then the UNDO_RETENTION will represent the low threshold but the actual UNDO RETENTION can be higher than the specific UNDO_RETENTION parameter and you can view the actual retention period by query the TUNED_UNDORETENTION column in V$UNDOSTAT.

          Oracle will try to honor the minimum threshold but there's no gurantee that Oracle will be able to honor that (for example when the maximum size has reached and Oracle has to overwrite unexpired undo records).

          • 2. Re: undo tablespace auto extenadable=yes
            Pini Dibask

            And another comment, if you'd like that Oracle will honor the low threshold of the UNDO_RETENTION parameter in any price, you can specify RETENTION GUARANTEE (in the CREATE UNDO TABLESPACE or ALTER TABLESPACE commands) and then Oracle never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

            Bear in mind that this can cause multiple DML operations to fail. This feauture is disabled by default.

            • 3. Re: undo tablespace auto extenadable=yes
              Jhil

              SYS>show parameter undo_retention;

               

              NAME                                 TYPE        VALUE

              ------------------------------------ ----------- ------------------------------

              undo_retention                       integer     2800

               


              @ pini wrote


              The undo_retention will set minimal threshold

              I set undo_retention=2800  but  you told  undo_retention will set minimal threshold

               

              Where we can see this  minimal threshold value ?



              • 4. Re: undo tablespace auto extenadable=yes
                Jhil

                Hi ,

                 

                >> please have a look on this

                 

                select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,

                to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention

                from v$undostat order by end_time;

                 

                27-SEP-15 19:32 27-SEP-15 19:42                2800

                27-SEP-15 19:42 27-SEP-15 19:52                2800

                27-SEP-15 19:52 27-SEP-15 20:02                1150

                27-SEP-15 20:02 27-SEP-15 20:12                1709

                27-SEP-15 20:12 27-SEP-15 20:16                1820

                 

                 

                > SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;

                 

                BEST_RETENTION

                --------------------

                          1560

                 

                Which is most best one ?

                Both queries are managed by oracle only - but two different answers

                • 5. Re: undo tablespace auto extenadable=yes
                  Pini Dibask

                  Hi,

                   

                  The undo_retention parameter specifies the minimum undo period that Oracle attempts to honor, therefore the actual period might be even higher to lower.

                  This is why when you queried tuned_undoretention from V$UNDOSTAT in some intervals it was identical to the UNDO_RETENTION value (2800) and in some other intervals it was lower than the UNDO_RETENTION value.

                   

                  As for dbms_undo_adv.best_possible_retention, this function returns the best undo_retention based on the current settings while the tuned_undoretention in V$UNDOSTAT will tell you the actual period for which the undo retained based in every 10-mintue interval.

                  • 6. Re: undo tablespace auto extenadable=yes
                    >> Different points for same process - i am highly confused.

                     

                     

                    1)  Docs clearly says ,  If undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION  **

                    2)   If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time.

                          Again, this tuned retention period can be greater than the  specified minimum retention period.  **

                          source : http://docs.oracle.com/cd/B19306_01/server.102/b14231/undo.htm

                     

                    Those are NOT 'different points' - that is why they are discussed in DIFFERENT sections of that doc

                     

                    #1  is about USING the retention period

                    #2 is about SETTING the retention period

                     

                    #1 is discussed in the section about how the period is used

                     

                    #2 is discussed in the section about how the period is set - both with and without 'automatic' extension

                     

                    What PROBLEM are you trying to understand?

                    • 7. Re: undo tablespace auto extenadable=yes
                      Jhil

                      Hi Pini

                       

                      @ Pini wrote

                      Some intervals it was identical to the UNDO_RETENTION value (2800) and in some other intervals it was lower than the UNDO_RETENTION value.

                       

                      I agree.

                       

                       

                      >> on session 1 :

                      update t1 set string_val='ORACLEBDATABASE' where no >= 1 and no <= 400000;

                      ..

                       

                      >> on session 2 :

                      update t1 set string_val='ORACLEBDATABASE' where no >= 400001 and no <= 550000;

                      ..

                       

                      SYS> select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,

                      to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention

                      from v$undostat order by end_time;

                       

                      28-SEP-15 10:14 28-SEP-15 10:24                2800

                      28-SEP-15 10:24 28-SEP-15 10:34                2800

                      28-SEP-15 10:34 28-SEP-15 10:44                2800

                      28-SEP-15 10:44 28-SEP-15 10:54                1169

                      28-SEP-15 10:54 28-SEP-15 11:03                1030

                       

                       

                      SYS>/

                       

                      BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

                      --------------- --------------- -------------------

                      28-SEP-15 10:14 28-SEP-15 10:24                2800

                      28-SEP-15 10:24 28-SEP-15 10:34                2800

                      28-SEP-15 10:34 28-SEP-15 10:44                2800

                      28-SEP-15 10:44 28-SEP-15 10:54                1169

                      28-SEP-15 10:54 28-SEP-15 11:04                1095

                      28-SEP-15 11:04 28-SEP-15 11:05                1095

                       

                       

                      >> I am updating huge number of records , if undo is automated ,

                       

                      1)  In which case oracle can increase TUNED_UNDORETENTION  more  than 2800 ?

                      2)  In which case oracle can decrease TUNED_UNDORETENTION  less  than 2800 ?

                       

                       

                      > select status,

                        round(sum_bytes / (1024*1024), 0) as MB,

                        round((sum_bytes / undo_size) * 100, 0) as PERC

                      from

                      (

                        select status, sum(bytes) sum_bytes

                        from dba_undo_extents

                        group by status

                      ),

                      (

                        select sum(a.bytes) undo_size

                        from dba_tablespaces c

                          join v$tablespace b on b.name = c.tablespace_name

                          join v$datafile a on a.ts# = b.ts#

                        where c.contents = 'UNDO'

                          and c.status = 'ONLINE'

                      );

                       

                      STATUS                                 MB       PERC

                      ------------------------------ ---------- ----------

                      ACTIVE                               1040         67

                      EXPIRED                                27          2

                      UNEXPIRED                             418         27

                       

                      Thank you pini and rp .

                       

                      EDITED :

                       

                      Increasing/decreasing  of  tuned_undoretention value is based on  system workload  ??


                      • 8. Re: undo tablespace auto extenadable=yes
                        Pini Dibask

                        Hello,

                         

                        As long as you generate more undo (in your example - update more and more records) then more undo extents will be active (i.e. required to perform rollback) and once you'll execute a commit, these undo extents will be UNXPIRED and the TUNED_UNDORETENTION could be less than 2800 because Oracle can't guarantee the ~47 minutes retention period (2800/60) .

                        Once the retention period of ~47 minutes has passed these undo extents will be EXPIRED and can be reused and then you'll see that the value of TUNED_UNDORETENTION will be higher because there are more EXPIRED undo extents which can be reused.


                        Summary - The value of TUNED_UNDORETENTION will dynamically change based on the Undo Tablespace SIZE & MAX_SIZE and also based on the system workload - when you generate more undo records (for example, many updates), and the percent of ACTIVE or UNEXPIRED undo extents bceomes higher and the percent of EXPIRED percent becomes lower then it's you should expect that the value of TUNED_UNDORETENTION will decrease.

                        1 person found this helpful
                        • 9. Re: Re: undo tablespace auto extenadable=yes
                          Jhil

                          Hi pini


                          I want to know one thing. When space becomes low , an oracle database begins to overwrite expired undo.

                          Suppose all expired undo is overwritten, still undo tablespace has no space for new transactions then the database may begin to overwrite unexpired undo information


                          This is auxo extendable undo tablespace

                           

                          but following output shows , some times oracle  overwrites  unexpired undo data.  It is not going to  use completely use expired undo.

                           

                          what do you think pini ?

                           



                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                546         35

                          EXPIRED                                25          2

                          UNEXPIRED                             914         59

                           

                          SYS>/

                           

                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                553         36

                          EXPIRED                                25          2

                          UNEXPIRED                             906         58

                          SYS>/

                           

                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                589         38

                          EXPIRED                                25          2

                          UNEXPIRED                             868         56

                           

                          SYS>/

                           

                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                596         38

                          EXPIRED                                25          2

                          UNEXPIRED                             864         56

                           

                           

                          SYS>/

                           

                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                596         38

                          EXPIRED                                25          2

                          UNEXPIRED                             864         56

                           

                          SYS>/

                           

                          STATUS                                 MB       PERC

                          ------------------------------ ---------- ----------

                          ACTIVE                                871         56

                          EXPIRED                                25          2

                          UNEXPIRED                             557         36

                          • 10. Re: undo tablespace auto extenadable=yes
                            Pini Dibask

                            Hi,

                            In the output that you've provided you can see that the size of the EXPIRED undo extents is 25MB in all the intervals and that the size of the ACTIVE undo extents changes probably due to running transactions that generate undo records. As for the UNEXPIRED undo extents - undo extents are UNEXPIRED as long as they as still under the retention period defined in the UNDO_RETENTION initialization parameter.

                            This is an behaviour - when long running transactions generate more UNDO records the SIZE of the ACTIVE UNDO Extents will increase and the size of UNEXPIRED undo extents may decrease as some of these UNDO EXTENTS are no long under the retention period defined in the UNDO_RETENTION initialization parameter.

                            1 person found this helpful
                            • 11. Re: undo tablespace auto extenadable=yes
                              Mark D Powell

                              user955912. in relation to your first reference if you use a non-extendable undo tablespace Oracle will ignore undo_retention and will attempt to hold onto all undo as long as it can based on demand for undo verse provided resource (undo tablespace size).  This is my recommendation for how the undo tablespace should be defined.  Allocate a fixed size based on what you can afford to provide then monitor to see how you are using the allocated resource.

                              - -

                              HTH -- Mark D Powell --

                              • 12. Re: undo tablespace auto extenadable=yes
                                Jhil

                                Hi pini ,

                                 

                                You told specific reason was Some of these UNDO EXTENTS are no long under the retention period defined in the UNDO_RETENTION initialization parameter.

                                Did you refer any doc id ? How do you say ?

                                I have noticed . Sum of Total mb  =  96% only .  4%  is missing

                                 

                                STATUS                                 MB       PERC

                                ------------------------------ ---------- ----------

                                ACTIVE                                495         32

                                EXPIRED                               927         60

                                UNEXPIRED                            57          4

                                 

                                 

                                 

                                Any idea  to deallocate  expired undo segements ?

                                 

                                I  know , oracle only can do this. or  we can resize datafile.

                                • 13. Re: undo tablespace auto extenadable=yes
                                  Jhil

                                  Hi Powell

                                   

                                  Already i checked with  fixed size undo tablespace

                                   

                                   

                                  SYS> select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,

                                  to_char(end_time,'DD-MON-RR HH24:MI') end_time, tuned_undoretention

                                  from v$undostat order by end_time;

                                   

                                  BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

                                  --------------- --------------- -------------------

                                  26-SEP-15 12:02 26-SEP-15 12:04              14244

                                   

                                  SYS> /

                                   

                                  BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

                                  --------------- --------------- ------------------- 

                                  26-SEP-15 12:02 26-SEP-15 12:04              28488

                                   

                                   

                                  I noticed ,  TUNED_UNDORETENTION valuse gradually increased.

                                  • 14. Re: undo tablespace auto extenadable=yes
                                    Pini Dibask

                                    Hi,

                                     

                                    As per Oracle official documentation:

                                    After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

                                    When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired and its space is available to be overwritten by new transactions. Old undo information with an age that is less than the current undo retention period is said to be unexpired and is retained for consistent read and Oracle Flashback operations.

                                    See: http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN10180

                                    1 2 3 Previous Next