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 of my > employer or clients ** > > > > -----Original Message----- > > From: Arup Nanda [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, February 19, 2003 8:24 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: Automatic Segment Space Management > > > > > > Jay, > > > > I have been using ASSM for last five months in our Datawarehouse > > environment. Haven't had a chance to play with the OLTP side, yet. > > > > Inserts are way faster as compared to system managed extent > > allocation. I > > read Don's article on DBAZINE. However, I would like to add > > one caveat here: > > ASSM does not *eliminate* buffer busy waits as the article claims; it > > *reduces* them. BBW occur due to concurrent access to a > > buffer by more than > > one session. This will be the case regardless of number of > > freelists. While > > ASSM eliminates the freelist contention - thereby reducing > > BBW in inserts - > > it does not reduce the likelihood that more than one sessions > > will try to > > get the same block to the buffer cache simulataneously. > > > > Table drops appear a little slower in ASSM; but that could be > > wrong - I > > never timed dropping a table in the system managed mode. > > > > HTH. > > > > Arup Nanda > > > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Wednesday, February 19, 2003 9:33 AM > > > > > > > I'm continuing to introduce myself to 9i. I've been reading about > > Automatic Segment Space Management, and I just wondered if > > anybody had any > > positive/negative experiences with it. I got some good info at: > > > > > > http://www.dbazine.com/burleson11.html > > > > > > > > > Thank you, > > > > > > > > > Jay Hostetter > > > Oracle DBA > > > D. & E. Communications > > > Ephrata, PA USA > > > > > > > > > > > > **DISCLAIMER > > > This e-mail message and any files transmitted with it are > > intended for the > > use of the individual or entity to which they are addressed > > and may contain > > information that is privileged, proprietary and confidential. > > If you are not > > the intended recipient, you may not use, copy or disclose to > > anyone the > > message or any information contained in the message. If you > > have received > > this communication in error, please notify the sender and > > delete this e-mail > > message. The contents do not represent the opinion of D&E > > except to the > > extent that it relates to their official business. > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jay Hostetter > > > 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). > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Arup Nanda > > 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). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: John Kanagaraj > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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).
