Hi Jeff,
Stealing extents is normal when there is no free space available to grow the active
undo segment.
If expired extents are getting stolen, I would not worry too much about adding more
space to the
undo tablespace, but monitor how much undo space the segment takes up.
If unexpired
:
pandeSubject: RE: Estimating space needed
for UNDO tablespaces
@yahoo.com
Kirti,
Thanks for this information.I've implemented AUM in a number of our
development
databases.One of the things I have to do is write up a monitoring policy
to hand
to our contracted production DBAs -- guidelines on how to address certain
scenarios
and so forth -- otherwise, they will
That is a good place to start. You might consider adding a little if you
have many concurrent transactions or want to increase the undo_retention
to a high number. Once you are using AUM, keep a close eye on
v$undostat, though there are some known issues with it not populating
properly, keep an
Thanks for the reply Dan.
Would you suggest setting UNDO_RETENTION to roughly the length of time of
the longest
running job in the database? For example, in our DW, our BI analysts tell
me that their
longest batch run is about 1 hr 45 minutes. My uneducated guess is to
accordingly
set
That sounds very reasonable. You can check that number against the values in
v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the
space is needed by another segment, it may be taken even if the expire time
has not been reached.
Daniel
Thomas Jeff wrote:
Thanks for the
You can run following query to get an idea of undo generation rate and max query
length:
SELECT
to_char(min(begin_time),'MM/DD/ HH24:MI:SS') Begin Time,
to_char(max(end_time),'MM/DD/ HH24:MI:SS') End Time,
(max(end_time)-min(begin_time))*24*60*60 Seconds,