| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Automatic Segment Space Management
Stephan just passed this on to me...
"Cary,
I really must subscribe to this mail list, but until I do, maybe you can pass this on.
You are correct, the _db_block_max_cr_dba parameter is just a guide. I believe that when needing to create a new CR copy and this limit has been reached Oracle tries to place any older CR buffers (not sure if it does all of them or oldest found) to the cold end of the LRU ready to leave the cache at the next possible opportunity. If the buffer has any users or waiters (can be seen in x$bh), then the CR buffer will remain in cache until next time. When a new CR buffer is created, and an older CR buffer no longer has users or waiters, it should be aged out of the cache as soon as possible.
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 1:41 PM
To: Multiple recipients of list ORACLE-L
Anjo personally "saved my bacon" when I was at a site in Dallas with this problem. This particular problem was a vendor application ported from Sybase and thus used "select from blah_id for update; update blah; commit;" instead of Oracle sequences. These guys had 1,200+ CR copies of each little 1-row-1-column id table in their system. In the end, the vendor repaired its app to use sequence numbers (within the week, actually!), and the problem which had caused daily shutdown/restarts ended instantly. The "42 patch," as it was called at the time, would have helped reduce the severity of the problem, but it wouldn't have solved it.
I was pretty proud of myself when the engagement was done, but a monkey could have probably executed my part in the project if the monkey had known how to call Anjo.
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: Thursday, February 20, 2003 1:04 PM
To: Multiple recipients of list ORACLE-L
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
>
>
-- ---------------------------------------------------------------- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: cary.millsap_at_hotsos.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: cary.millsap_at_hotsos.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 - 14:26:35 CST
 |
 |