The table has about 550,000 rows, and will be growing, currently about
230MB in size.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 27, 2001 7:25 AM
To: Multiple recipients of list ORACLE-L
Subject: Antwort: RE: Antwort: RE: Buffer Busy Waits -- Sanity check
please
But what if they are aging out, isnt that one of reasons for buffer busy
and they are - if there is a scattered read....
From docus:
As buffer busy waits are due to contention for particular blocks then you
cannot take any action until you know which blocks are being competed for
and why. Eliminating the cause of the contention is the best option. Note
that "buffer busy waits" for data blocks are often due to several processes
repeatedly reading the same blocks (eg: if lots of people scan the same
index) - the first session processes the blocks that are in the buffer
cache quickly but then a block has to be read from disk - the other
sessions (scanning the same index) quickly 'catch up' and want the block
which is currently being read from disk - they wait for the buffer as
someone is already reading the block in.
If the table s not too big, than I would put it to the cache to avoid
reloads from harddisk.
Ivo
____________________________________________________________________
Ivo Libal, Bc.
Design & Development, Warehousemanagementsystems Dept.
KNAPP Systemsintegration GmbH
Waltenbachstraße 9
A-8700 Leoben, Austria
Phone: ++43/3842/805-0
e-Mail: [EMAIL PROTECTED]
"Thorns, Malcolm (NESL-IT)"
<Malcolm.Thorns@northern-elect An: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
ric.co.uk> Kopie:
Gesendet von: [EMAIL PROTECTED] Thema: RE: Antwort: RE: Buffer Busy Waits -- Sanity check please
11/27/2001 11:10 AM
Bitte antworten an ORACLE-L
This will not resolve the 'buffer busy wait' scenario.
The contention is for blocks that are already in the SGA.
Regards,
Malcolm.
-----Original Message-----
Sent: Tuesday, November 27, 2001 9:15 AM
To: Multiple recipients of list ORACLE-L
Hi
I would try to set the table to cache mode. It seams that the blocks are
aged out from buffer - which is default for FTS.
alter table BBN_SRVvignette cache;
to avoid reloading into cache.
Ivo
____________________________________________________________________
Ivo Libal, Bc.
Design & Development, Warehousemanagementsystems Dept.
KNAPP Systemsintegration GmbH
Waltenbachstraße 9
A-8700 Leoben, Austria
Phone: ++43/3842/805-0
e-Mail: [EMAIL PROTECTED]
"Thorns, Malcolm (NESL-IT)"
<Malcolm.Thorns@northern-elect An: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
ric.co.uk> Kopie:
Gesendet von: [EMAIL PROTECTED] Thema: RE:
Buffer
Busy Waits -- Sanity check please
11/27/2001 09:25 AM
Bitte antworten an ORACLE-L
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: Thorns, Malcolm (NESL-IT)
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
********************************************************************************************
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: Thorns, Malcolm (NESL-IT)
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
