| 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: Buffer Busy Waits -- Sanity check please
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/SUser
SID EVENT P1TEXT P1 P2TEXT P2 P3TEXTP3
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 8Jeffery D Thomas
--------------------------------------------
Email: thomasje_at_tce.com
DBA Quickplace: http://gkmqp.tce.com/tis_dba <http://gkmqp.tce.com/tis_dba>
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 postmaster_at_northern-electric.co.uk.
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: Thorns, Malcolm (NESL-IT) INET: Malcolm.Thorns_at_northern-electric.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing ListsReceived on Tue Nov 27 2001 - 02:53:48 CST
--------------------------------------------------------------------
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).
 |
 |