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 1000+ times
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
Is this answer out of date? If it is, please let us know via a Comment