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 <cary.millsap_at_hotsos.com> 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" <ORACLE-L_at_fatcity.com>
> 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: kaygopal_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Feb 20 2003 - 05:14:05 CST
