Re: undo tablespace keeps growing, why can't I reclaim space?

Sybrand Bakker schreef:

> First of all: I don't agree with your analysis the undo segments are
> the cause of your problem.

If you've read carefully, you would conclude that is not my analysis. I'm looking for an explanation for the excessive use of undo space. When you take a look at the statspack snippets, it's pretty hard to deny a problem with IO.

                (snapshot taken of a 2hr period)

                                                     Total Wait Avgwait
  Waits
Event                               Waits   Timeouts   Time (s)   (ms)

   /txn

---------------------------- ------------ ---------- ---------- ------
--------
log file sync                   5,050,014      1,063      4,994      1
3,942.2
db file parallel write             20,083          0        737     37
   15.7
db file sequential read           200,944          0        626      3
  156.9
control file parallel write        11,128          0        523     47
    8.7
log file parallel write         5,055,040          0        387      0
3,946.2
db file scattered read             35,818          0        249      7
   28.0
async disk IO                      40,930          0        225      5
   32.0
direct path read                   17,884          0         85      5

   14.0

Given the number of commits (1277) you must at least share the part of my analysis that there *is* an IO problem.

> Might be undo segment, but might be not. Basically your claim is
> unsubstantiated.

What claim? My only claim is that the batch consumes an unacceptable amount of undo AND that there is an IO problem. I wonder whether those symptoms *could* be related. I didn't say they are related.

> Thirdly: undo segments were designed to work like temporary segments.
> Ie they never shrink. This is to be expected as Oracle and several
> Oracle gurus have been advising on not using the optimal clause of the
> rollback segment for many years, for the reason extent allocation and
> deallocation is an expensive operation.
Have you ever read "practical Oracle 8i", by Jonathan Lewis? Page 146 clearly states that you should aim to keep the optimal size as small as possible.
But that's off-topic. BTW: automatic undo management won't let you use the optimal clause.

 As disk is cheap nowadays, the
> space allocated usually shouldn't be a problem.
Space IS a problem, because undo tablespaces need backing up.

> Fourthly: your transaction may show an inefficient execution path, or
> (implicitly) modify many, many indexes. As you are pretty scarce on
> details, crystall balls would be needed to determine why you
> transaction is 40G. Oracle however, will online additional undo
> segments as needed, and those actions are being logged in the alert
> log.

Only difference is the machine and the IO characteristics. Database layout, tables and indexes are all the same, so I don't see the need for crystal balls. I've never seen a transaction span multiple undo segments.

> Your query about the relationship between v$rollstat and dba_segments
> can, as you didn't provide any specifics, not be answered.
When I query v$rollstat and Oracle reports 3g from column "rssize", then why does dba_segments report 41g as the size of that particular segment? I used v$rollstat.usn to get to dba_rollback_segs.segment_name to get to dba_segments.bytes.

> Finally: my impression is you set up undo segments and forget about
> them. Any attempt to try to be 'smarter' than Oracle is probably
> likely to succeed in some circumstances, and fail in other.
Assumption is the mother of all screwups...

By now, it must be clear to you that I'm not trying to solve a problem, I'm trying to obtain knowledge about extension and shrinkage of automatically managed undo segments. Under what circumstances does Oracle postpone or cancel freeing space from those segments?

HTH,
Hans Received on Mon Aug 15 2005 - 03:37:55 CDT