I searched my archive to see if I could identify the client and find the report
- and I found both. The report was dated March 2009 and the problem related to
automatic undo management (particularly a bug with stealing space from offline
undo segments, aided and abetted by the fact that there were 1,200 to 1,500
undo segments created and brought online when the system was busy), and the
necessary presence of a couple of long running batch "reports" at low
concurrency finishing a little before the early morning rush of 400 users
hammering in lots of short transactions.
Here's a coincidence (though not much of one, really):
My suggested workaround - have a second undo tablespace available, watch for
the "tuned undo retention" going high (it sent up from 15 minutes to about 3
hours) and switch undo tablespaces when it happened - then drop and recreate
the problem tablespace a few hours later in anticipation of the next batch. The
long running transaction caused the problem, the morning rush suffered as a
consequence.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Mark W. Farnham [mwf@xxxxxxxx]
Sent: 24 August 2016 14:50
To: Jonathan Lewis; christopherdtaylor1994@xxxxxxxxx; 'ORACLE-L'
Subject: RE: Are any of you guys managing rollback segments manually?
If you wanted to isolate the activity of a large monolith to not your usual
rollback segment you can use coordinated sessions to: 1) from dba session 1
alter the undo tablespace to the special one, 2) from session 2 start the
monolith intended to have its activity isolated, 3) from dba session 1 alter
the undo tablespace to the usual one.
s1: ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;
s2: update …..
s1: ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_01;
s2: commit;
Notice that some additional transactions may sneak into the “special” undo
tablespace, but all the activity of the monolith will be in the “special”
tablespace, and intentional injection of race cases of miscellaneous small
transactions didn’t defeat the purpose. But unlike trying to isolate
transactions in the old manual mode, you can close the window when new
transactions are allowed to start in that undo tablespace. If a small
transaction does sneak in that is never committed or rolled back, you will not
be able to take undotbs_02 off line until that transaction is killed, in case
you were thinking about that.
When I ran a demo of this at OOW when automatic undo was brand new I was
scolded and told that is not supported. As with the invention of physical
standby databases, I asked “Explain to me very carefully where it says anything
I have just done is not a fully supported feature exactly as documented in your
manuals.” Silence, followed by what I can best describe as blubbering “but we
didn’t test that and didn’t intend that it be used that way.”
Nothing ever broke that I know of, although we did not test and did not allow
the “special” undo tablespaces to be taken offline prior to completion of
backing up all recoverable tablespaces.
I do not know and have not tested whether later releases can be caused to fail
in any way by this practice. Several major changes to undo have taken place
since I last tested this. In the day of spinning rust having a monolith update
stream undo to an independently operating LUN was definitely an improvement to
throughput (as long as you also kept redo out of the way).
I question whether this slimy trick has value on today’s hardware, although I
suppose it might untangle something somewhere and avoid some contention
somewhere above the i/o to persistent media layer.
It is probably worth a test I don’t have time to do right now. It may well go
splat, but it may well do something pretty cool.
Your mileage may vary. From the manuals I believe this is still legal, but I
doubt trying to use it in this manner would make Oracle happy. Unless of course
it speeds something up dramatically and wins a benchmark.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Tuesday, August 23, 2016 10:43 AM
To: christopherdtaylor1994@xxxxxxxxx; ORACLE-L
Subject: RE: Are any of you guys managing rollback segments manually?
I recall seeing a case in the early days of automatic undo where a very big
serial DML batch had a problem because due to the cost of stealing a very large
number of unexpired undo extents while it ran; and then everything running at
9:00 having a problem because all the small(ish) concurrent DML stuff from the
end users had to steal the extents back - and that caused a massive contention
problem for a few minutes.
If someone had run into that problem they might have decided to run with manual
rollback and specify a particular segment in a separate tablespace for the
batch job as a workaround.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Chris Taylor
[christopherdtaylor1994@xxxxxxxxx]
Sent: 23 August 2016 15:03
To: ORACLE-L
Subject: Are any of you guys managing rollback segments manually?
I was having a discussion with a guy about improving data load speeds and he
was asking me about creating a large rollback segment and how I would handle
it. I was a bit taken aback because I haven't manually managed rollback
segments in a long, long time.
So, I'm curious if this is still "a thing"? Managing rollback segments
manually especially in regard to large data loads?
If so, I'd like to understand why managing rollback segments manually makes
more sense than using a dedicated UNDO tablespace and letting Oracle manage the
rollback segments?
I've been googling some this morning and I see some bits and pieces here and
there but I don't see any use cases where managing rollback segments manually
makes sense to me. (So I'm trying to put 2+2 together in my own mind)
Regards,
Chris
