Re: UNDO poll - was undo segments vs. rbs

From: Kirtikumar Deshpande <kedeshpande_at_yahoo.com>
Date: Wed, 19 May 2004 06:04:07 -0700 (PDT)
Message-ID: <20040519130407.78381.qmail@web50110.mail.yahoo.com>

In a few of my databases I ended up using less (20-25% less) disk space for the undo tablespace. I started with the same amount of space that was allocated to the RBU method. After monitoring undo generation and maxquerylen, I found that the default value of 15 minutes (900 seco) for UNDO_RETENTION was way too large for our requirements (we don't care about FBQ). Setting it to just 60 seconds was sufficient.

Also, IMO, Oracle's suggestion to set UNDO_RETENTION to max(v$undostat.maxquerylen) is more of a CYA type. This will tend to increase the size of the undo tablespace. One must monitor undo generation, and also, what tables the long running queries are accessing during the DML operations to deal with ORA-1555, not just blindly follow Oracle's suggestion. You will need more disk space to let Oracle deal with those.

Regards,

• Kirti
  • Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
    >
    > I'd be interested in a quick poll.
    >
    > Your case is the first time I've heard of undo space
    > being less than rollback space. Every other comment
    > I've heard has been along the lines of
    > why has my 1GB rbs t/s grown to 12GB when
    > I switched to undo t/s.
    >
    > So - out of three options:
    > Undo much bigger
    > Undo much smaller
    > No significant change noticed
    >
    > Would people like to contribute.
    >
    > I've changed the subject heading to isolate the poll.
    >
    > Regards
    >
    > Jonathan Lewis
    >
    > http://www.jlcomp.demon.co.uk
    >
    > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
    > The Co-operative Oracle Users' FAQ
    >
    > http://www.jlcomp.demon.co.uk/seminar.html
    > Optimising Oracle Seminar - schedule updated May 1st
    >
    >
    

    ---

Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 19 2004 - 08:00:56 CDT