RE: Undo Tablespace Error
Date: Wed, 8 Sep 2021 08:36:43 -0400
Message-ID: <14ea01d7a4ae$2e529db0$8af7d910$_at_rsiz.com>
Excellent!
Auto this and auto that are useful for academic systems (for example student class accounts, live with auto), “little” systems (compared to the resources available), and stress test systems (to discover what you need.)
In serious production, it is best if you have long since determined the optimal sizes.
That goes not only for UNDO, but also for the dynamic memory size allocations, which have a tendency at steady state to oscillate at a few granules, which cleverly discards the memory of sga likely containing the most recently parsed sql on alternate oscillations. It may be the case that you want to manually resize some instances for differing workshift requirements, but that is all something you should know and track versus organic growth and added applications for production.
All of this exercise and planning comes under cost control, and the work (except for practicing so that getting it right when you really need it is not your first experience) should be avoided unless you need it. When AUTO is good enough it is cheapest total cost of ownership unless you already have a set of standard sizes developed.
Good stuff, John and Hemant!
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale
Sent: Tuesday, September 07, 2021 10:45 PM
To: loknath.73_at_gmail.com
If you have AUTOEXTEND ON it is likely that the datafile had already hit the MAXSIZE.
So this would result in failure 6 in Oracle Support document " Troubleshooting ORA-30036 - Unable To Extend Undo Tablespace (Doc ID 460481.1)
Besides dba_hist_tbsp_space_usage you should also look at v$undostat
(also in earlier versions dba_hist_tbsp_space_usage had bugs in reporting Tablespace Usage for Undo tablespaces)
As John says, if you have AUTOEXTEND ON, Oracle will try to keep extending Undo retention. Setting a max size for the datafiles and then setting AUTOEXTEND OFF is better.
Hemant K Chitale
On Mon, Sep 6, 2021 at 2:59 AM Lok P <loknath.73_at_gmail.com> wrote:
Thank You John. I understand your point that with AUTOEXTENSIBLE YES oracle may tend to keep the expired UNDO longer to satisfy retention thereby increasing the UNDO tablespace to higher size. But , If we will talk specifically about the exact error here , isn't it true that with AUTOEXTENSIBLE YES, it should not have made it errored out with Ora-30036 rather it would have thrashed/increased UNDO tablespace size by its own?
Also as I see from dba_hist_tbspc_space_usage , the UNDO usage(tablespace_usedsize) was ~160GB which was equal to tablespace_size when it failed with ora-30036 , but then it increased to ~200GB after 4-5hrs, yet to see if somebody has increased it manually or it increased by itself.
On Sun, Sep 5, 2021 at 2:53 AM John Piwowar <jpiwowar_at_gmail.com> wrote:
The “autoextensible” setting on your undo data files is possibly the culprit here. Undo segments are a bit of a special case, and Oracle’s decision-making about when to reclaim expired undo can be thrown off when it thinks it has the option to extend a data file instead. It’s possible that you might still need to resize your undo to accommodate recent changes in your workload, but you’re better off with fixed-size undo datafiles, followed by an examination of your automatic undo configuration (undo_retention, etc).
Sorry about the vagueness of the response; I’m on my phone and can’t get decent references with my thumbs. :)
On Sat, Sep 4, 2021 at 1:05 PM Lok P <loknath.73_at_gmail.com> wrote:
Hello Listers, In one of our 12.1 version databases, a few days back we encountered an error- "Ora-30036 Unable to extend segment by 128 in tablespace 'UNDOTBS1'" for a delete query. This failure has never happened before. And we were trying to see if we really have to increase the size of UNDO tablespace(current size is ~200GB) as it has other impacts like making long running queries run even longer before hitting ora-01555/snapshot too old etc. Or anyway we can track and fix the long transactions which might have consumed large UNDO during that time and then we can try to break that into small transactions?
Another thing I notice in the dba_data_files is now showing autoextensible as YES, so how come this query errored out with Ora-30036/space issues?
The USED space in dba_hist_tbspc_space_usage for this UNDO tablespace is sometimes showing completely full and sometimes it's a lot empty and i believe it's because of a circular buffer.
Cc: John Piwowar; Oracle L
Subject: Re: Undo Tablespace Error
--
Regards, John P. (Typed with thumbs on a mobile device. Lowered expectations appreciated)
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 08 2021 - 14:36:43 CEST