Interesting. Any idea of what the point is in preventing other processes from reading a block in the buffer?
Jared On Saturday 01 December 2001 20:10, MacGregor, Ian A. wrote: > The P3 value of 130 on the buffer busy waits does indicate that the block > is being read by another process as Malcolm stated that's the process doing > the scattered read (Full table scan). Oracle needs to protect the block > while it is being read. The others sessions are waiting until the read of > that block is complete. > > For a definition of the P3 values see Steve Adam's website > http://www.ixora.com.au/ > > His full explanation of P3 id 130 is > > > 1013 Block is being read by another session and no other > or 130 suitable block image was found, so we wait until the read > is completed. This may also occur after a buffer cache > assumed deadlock. The kernel can't get a buffer in a > certain amount of time and assumes a deadlock. Therefore it > will read the CR version of the block. > > > Ian MacGregor > > -----Original Message----- > Sent: Saturday, December 01, 2001 6:20 PM > To: Multiple recipients of list ORACLE-L > > > > Malcolm, > > The paragraph below would indicate that readers are blocking. > > Readers don't block in Oracle. The only reason I can think of at > the moment for a SELECT to cause buffer busy waits is delayed > block cleanout, of which there has been a lot of discussion lately. > > I could be all mixed up here I guess, it's Saturday and I dont' want > to think too hard about all this. Don't have time to break out the FM > so I'll just sit back and wait for you to agree or refute. ;) > > Jared > > On Tuesday 27 November 2001 00:25, Thorns, Malcolm (NESL-IT) wrote: > > Jeff, > > > > The 3 sessions are doing the same (or similar) queries. In this case > > count(*) which is forcing a full table scan of the table in each session. > > The 3 sessions are thus trying to access the same blocks from the SGA, > > in the same order. Only 1 session can access a block in the SGA at a > > time - this is the session showing 'db file scattered read'. The other 2 > > sessions need to wait for the block (these waits show as 'buffer busy > > waits' - ie waiting for the block in the SGA). You will see the block id > > (and perhaps the file id) changing as the FTS's progress. Thus the > > sessions are 'chasing' each other through the blocks - holding each other > > up with SGA block contention - which shows up as 'buffer busy waits'. > > Hope that explains things. > > > > Regards, > > > > Malcolm > > > > -----Original Message----- > > Sent: Monday, November 26, 2001 11:21 PM > > To: Multiple recipients of list ORACLE-L > > > > > > > > We recently had a new website go live. Since then, I'm seeing constant > > buffer busy waits > > and after a period of time, I see sessions hung on the same block#. > > The SQL query > > is always a COUNT(*) (below). It's almost as though one session has a > > lock > > > > of some sort in the buffer cache and other sessions are blocked. > > Although, I've checked and > > there's no DML ongoing, so I'm unsure as to why we would see this. Note > > that v$session shows > > 78 and 393 to be INACTIVE, while 159 is ACTIVE. So it's like 159 can't > > write to > > the buffer cache because 78 and 393 have a lock there. Note that these > > are all defined > > as persistent connections, via the Vignette front-end. I'm sure all the > > clues are there > > but my brain is too fuzzed to piece it together. > > > > SID SQL_TEXT O/S > > User > > ----- ---------------------------------------------------------------- > > --------------- > > 159 SELECT COUNT(*) NUM,SUM(TOTAL_CHARGE_AMT) TOT FROM BBN.BBN_SRV > > vignette > > 159 _PAID_WARR_CLAIM WHERE CUSTOMER_ID = :b1 AND ENTERPRISE_CD = : > > vignette > > 159 b2 AND (CHECK_ID IS NOT NULL AND CHECK_ID != 'PENDING' ) > > vignette > > > > SID EVENT P1TEXT P1 P2TEXT P2 > > P3TEXT P3 > > ----- -------------------------- ------------ -- ----------- ------ ----- > > ------ > > 78 buffer busy waits file# 72 block# 109177 id > > 130 > > 393 buffer busy waits file# 72 block# 109177 id > > 130 > > 159 db file scattered read file# 72 block# 109177 > > blocks 8 > > > > > > -------------------------------------------- > > Jeffery D Thomas > > DBA > > Thomson Information Services > > Thomson multimedia Inc. > > > > Email: [EMAIL PROTECTED] > > DBA Quickplace: http://gkmqp.tce.com/tis_dba > > <http://gkmqp.tce.com/tis_dba> > > > > -------------------------------------------- > > > > > > ************************************************************************* > >** ***************** E mail Disclaimer > > > > You agree that you have read and understood this disclaimer and you agree > > to be bound by its terms. > > > > The information contained in this e-mail and any files transmitted with > > it (if any) are confidential and intended for the addressee only. If you > > have received this e-mail in error please notify the originator or > > telephone 0191 210 2060 or e-mail [EMAIL PROTECTED] > > > > This e-mail and any attachments have been scanned for certain > > viruses prior to sending but neither Northern Electric plc nor any of the > > companies in the Northern Electric group of companies from whom this > > e-mail originates shall be liable for any losses as a result of any > > viruses being passed on. > > > > No warranty of any kind is given in respect of any information contained > > in this e-mail and you should be aware that that it might be incomplete, > > out of date or incorrect. It is therefore essential that you verify all > > such information with us before placing any reliance upon it. > > > > Northern Electric plc > > Carliol House > > Market Street > > Newcastle-upon-Tyne > > NE1 6NE > > Registered in England and Wales: Number 2366942 > > > > > > ************************************************************************* > >** ***************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
