Skip to Main Content
  • Questions

  • Undo Tablespace; autoextend or not to autoextend that is the question

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Mark.

Asked: November 12, 2020 - 4:09 pm UTC

Last updated: November 13, 2020 - 3:08 am UTC

Version: 12.2.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

I am looking for guidance on whether the undo tablespace data files should have auto extend on or off. Undo Auto-tuning is enabled. I am working through expanding the size of the UNDO tablespace in order for my users to take advantage of flashback query. The current tablespace size and workload supports just under 1 Days worth of daily change and based upon the happy accidents some of my users are making it would be beneficial to expand this to a couple of weeks.

The database I have inherited is modestly sized database 20ish TBs; and the undo tablespace is 14 files (~500 GB) all fully pre-allocated out in size but autoextend was turned on. There are no ora-1555 errors within the past 12 months. If this was a traditional tablespace I would have turned autoextend off already. UNDO is a different beast and after a bunch of web research I am getting competing advice. But if everything works the way the oracle documentation says it does . .. . (grain of salt always)

It seems to me that the best method for my database is going with Auto-extend off based upon the calculations that auto tune does.
* auto tune with auto extend off will default to a retention threshold of 85% of the tablespace using undo_retention as a guide.

I plan on changing the undo_retention parameter from the default 15 minutes upwards, maybe as high as 3 weeks. I plan on increasing the size of the undo tablespace and the undo_retention parameter in alternating stages to track performance, growth and impact to backups.

Am I crazy? Is there a better approach?

Thanks!
Mark

and Connor said...

The big thing about undo retention is not *really* duration as such, but what that duration means in terms of undo generation. A predominantly read-only database could have a massive undo retention with virtually no performance impact, because most of the time, no matter how far you flash back, there is no work required. Conversely, a heavy read/write activity database could be crippled by just a few people flashing back just a few hours because each might be rolling back millions of transactions.

So undo retention of 3 weeks might not be a drama, but someone flashing back 3 weeks worth of activity could be a disaster. In particular, with reference to

"based upon the happy accidents some of my users are making it would be beneficial to expand this to a couple of weeks"

I'd strongly recommend you do some benchmarking of how long it takes to flashback query/flashback table over a 2 week period. Whilst you might have the undo available, the time it takes to do the operation might ultimately prove prohibitive.

I concur with your thoughts on autoextend, because having it on - we're going to focus on long running *queries*. So if the longest query is a couple of hours, we're not going to be particularly mindful of the fact that you want to flashback 2 weeks or more.

Some basic video demos of the cost here



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database