From: andrew_webby@hotmail.com (andrew_webby at hotmail) Newsgroups: comp.databases.oracle.server Subject: Re: Do I really need more than 1 rollback segment? Date: 12 Dec 2001 07:49:37 -0800 Organization: http://groups.google.com/ Lines: 87 Message-ID: References: <9ume07$p3$1@panix3.panix.com> <3c0f98b6$1@usenetgateway.com> <95cd51c.0112070034.1fde8cee@posting.google.com> <3c110a2a$0$29051$afc38c87@news.optusnet.com.au> <3c166064$0$559$afc38c87@news.optusnet.com.au> NNTP-Posting-Host: 195.11.221.226 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1008172177 27880 127.0.0.1 (12 Dec 2001 15:49:37 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 12 Dec 2001 15:49:37 GMT Yeah, I see what you're saying Howard. What has always been of interest to me is that my predecessor had something like 7 350m segments with 2 extents each (or something bizarre like that) and there were never any problems. I did intend to leave it that way till I understood more about it, and Johnathan's pointer was the convincer in the end... Anyway, since I recreated as a result of my statspack/ora816i upgrade, I did the following in an LMT (current results at 3:37pm included). USN file_nam Ext RS_MEG SHRINKS Wrap EXTENDS avg_shr GETS Waits ---- -------- ----- ------ ------- ----- -------- --------- ------- ----- 2 R01 21 42 0 5 0 0 10171 0 1 R02 21 42 0 6 0 0 14156 0 3 R03 21 42 0 3 0 0 9275 0 4 R04 21 42 0 3 0 0 10531 0 5 R05 21 42 0 3 0 0 7399 0 6 R06 21 42 0 3 0 0 9781 0 7 R07 21 42 0 5 0 0 8418 1 9 R08 21 42 0 2 0 0 4425 0 0 SYSTEM 61 5 0 0 0 0 225 0 As you can see, no shrinks or extends, all in 2meg initial/next, minextents 21 segments (actually, optimal is set at 50m thus protecting me a bit further). From what you've said, I can understand the cost of shrinking, but if it were a factor I'd either create the segments with larger extents, or possibly disable the optimal clause and shrink manually them at the nightly restart (probably best). Also, what has always concerned me is that although I have 8 segments defined (8 is 'big' and is offline), my max_utilization for transactions is around the 130 mark - processes is around the 150-mark. Most published wisdom I've seen says 1 segment per concurrent trans, but obviously my large number of extents is taking that slack up? From what I've read in other words, my configuration seems less than optimal/recommended, but it appears to be working fine. This despite the fact that the provided, closed, application appears to generate an horrific amount of redo ("Rollback per transaction %: 41.25", "Redo size: 5,093.90/second 11,166.27/transaction") for some reason I've yet to get to the bottom of... I do get the odd user leaving a transaction uncommitted (in fact, I get a few who open two windows and deadlock themselves), but it's yet to cause a problem other than locking. Any comments appreciated (again) AW "Howard J. Rogers" wrote in message news:<3c166064$0$559$afc38c87@news.optusnet.com.au>... > We could have a battle of the giants on our hand here. Steve Adams says > make 'em big, and who cares about a bit of wasted space: these things are > supposed to be on their own hard disk anyway. (I am paraphrasing like > crazy, natch). > > Personally, I go for the Steve Adams school of thought on this one. I can't > see any drawbacks (though I'm sure Jonathan will elaborate on the > 'additional I/O' idea) of large segments. It's the NUMBER of them that's > the worry, to avoid contention issues. > > As for sizing them "appropriately" -you must have the best behaving set of > Users I've never met. It takes just one of them to raise a transaction and > leave it uncommitted, and growth will follow as sure as day follows night. > The more "appropriate" your segments are for daily use, the more such growth > will be required in such circumstances. And that's the reason I *still* > wouldn't touch optimal with a bargepole. > > Regards > HJR > -- > ---------------------------------------------- > Resources for Oracle: http://www.hjrdba.com > ===============================