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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, August 23, 2016 10:43 AM
To: christopherdtaylor1994_at_gmail.com; 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
_at_jloracle

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Chris Taylor [christopherdtaylor1994_at_gmail.com] 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  

--
http://www.freelists.org/webpage/oracle-l