Re: Automatic Segment Space Management

The _db_block_max_cr_dba parameter was put in to fix this problem with massive number of CR copies (segment header blocks mostly). I remember seeing a test case that had 1500+ CR copies of the segment header block. So the fix was to limit the number of CR copies. The parameter _db_block_max_cr_dba had initially a default value of 42 (really). And worked perfectly, but it was brok in Parallel Server, the reason for it not working was very funny.

While scanning the hash chain for the right (tsn, rdba) the CR code may already stop if it finds the best fit and never scan all the buffers and there it can't enforce the limit of 6. Another reason could be that the buffers are pinned (in use), but they should be flushed out later if the same buffer hash chain is scanned again for the (tsn, rdba).

In version8 I have seen a particular test case with over 60+ CR copies of a index root block (running many processes doing NL and inserts into that index didn't help ofcourse). It is alway hard to tell why the limit is not enforced (may be we need a stat on this? ;-)). It could be a bug or buffer pinned (have seen both in production situations).

Anjo.

On Thursday 20 February 2003 07:18, Cary Millsap wrote:
> My guess would be it's the pinning issue. A pinned block will *never* be
> expelled, and the Oracle kernel is very unlikely to "come back later" to
> do an operation that's not possible to accomplish right now.
>
> However, having said that, it's likely something else too. I say this
> because I haven't even attempted to study the issue since about 1995,
> and virtually every time I've ever seen Steve Adams or Jonathan Lewis or
> Stephan Haisley's (etc.) mouth move, I become more aware that what I
> *thought* I knew in about 1995 is not really knowledge, it's more
> "Knowledge Light."
>
>
> 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-----
> Gopalakrishnan
> Sent: Thursday, February 20, 2003 5:14 AM
> To: Multiple recipients of list ORACLE-L
>
> 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.

-- 
----------------------------------------------------------------
Anjo Kolk
http://www.oraperf.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).