Re: buffer busy waits and v$filestat
Not completely true.
If the dbwr is going to write a buffer, it will set a bit that the buffer is
being written. In the good old days, this meant that the buffer could be
changed until the block was written ('write complete waits'). However in 8.1,
cloning of buffers was introduced. So now the dirty buffer is cloned (see
stats in v$sysstat), the new clone can be changed, the old clone is written.
(old and new are bad words, but you get my drift). So write complete waits
should be a whole lot less in 8.1 then before. They may still happen (not
sure why yet).
Anjo.
On Wednesday 30 July 2003 03:04, you wrote:
That's On 2003.07.29 19:59, Diego Cutrone wrote:
Another thing I think (I'm sorry to disagree with
Mladen on this) is that when DBWR hasn't finished
writing a buffer to the disk, and a session wants that
buffer in exclusive mode, there's a wait and that wait
is computed as a write complete wait and not as BBW.
DBWR works in 2 phases:
a) It scans for dirty buffers and pins them.
b) It starts IO, usually using writev.
If IO has been launched and not yet finished, then the wait is
write complete wait. If IO hasn't been started yet, we have buffer
busy. This write complete wait became essential with the advent of
asynchronous I/O. I was just simplifying things, nothing else.
--
Anjo Kolk
http://www.oraperf.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Anjo Kolk
INET: [EMAIL PROTECTED]
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: [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 and v$filestat
Thanks, Mladen, for that helpful quick reply. The reason I ask is, one of our clients claims that they see a intermittent database performance degrade (queries get timed out), during times when the RMAN backup runs. Using an OS monitoring tool, they see a spike in I/O. The RMAN backups have 4 channels writing to disk, and as such, I/O is to be expected. I generated statspack reports during the times the backup runs, and the tablespace I/O summary does show high avg reads/ms. Also, I see higher than normal total waits in the tablespace I/O summary. This, I guess, should be the buffer busy wait events, though I dont see it amongst the top 15 wait events. Also, the data block waits in v$waitstat spikes during this backup. So, I was wondering if I these wait events are the real cause of the I/O spike? Thanks Raj Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hia.net cc: Sent by: Subject: Re: buffer busy waits and v$filestat [EMAIL PROTECTED] ity.com 07/29/2003 05:49 PM Please respond to ORACLE-L Buffer busy wait has a different correlation with v$filestat and I/O. Buffer busy wait simply means that the buffer you're waiting for is pinned by somebody else. There are 3 classic situations: 1) DBWR hasn't finished writing to the disk yet. 2) Block is locked by another node (OPS, RAC). 3) RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. To make the long story short, there is a note on metalink (Note:155971.1) with an appropriate title: Resolving Intense and Random Buffer Busy Wait Performance Problems. Buffer busy waits are usually a consequence of I/O subsystem not being to provide enough throughput to the database. What can you do with v$filestat? You can find where are your hot spots and fix the problem. On 2003.07.29 17:24, [EMAIL PROTECTED] wrote: Folks, Say a session issues a read request, and finds another session already reading the block into the buffer cache. If this session waits N ms on a buffer busy waits event, does this N ms of wait get added to the read times in v$filestat? Or is the readtim in v$filestat purely physical I/O? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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 and v$filestat
Raj - You might also consider monitoring the I/O from the system side, until that you won't have the full picture. That is probably where the RMAN load would show up, and your instance would be indirectly affected. 4 channels sounds pretty heavy. Is this tuned for to keep a tape drive busy? We haven't had any noticed any interference, but since we're backing up to disk, we just use a single channel. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 30, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Thanks, Mladen, for that helpful quick reply. The reason I ask is, one of our clients claims that they see a intermittent database performance degrade (queries get timed out), during times when the RMAN backup runs. Using an OS monitoring tool, they see a spike in I/O. The RMAN backups have 4 channels writing to disk, and as such, I/O is to be expected. I generated statspack reports during the times the backup runs, and the tablespace I/O summary does show high avg reads/ms. Also, I see higher than normal total waits in the tablespace I/O summary. This, I guess, should be the buffer busy wait events, though I dont see it amongst the top 15 wait events. Also, the data block waits in v$waitstat spikes during this backup. So, I was wondering if I these wait events are the real cause of the I/O spike? Thanks Raj Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hia.net cc: Sent by: Subject: Re: buffer busy waits and v$filestat [EMAIL PROTECTED] ity.com 07/29/2003 05:49 PM Please respond to ORACLE-L Buffer busy wait has a different correlation with v$filestat and I/O. Buffer busy wait simply means that the buffer you're waiting for is pinned by somebody else. There are 3 classic situations: 1) DBWR hasn't finished writing to the disk yet. 2) Block is locked by another node (OPS, RAC). 3) RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. To make the long story short, there is a note on metalink (Note:155971.1) with an appropriate title: Resolving Intense and Random Buffer Busy Wait Performance Problems. Buffer busy waits are usually a consequence of I/O subsystem not being to provide enough throughput to the database. What can you do with v$filestat? You can find where are your hot spots and fix the problem. On 2003.07.29 17:24, [EMAIL PROTECTED] wrote: Folks, Say a session issues a read request, and finds another session already reading the block into the buffer cache. If this session waits N ms on a buffer busy waits event, does this N ms of wait get added to the read times in v$filestat? Or is the readtim in v$filestat purely physical I/O? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [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 and v$filestat
Buffer busy wait has a different correlation with v$filestat and I/O. Buffer busy wait simply means that the buffer you're waiting for is pinned by somebody else. There are 3 classic situations: 1) DBWR hasn't finished writing to the disk yet. 2) Block is locked by another node (OPS, RAC). 3) RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. To make the long story short, there is a note on metalink (Note:155971.1) with an appropriate title: Resolving Intense and Random Buffer Busy Wait Performance Problems. Buffer busy waits are usually a consequence of I/O subsystem not being to provide enough throughput to the database. What can you do with v$filestat? You can find where are your hot spots and fix the problem. On 2003.07.29 17:24, [EMAIL PROTECTED] wrote: Folks, Say a session issues a read request, and finds another session already reading the block into the buffer cache. If this session waits N ms on a buffer busy waits event, does this N ms of wait get added to the read times in v$filestat? Or is the readtim in v$filestat purely physical I/O? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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 and v$filestat
Hello Raj. BBW with a p3=0 are a consecuence of the I/O subsystem not being able to provide enough throughput to the database, as Mladen has said. But there are also many others causes for BBW. Check p3. Also if the session A is waiting for a buffer in the buffer cache (that's a BBW), the buffer is already in the BC so this session has no need (won't) to do a physical I/O for that block. Another thing I think (I'm sorry to disagree with Mladen on this) is that when DBWR hasn't finished writing a buffer to the disk, and a session wants that buffer in exclusive mode, there's a wait and that wait is computed as a write complete wait and not as BBW. please correct me if I'm wrong. thanks HTH Greetings Diego Cutrone Folks, Say a session issues a read request, and finds another session already reading the block into the buffer cache. If this session waits N ms on a buffer busy waits event, does this N ms of wait get added to the read times in v$filestat? Or is the readtim in v$filestat purely physical I/O? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [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). Internet GRATIS es Yahoo! Conexión 4004-1010 desde Buenos Aires. Usuario: yahoo; contraseña: yahoo Más ciudades: http://conexion.yahoo.com.ar -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Diego=20Cutrone?= INET: [EMAIL PROTECTED] 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: [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 and v$filestat
That's On 2003.07.29 19:59, Diego Cutrone wrote: Another thing I think (I'm sorry to disagree with Mladen on this) is that when DBWR hasn't finished writing a buffer to the disk, and a session wants that buffer in exclusive mode, there's a wait and that wait is computed as a write complete wait and not as BBW. DBWR works in 2 phases: a) It scans for dirty buffers and pins them. b) It starts IO, usually using writev. If IO has been launched and not yet finished, then the wait is write complete wait. If IO hasn't been started yet, we have buffer busy. This write complete wait became essential with the advent of asynchronous I/O. I was just simplifying things, nothing else. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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 and v$filestat
Gogala, quote RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. /quote I have read that RMAN has the advantage of not generating excessive redo which alter tablespace begin backup does. When RMAN locks a data block while backup and suppose if some data is to be written to the same data block,what happens now ? 1.where will the data be until RMAN releases the lock ? 2.will this be a wait event.if so,how can it be identified ? Sorry,if i sound silly. i am going thro' the RMAN book by Robert Freeman and so curious to know ?! Regards, Jp. 30-07-2003 06:49:23, Mladen Gogala [EMAIL PROTECTED] wrote: Buffer busy wait has a different correlation with v$filestat and I/O. Buffer busy wait simply means that the buffer you're waiting for is pinned by somebody else. There are 3 classic situations: 1) DBWR hasn't finished writing to the disk yet. 2) Block is locked by another node (OPS, RAC). 3) RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. To make the long story short, there is a note on metalink (Note:155971.1) with an appropriate title: Resolving Intense and Random Buffer Busy Wait Performance Problems. Buffer busy waits are usually a consequence of I/O subsystem not being to provide enough throughput to the database. What can you do with v$filestat? You can find where are your hot spots and fix the problem. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] 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: [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 and v$filestat
On 2003.07.29 21:36, Prem Khanna J wrote: Gogala, quote RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. /quote I have read that RMAN has the advantage of not generating excessive redo which alter tablespace begin backup does. When RMAN locks a data block while backup and suppose if some data is to be written to the same data block,what happens now ? The answer is very simple: write will wait until block is released (un-pinned). Locks, latches and pins exist to prevent data corruption from the simultaneous access. Those mechanisms are, essentially, serializing the access to the critical resources. There is a very nice book, an oldie but goodie, that I read once upon a time, called Modern Operating Systems, written by Andrew Tannenbaum. 1.where will the data be until RMAN releases the lock ? 2.will this be a wait event.if so,how can it be identified ? Data will wait in user buffers. Yes, it will be a wait event, and same with the DBWR, when the buffer is pinned, it will be a buffer busy, and when I/O is in progress, it will be write complete. Sorry,if i sound silly. i am going thro' the RMAN book by Robert Freeman and so curious to know ?! Bob Freeman's book is an excellent book which helped me understand and write a few RMAN scripts. It is neither intended nor very good at thorough explanation of oracle kernel. For that, there is Steve Adams and his book. I'd also like to abuse this occasion and ask Steve whether he plans a revised V9 (or 10x) edition? Regards, Jp. 30-07-2003 06:49:23, Mladen Gogala [EMAIL PROTECTED] wrote: Buffer busy wait has a different correlation with v$filestat and I/O. Buffer busy wait simply means that the buffer you're waiting for is pinned by somebody else. There are 3 classic situations: 1) DBWR hasn't finished writing to the disk yet. 2) Block is locked by another node (OPS, RAC). 3) RMAN is writing the block. That's right, RMAN locks (pins) blocks in memory, otherwise it couldn't ensure consistent backup. That is the reason why RMAN doesn't need alter tablespace begin backup command. To make the long story short, there is a note on metalink (Note:155971.1) with an appropriate title: Resolving Intense and Random Buffer Busy Wait Performance Problems. Buffer busy waits are usually a consequence of I/O subsystem not being to provide enough throughput to the database. What can you do with v$filestat? You can find where are your hot spots and fix the problem. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [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 and v$filestat
Thanks a lot for your explanation Gogala. Eagerly waiting for Steve's revised edition of Oracle Internals. Regards, Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] 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: [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).
