| 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
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
> 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
> ---------------------------------------------
--Received on Thu Feb 20 2003 - 00:23:43 CST
> 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:orarup_at_hotmail.com]
> > 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" <ORACLE-L_at_fatcity.com>
> > 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: jhostetter_at_decommunications.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: Arup Nanda
> > INET: orarup_at_hotmail.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: John Kanagaraj
> INET: john.kanagaraj_at_hds.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: Arup Nanda INET: orarup_at_hotmail.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).
 |
 |