DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: UNDO space used

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    UNDO space used

    I know that my UNDO size will not shrink as long as there are active
    transactions within the tablespace.

    Is there a query I can use to see how long a transaction as been inactive
    so it can be released from the UNDO tablespace.

    Here is my setup for UNDO:

    *.undo_management='AUTO'
    *.undo_retention=5400
    *.undo_tablespace='UNDOTBS01

    What I am trying to determine is at what time/(s) I will get back space
    in the UNDO TS.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    More along the lines of "expired" is what you're looking for, and you may be able to swag the release time based on the retention period.

    http://download.oracle.com/docs/cd/E...htm#REFRN23299

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by BeefStu View Post
    *.undo_management='AUTO'
    *.undo_retention=5400
    *.undo_tablespace='UNDOTBS01

    What I am trying to determine is at what time/(s) I will get back space in the UNDO TS.
    Exactly after undo_retention for specific transaction completes, in this case 5400 seconds.

    Monitoring of space size on UNDOTBS should be based on HWM for desired undo_retention - nothing else matters.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Jul 2006
    Posts
    195
    I have a count of 46,126 that are "UNEXPIRED" and a count of "22" that are marked "EXPIRED" and a count of "0" transactions that are active.

    I am looking at my UNDO TS and I see there is NO free space within it.

    I know that "UNEXPIRED" transactions may be over-written if needed.



    My questions are:

    1) How do I find out when the unexpired transactions will be marked expired
    or do I never have to worry about that since they will be over-written
    because there are no ACTIVE transactions?

    2) Will I ever see free space within my UNDO TS again or will it always
    be full and space will be taken when needed.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by BeefStu View Post
    1) How do I find out when the unexpired transactions will be marked expired or do I never have to worry about that since they will be over-written because there are no ACTIVE transactions?

    2) Will I ever see free space within my UNDO TS again or will it always be full and space will be taken when needed.
    Let me start with #2. Over time Oracle may decrease UNDOTBS HWM and certainly after a shudown/startup you will see UNDOTBS HWM reset.

    In regards to #1... do not over-engineer the issue. Only two things matter being a) undo_retention and b) space availability.

    Query below shows you the top five queries ordered by elapsed time, if you want to ensure no query gets ORA-01555 just set undo_retention higher than the higher number you see...
    Code:
    select * from(    
    select 
        begin_time, 
        maxquerylen, 
        undoblks 
    from 
        V$UNDOSTAT 
    where 
        begin_time > sysdate-(24/24)
    order by
        maxquerylen desc
    )where 
        rownum < 6;
    Please notice you may have bad performing queries in the system, if you see queries with too large of a elapsed time it is better to investigate further and eventually fine tune them.


    Query below shows you errors caused by lack of UNDO space...
    Code:
    select * from(
    select 
        begin_time, 
        end_time, 
        undoblks, 
        txncount, 
        maxconcurrency as maxcon,
        nospaceerrcnt  as ERRORS
    from 
        V$UNDOSTAT 
    where 
        begin_time > sysdate-(24/24)
    order by
        ERRORS desc, undoblks desc 
    )where
        rownum < 6;
    ...if you see ERRORS > 0 then additional UNDO space may be needed.

    Hope this points you in the right direction.
    Last edited by PAVB; 12-27-2010 at 05:07 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2006
    Posts
    195
    thanks for the great explanation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width