Re: Buffer Busy Waits -- Sanity check please

[Jared . Still]

Thanks for the explanation.  I usually try to avoid delving
that deep into the internals, but I guess it's necessary on
occasion just to understand what's going on.

Jared



   
 
Riyaj_Shamsude 
 
[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Re: Buffer Busy Waits -- Sanity 
check please  
om 
 
   
 
   
 
12/03/01 07:00 
 
AM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 





Jared
Say, process A is interested in reading a block, then it hashes the
data block address of the block to find the hash bucket in the buffer
cache. If that specific block is in the buffer cache, then it must be
attached with that hash bucket. Holding the hash bucket latch, the process
A will look for the buffer in that hash chain with that data block address
. If the buffer is found in the buffer cache, then that process has to
examine the state of the buffer before proceeding further.
If another process B is operating on the buffer, i.e. reading a
database block from the disk in to the buffer (for FTS or otherwise), then
the process B will pin the buffer and the buffer is not available until the
read is completed. So, the process A will wait for the buffer to be
unpinned, posting 'buffer busy event'. Since this event can happen in
various points in the buffer lifecycles, p3 indicates details about the
wait itself.
Point being that, two processes can not  operate on the same buffer
simultaneously. Even though readers do not block readers in terms of locks,
they could be blocked due to buffer unavailability, but this event is
usually very brief.
As malcolm suggested, probably, the processes are chasing one
another.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

   
   Jared Still 
   <[EMAIL PROTECTED]> To:Multiple recipients of list 
   Sent by: ORACLE-L <[EMAIL PROTECTED]>
   [EMAIL PROTECTED] cc:
            Subject:Re: Buffer Busy Waits  
    -- Sanity check please 
   12/02/01 10:15 PM   
   Please respond to   
   ORACLE-L
   






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
>
>
>1013Block is being read by another session and no other
>  or 130suitable block image was found, so we wait until the read
>is completed. This may also occur after a buffer cache
&g

RE: Buffer Busy Waits -- Sanity check please

[Khedr, Waleed]

Enabling parallel=2 or higher on this table could resolve the problem for
the original poster.

Regards,

Waleed

-Original Message-
Sent: Saturday, December 01, 2001 9: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 waitsfile#  72 block#  109177  id
> 130
>   393 buffer busy waitsfile#  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

>
> 
>
>
>
***
>* 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

Re: Buffer Busy Waits -- Sanity check please

[Riyaj_Shamsudeen]

Jared
        Say, process A is interested in reading a block, then it hashes the data block address of the block to find the hash bucket in the buffer cache. If that specific block is in the buffer cache, then it must be attached with that hash bucket. Holding the hash bucket latch, the process A will look for the buffer in that hash chain with that data block address . If the buffer is found in the buffer cache, then that process has to examine the state of the buffer before proceeding further.
        If another process B is operating on the buffer, i.e. reading a database block from the disk in to the buffer (for FTS or otherwise), then the process B will pin the buffer and the buffer is not available until the read is completed. So, the process A will wait for the buffer to be unpinned, posting 'buffer busy event'. Since this event can happen in various points in the buffer lifecycles, p3 indicates details about the wait itself.
        Point being that, two processes can not  operate on the same buffer simultaneously. Even though readers do not block readers in terms of locks, they could be blocked due to buffer unavailability, but this event is usually very brief.
        As malcolm suggested, probably, the processes are chasing one another.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Jared Still <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/02/01 10:15 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: Buffer Busy Waits -- Sanity check please



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
> > 

Re: Buffer Busy Waits -- Sanity check please

[Jared Still]

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
>
>
>1013Block is being read by another session and no other
>  or 130suitable 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 waitsfile#  72 block#  109177  id
> > 130
> >   393 buffer busy waitsfile#  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
> > 
> >
> > 
> >
> >
> > *
> >** * E mail Disclaimer
> >
> > You agree that you have read and understood this disclaimer and you agree
> > to be bound by its terms.
> >
> 

RE: Buffer Busy Waits -- Sanity check please

[MacGregor, Ian A.]

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


   1013Block is being read by another session and no other
 or 130suitable 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 waitsfile#  72 block#  109177  id
> 130
>   393 buffer busy waitsfile#  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 
>
> 
>
>
> ***
>* 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
> com

Re: Buffer Busy Waits -- Sanity check please

[Jared Still]

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 waitsfile#  72 block#  109177  id
> 130
>   393 buffer busy waitsfile#  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 
>
> 
>
>
> ***
>* 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
-

Antwort: RE: Antwort: RE: Buffer Busy Waits -- Sanity check please

[Ivo . Libal]

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)"
 
   
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)"


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&#

Re: Buffer Busy Waits -- Sanity check please

[Stephane Faroult]

> Thomas Jeff wrote:
> 
> 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 waitsfile#  72 block#  109177
> id   130
>   393 buffer busy waitsfile#  72 block#  109177
> id   130
>   159 db file scattered read   file#  72 block#  109177
> blocks8
> 
> 
> Jeffery D Thomas
> DBA
> Thomson Information Services
> Thomson multimedia Inc.
> 
> Email: [EMAIL PROTECTED]
> DBA Quickplace: http://gkmqp.tce.com/tis_dba
> 
> 

SELECTs sometimes do some block house-keeping, and thus modify Oracle
buffers even if they are not supposed to. I have also witnessed strange
behaviours with IOTs. What do other queries do? Have you checked
V$SQLAREA to know whether this query is executed very often or not? I
guess that at least CUSTOMER_ID is indexed (BTW it would be interesting
to know whether the busy block is a data or index block. Try this :

select owner, segment_name, partition_name, segment_type
from dba_extents
where file_id = 72
 and block_id <= 109177
  and   109177 < block_id + blocks

If the block is a table block, you can fudge the issue by making Oracle
only look into an index storing all referenced columns (which would
probably also mean making CHECK_ID not null, side-effects on your code).
If it's an index block, it's more delicate to handle.

If your query is executed very often, denormalizing might also be an
idea. I am no great fan of denormalisation but a trigger to maintain a
count and a sum would be comparable in overhead cost to an additional
index.

-- 
HTH,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:+44  (0) 7050-696-449 
Performance Tools & Free Scripts
--
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  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).

RE: Antwort: RE: Buffer Busy Waits -- Sanity check please

[Thorns, Malcolm (NESL-IT)]

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)"

   
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 waitsfile#  72 block#  109177  id
130
  393 buffer busy waitsfile#  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

Antwort: RE: Buffer Busy Waits -- Sanity check please

[Ivo . Libal]

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)"
 
   
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 waitsfile#  72 block#  109177  id
130
  393 buffer busy waitsfile#  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 addres

RE: Buffer Busy Waits -- Sanity check please

[Thorns, Malcolm (NESL-IT)]

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 waitsfile#  72 block#  109177  id
130 
  393 buffer busy waitsfile#  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 

 
  


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).

RE: Buffer Busy Waits -- Sanity check please

[Khedr, Waleed]

Title: Buffer Busy Waits -- Sanity check please



Check 
what segment owns this block: file#  72 
block#  109177  
 
This could 
help finding the way to approach this problem.

  -Original Message-From: Thomas Jeff 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, November 26, 2001 6:21 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  Buffer Busy Waits -- Sanity check please
  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