Cary: I guess the parameter just puts a softlimit of number of CR Blocks per DBA. But I have seen more number of CR copies (10+ CR copies per DBA) many times. But then I was using Relative File# in the X$BH while querying (though the chances of same block# in the different files becoming hot is very rare), which I realized later.
Any thoughts Cary? KG --- Cary Millsap <[EMAIL PROTECTED]> wrote: > I've received additional insight from Stephan Haisley of Oracle. > Bottom-line, it sounds like the real trade-off is that if a larger > number of CR versions could be left on a chain, then the number of > undo > operations required to fulfill a given query might be reduced, but at > the expense of longer chain searches. > > From Stephan: > > > "Cary, > > I think you are forgetting the fact that updates can only occur to a > CURRENT buffer. There can only be ONE current buffer of any block in > the > buffer cache. All row updates will occur to the same current buffer. > Therefore, it is not related to the max. number of CR buffers > permitted > per datablock. A CR block can not be used for row updates. Sure the > block is updated during application of undo to make it consistent of > a > particular SCN, but this is not the same as a DML row update that > must > be applied to the CURRENT version of the buffer. > > Clone buffers (as Cary mentioned them) are mainly created in one of > two > cases. When a block is required for CR purposes (closest buffer to > required SCN is found, cloned and then undo is applied). The second > common occasion is when you want to update (DML) a buffer, and there > is > a current buffer already in cache. If all users or waiters are for CR > purposes only (NO DMLs) according to the users state objects, the > buffer > will be cloned, switching the clone to the CURRENT buffer, and > leaving > the existing buffer as a CR buffer. > > Someone has stated already on this thread that, if there are multiple > updates in the same block to different rows, the number ITL entries > will > be the limiting concurrency factor. And could also cause some BBW > during > the actual block update causing an incompatible mode BBW even between > the processes actually applying their changes. > > Additions and corrections are welcome. > > I hope this helps, > Stephan" > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 25-27 London > > > -----Original Message----- > Millsap > Sent: Thursday, February 20, 2003 12:24 AM > To: Multiple recipients of list ORACLE-L > > Jonathan or Steve or Stephan will likely provide a better answer to > this > than I will, but I'll add this food for thought: > > I think that if (1) six CR versions of one data block address already > reside on a given cache buffers chain, and (2) at least one of them > is > not pinned, and (3) a request for a 7th distinct CR version of the > block > were to come along (i.e., same block but different SCN), then I think > that _db_block_max_cr_dba=6 simply means that one of the existing > (unpinned) CR versions will get expelled before the new CR > construction > takes place. The resulting desired behavior is thus that the > construction of the new clone will not increase the original length > of > the cache buffers chain. > > I believe the trade-off is this: If, after this occurred, some query > called were to Q request the CR version that was expelled (that is, > if > the expelled CR version's SCN suited the query's SCN), then the > Oracle > kernel would have to execute all the instructions required to > reconstruct that CR version again (reading undo blocks using the ITL > as > a guide), instead of simply finding the right version's buffer header > already on the chain (had the setting been 7, then the requested CR > version wouldn't have been expelled). This whole operation would of > course cause the expulsion of some other unpinned CR version of the > same > block (keeping the number of CR versions of the block to 6), so that > the > chain length would not increase. Had the parameter setting been 7, > then > Q's new CR request could have been fulfilled more cheaply, but at the > expense of incurring longer average cache buffers chain lengths, > which > would cause incrementally longer cache buffers chain scan times, > which > would cause incrementally worse contention for the cache buffers > chains > latch on the relevant cache buffers chain. > > I think producing 7+ concurrent updates of a block will test, as Arup > notes, only the kernel's ITL management prowess, not the case you're > interested in. > > I'll step back timidly now, in anticipation of what dog trainers call > a > "firm correction." :) > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - RMOUG Training Days 2003, Mar 5-6 Denver > - Hotsos Clinic 101, Mar 25-27 London > > > -----Original Message----- > Sent: Wednesday, February 19, 2003 4:03 PM > To: Multiple recipients of list ORACLE-L > > Gee, John, I was not aware of this underscore parameter. In my 9.2 > database > it's 6, just as yours. > > I did my test using upto three concurrent tranactions; guess I'll > need > to > test with 7 or more. However, even if 7 concurrent transactions > update > the > block's rows, and the limit is 6, then the waits should be based in > ITL > (Interested Trasnaction List) Waits, not BBW. since this is not due > to a > session not being able to get a particular buffer to the SGA, rather > the > lack of resources to get the CR copy of a buffer. > > Has anyone done this test? I'll certainly take it up later to build > up > on my > upcoming article on ITL Waits. > > Regards, > > Arup > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, February 19, 2003 2:45 PM > > > > Arup, > > > > Just picking up the thread on the BBWs. (Btw, I asked this question > in > this > > list - never got an answer!) The following undocumented parameter > limits > the > > numbe of CR copies in the Block buffers. > > > > Name Value > > --------------------------------------------- > ---------------------------- > -- > > Description > > > ------------------------------------------------------------------------ > -- > -- > > --- > > _db_block_max_cr_dba 6 > > Maximum Allowed Number of CR buffers per dba > > > > What if there are more than 6 concurrent update requests for the > same > block. > > Would that not result in BBW? > > > > John Kanagaraj > > Oracle Applications DBA > > DBSoft Inc > > (W): 408-970-7002 > > > > Disappointment is inevitable, but Discouragement is optional! > > > > ** The opinions and statements above are entirely my own and not > those > === message truncated === ===== Have a nice day !! ------------------------------------------------------------ Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
