Re: RE: why does block cleanout incur redo?
thanks... I have jonathan lewis's book on my list to read. The transaction table is what is modified with the initrans and maxtrans setting. I get it now. when you perform block clean out you modify the block and state that it is no longer being used in this transaction. when the blocks are flushed by DBWR, the transaction table needs to be modified so that the number of transactions that is occurring is known. this causes redo. so that if you have to recover, the transaction table in each block can be accurate. Am I correct? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/08/06 Wed PM 11:29:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: why does block cleanout incur redo? It's because flush doesn't mean what you probably think. During a delayed block cleanout, Oracle updates a block's transaction table (ITL). Any time a block gets modified, there's redo. See Jonathan Lewis's Practical Oracle8i (pp43-44) for a description. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic http://www.hotsos.com/training/clinic101 101 in Denver, Sydney - Hotsos Symposium 2004 http://www.hotsos.com/events/symposium/2004 March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Ryan Sent: Wednesday, August 06, 2003 9:34 PM To: Multiple recipients of list ORACLE-L My understanding of block cleanout is that oracle is flushing transaction information of already committed transactions from the buffer cache. This can happen in selects, when 10% of the buffer cache is filled with 'lists' if blocks involved in transactions, or with dml. i dont understand why this incurs redo? your just flushing blocks that are no longer needed? Its because flush doesnt mean what you probably think. During a delayed block cleanout, Oracle updates a blocks transaction table (ITL). Any time a block gets modified, theres redo. See Jonathan Lewiss Practical Oracle8i (pp4344) for a description. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic101 in Denver, Sydney - Hotsos Symposium 2004 March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: Wednesday, August 06, 2003 9:34 PM To: Multiple recipients of list ORACLE-L Subject: why does block cleanout incur redo? My understanding of block cleanout is that oracle is flushing transaction information of already committed transactions from the buffer cache. This can happen in selects, when 10% of the buffer cache is filled with 'lists' if blocks involved in transactions, or with dml. i dont understand why this incurs redo? your just flushing blocks that are no longer needed?
why does block cleanout incur redo?
My understanding of block cleanout is that oracle is flushing transaction information of already committed transactions from the buffer cache. This can happen in selects, when 10% of the buffer cache is filled with 'lists' if blocks involved in transactions, or with dml. i dont understand why this incurs redo? your just flushing blocks that are no longer needed?
RE: why does block cleanout incur redo?
Its because flush doesnt mean what you probably think. During a delayed block cleanout, Oracle updates a blocks transaction table (ITL). Any time a block gets modified, theres redo. See Jonathan Lewiss Practical Oracle8i (pp4344) for a description. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic101 in Denver, Sydney - Hotsos Symposium 2004 March 710 Dallas - Visit www.hotsos.com for schedule details... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ryan Sent: Wednesday, August 06, 2003 9:34 PM To: Multiple recipients of list ORACLE-L Subject: why does block cleanout incur redo? My understanding of block cleanout is that oracle is flushing transaction information of already committed transactions from the buffer cache. This can happen in selects, when 10% of the buffer cache is filled with 'lists' if blocks involved in transactions, or with dml. i dont understand why this incurs redo? your just flushing blocks that are no longer needed?
Re: RE: why does block cleanout incur redo?
Excerpts from one of my notes, collated from excellent papers or websites by Steve Adams, Jonathan Lewis, and KGopal. The first block of every rollback segment, also called the segment header, has a structure called the transaction table, which keeps track of all the active transactions in that rollback segment. Transactions that use a rollback segment first register themselves in the transaction table using their transaction id. Also, each data block has one or many slots for an Interested transaction List, also called the ITL, in its header. Any transaction which modifies a row in a datablock must first get an ITL slot in that datablock. Each ITL consists of a transaction id, an address to the transaction table in the rollback segment, where the undo is located, a flag to indicate if the transaction is open, committed or pending for a cleanout, and the commit SCN. The ITL is contained in the transaction layer of an Oracle block header. Apart, from this, the cache layer of the oracle block header, also has an SCN which represents when the block was last changed. When a transaction issues a commit, Oracle marks the transaction as commited in the transaction header table of the corresponding rollback segment. Also, the changes in the log buffer are written to disk. However, the transaction entry in the ITL of the data block may or may not be commited. Before Oracle 7.3, Oracle never marked any of the changed data blocks as commited. Any subsequent transaction that needs to read the changed block, would be directed to check the rollback segments to see if the transaction is commited, and would then update the block headers with the commit SCN, close the ITL, and would tidy up the data block by writing it to disk. This process is called a 'delayed block cleanout'. Staring with Oracle 7.3, Oracle introduced a fast commit mechanism, whereby it does mark some of the changes as commited, depending on if your transaction is a short one or a long one. The threshold value which decides if a transaction is a long one or a short one is 10% of the value for db_block_buffers. Also, the affected data blocks should be in the buffer cache. Thus, if the number of blocks that is modified by a transaction does not exceed 10% of the number of blocks in the database buffer cache, and all of affected data block are still in the buffer cache, Oracle will indeed perform a 'commit cleanout'. Else, the next transaction reading the affected data blocks will perform a 'delayed block cleanout'. [EMAIL PROTECTED] net To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: RE: why does block cleanout incur redo? ity.com 08/07/2003 07:24 AM Please respond to ORACLE-L thanks... I have jonathan lewis's book on my list to read. The transaction table is what is modified with the initrans and maxtrans setting. I get it now. when you perform block clean out you modify the block and state that it is no longer being used in this transaction. when the blocks are flushed by DBWR, the transaction table needs to be modified so that the number of transactions that is occurring is known. this causes redo. so that if you have to recover, the transaction table in each block can be accurate. Am I correct? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/08/06 Wed PM 11:29:23 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: why does block cleanout incur redo? It's because flush doesn't
RE: is this block cleanout ?
MetaLink document 40689.1 contains a very nice description of delayed block cleanout, and walks the reader through an example. Jared Deshpande, Kirti [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/19/2002 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: is this block cleanout ? Stephane, The db version in question is 7.3.2. And as of 7.3 the init parameter, delayed_logging_block_cleanouts, defaults to TRUE (for OPS and non-OPS). From my understanding of the delayed block cleanout, when oracle commits a transaction the blocks that it changed are not immediately marked with the commit time. The change to blocks can be due to insert, update or delete. However, if those blocks were still in the buffer cache, the cleanout will take place immediately and there won't be any disk i/o for the cleanout. If select count(*) is causing an FTS, then the changed blocks that are not in the buffer cache may be getting cleaned. If there is no FTS, then there is something else going on And that's why I said 'sounds about right.' Hopefully tracing the session may reveal what's going on... Regards, - Kirti -Original Message- Sent: Saturday, October 19, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- 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: is this block cleanout ?
Rahul: If you're working under Oracle 7.3 and delayed_logging_block_cleanouts=TRUE (default), the block cleanouts will be delayed until another transaction modifies the blocks (when the block is required in current mode again). A simple select will not force the cleanout. If you set delayed_logging_block_cleanouts=FALSE (default in Oracle 8), a select (of course you'll have to force a FTS) will do the cleanouts. Please correct me if I'm wrong. HTH Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 21, 2002 7:18 AM list / Gopal, this is not a direct load, the app does normal insert .. 20+ million of them !! i checked the block cleanout parameter .. and it does default to TRUE in verion 7.3+ now.. .can turning this parameter to FALSE will speed up the FTS after the insert.. but then the inserts will take longer !! right ? because each insert will do the cleanout too .. -- From: K Gopalakrishnan[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 10:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: is this block cleanout ? Stephane: INSERTs certainly associated with block cleanout. But the issue here Rahul is doing a Direct load and this does not work as expected in earlier versions (7.3 and below). They do not use ITLs as in the regular inserts and there used be some bugs in 7.x versions were ITLs are not used in regular fashion, and this results in incorrect count(*) during the direct loads (something similar to Dirty read). These bugs are fixed in 8i and above versions where Direct inserts are also behave like regular inserts. But in any case Inserts are also associated with block cleanouts. Best Regards, K Gopalakrishnan -Original Message- Faroult Sent: Saturday, October 19, 2002 6:44 AM To: Multiple recipients of list ORACLE-L Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: K Gopalakrishnan 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.com -- Author: Rahul 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
RE: is this block cleanout ?
Please read MetaLink document # 40689.1 Jared dcutrone [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/21/2002 02:08 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: is this block cleanout ? Rahul: If you're working under Oracle 7.3 and delayed_logging_block_cleanouts=TRUE (default), the block cleanouts will be delayed until another transaction modifies the blocks (when the block is required in current mode again). A simple select will not force the cleanout. If you set delayed_logging_block_cleanouts=FALSE (default in Oracle 8), a select (of course you'll have to force a FTS) will do the cleanouts. Please correct me if I'm wrong. HTH Greetings Diego Cutrone - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, October 21, 2002 7:18 AM list / Gopal, this is not a direct load, the app does normal insert .. 20+ million of them !! i checked the block cleanout parameter .. and it does default to TRUE in verion 7.3+ now.. .can turning this parameter to FALSE will speed up the FTS after the insert.. but then the inserts will take longer !! right ? because each insert will do the cleanout too .. -- From: K Gopalakrishnan[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 10:18 PM To: Multiple recipients of list ORACLE-L Subject: RE: is this block cleanout ? Stephane: INSERTs certainly associated with block cleanout. But the issue here Rahul is doing a Direct load and this does not work as expected in earlier versions (7.3 and below). They do not use ITLs as in the regular inserts and there used be some bugs in 7.x versions were ITLs are not used in regular fashion, and this results in incorrect count(*) during the direct loads (something similar to Dirty read). These bugs are fixed in 8i and above versions where Direct inserts are also behave like regular inserts. But in any case Inserts are also associated with block cleanouts. Best Regards, K Gopalakrishnan -Original Message- Faroult Sent: Saturday, October 19, 2002 6:44 AM To: Multiple recipients of list ORACLE-L Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: K Gopalakrishnan 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.com -- Author: Rahul INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
is this block cleanout ?
when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: is this block cleanout ?
That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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.com -- Author: Deshpande, Kirti 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: is this block cleanout ?
Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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: is this block cleanout ?
Stephane, The db version in question is 7.3.2. And as of 7.3 the init parameter, delayed_logging_block_cleanouts, defaults to TRUE (for OPS and non-OPS). From my understanding of the delayed block cleanout, when oracle commits a transaction the blocks that it changed are not immediately marked with the commit time. The change to blocks can be due to insert, update or delete. However, if those blocks were still in the buffer cache, the cleanout will take place immediately and there won't be any disk i/o for the cleanout. If select count(*) is causing an FTS, then the changed blocks that are not in the buffer cache may be getting cleaned. If there is no FTS, then there is something else going on And that's why I said 'sounds about right.' Hopefully tracing the session may reveal what's going on... Regards, - Kirti -Original Message- Sent: Saturday, October 19, 2002 8:44 AM To: Multiple recipients of list ORACLE-L Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: Deshpande, Kirti 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: is this block cleanout ?
index blocks need to be cleaned out too... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 19, 2002 7:43 AM Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: Tim Gorman 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: is this block cleanout ?
Stephane: INSERTs certainly associated with block cleanout. But the issue here Rahul is doing a Direct load and this does not work as expected in earlier versions (7.3 and below). They do not use ITLs as in the regular inserts and there used be some bugs in 7.x versions were ITLs are not used in regular fashion, and this results in incorrect count(*) during the direct loads (something similar to Dirty read). These bugs are fixed in 8i and above versions where Direct inserts are also behave like regular inserts. But in any case Inserts are also associated with block cleanouts. Best Regards, K Gopalakrishnan -Original Message- Faroult Sent: Saturday, October 19, 2002 6:44 AM To: Multiple recipients of list ORACLE-L Deshpande, Kirti wrote: That's sounds about right... - Kirti -Original Message- Sent: Saturday, October 19, 2002 3:48 AM To: Multiple recipients of list ORACLE-L when i do a count(*) an a 1.2GB table, just after an app finishes inserting 20 million records i can see from iostat that, that disk is being read as well as written to !!! the only reason i can think of WRITES being performed while being READ is the block cleanout is being performed by the count(*) !! i would appreciate if anyone could explain further ! regards PS: 7.3.2 on AIX, the file is raw and async_io is true Kirti, This is also what I thought, but wouldn't a 'count(*)' just use the primary key? (Unless it is a 1.2 G unindexed table). Morover, in my mind a block cleanout is associated with a delete, not an insert. Might it be say the cleanout of temporary segments after say a direct load ? Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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.com -- Author: K Gopalakrishnan 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).
FW: FW: ORA-01555 - Delayed Block Cleanout
OK, I'm moving this question back to the list, since my understanding of delayed block cleanout is so weak. Here's the definition I found of delayed block cleanout: When a data or index block is modified in the database and the transaction committed, Oracle does a fast commit by marking the transaction as committed in the rollback segment header but does not clean the datablocks that were modified. The next transaction which does a select on the modified blocks will do the actual cleanout of the block. This is known as a delayed block cleanout. According to this definition, your problem cannot be delayed block cleanout unless a data block has been modified. If delayed block cleanout is the problem, the recommended solution is FTS before you start your query. (Note 40689.1: If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur prior to the transaction that returns the ora-1555. .. [set to rule and select count(*), or don't change optimizer and select count(*) with full hint] Forcing this FTS should not cause an ora-1555, because you have not modified any blocks. Here's where someone on the list can enlighten me. In your case, I believe you're doing the large data load, then the transaction completes. The dataload is followed by a query against the table as a separate transaction. I'm guessing that the FTS will NOT cause a 1555 because it's query only, and will resolve any outstanding block issues. I would think the follow-on FTS would only cause a 1555 if it was running at the same time as the original transaction that's loading the data. Can anyone on the list confirm this?? Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 12:34 PM To: Baker, Barbara Subject: Re: FW: ORA-01555 - Delayed Block Cleanout No, it's the same issue as before except I am trying to come up with a way of preventing the 1555 error. According to my understanding, 1555 due to delayed block cleanout occurs when a block is left as uncommitted, and the corresponding rollback segment block, which hopefully contains the SCN for when the block was committed isn't available because the segment wrapped and the block is no longer in the rollback segment, thus causing the 1555 error. So, according to what I have read, until all blocks are read, then and only then, or if the DB is bounced, will the blocks get cleaned out (marked committed). So, if the FTS fails due to 1555 it would seem to me that it failed on the first block that it encountered a problem with, that block gets cleaned but no more blocks are read in because of the error and therefore it is possible for the remaining unscanned blocks to still be flagged as uncommitted and the 1555 will keep occurring until every block has been scanned. If this is in fact the case then one may need to perform the FTS numerous times until all the blocks have been scanned successfully. This is obviously not practical and is what I am trying to get to the bottom of. The table in question is 20 million rows large, is truncated and loaded weekly, and no DML is ever performed on it. We put an exclusive lock on the table to ensure the 1555 wasn't the result of concurrent DML occurring. I am pretty confident that the 1555 we have seen is due to delayed block cleanout but again, if the solution is a FTS to clean the blocks out it would seem to me that a FTS could need to be done several times until a clean select can be performed. I'm not sure if the ANALYZE...COMPUTE that Jared suggested would fail due to 1555, if not, it would be a better alternative than multiple FTS's. Does this make sense? -w --- Baker, Barbara [EMAIL PROTECTED] wrote: I confess to knowing little about delayed block cleanout. However, if you get a 1555 error when doing a fts, then I assume someone is doing something other than querying the table at the same time you're doing the fts. I thought this table was going into a warehouse. Is there really that much activity on the table that you can't do a fts without someone else changing data on you at the same time? Or am I missing something?? Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Monday, January 28, 2002 8:55 AM To: Multiple recipients of list ORACLE-L Subject: ORA-01555 - Delayed Block Cleanout From what I have read, a full table scan may be necessary to initiate block cleanout in a VLDB to prevent ORA-01555 errors due to delayed block cleanout. However, if the full table scan itself falls victim to the ORA-01555 error, how does one guarantee that all the blocks in the table are visited?--keep running the FTS query? The parameter delayed_logging_block_cleanout is not an option as it is no longer available in my DB (8.1.7.2
Re: FW: FW: ORA-01555 - Delayed Block Cleanout
Barb, As per my understanding of delayed block count, the definition that you give, is apt for Oracle 7.3 and below. Your statement that it does not clean the datablocks that were modified also holds true. To add further, starting with Oracle 8, Oracle introduced a fast commit mechanism, whereby some of the data blocks are marked with the commit SCN. The number of data blocks that are marked as such, depends on the number of blocks that are updated. If the transaction is a short one, then all the blocks could be marked. However, if its a long transaction, then only a few of the blocks are marked. If a transaction is long or short, is determined by the number of blocks that are updated. The threshold value is 10% of db_block_buffers. Also, the blocks must be presently in the database buffer cache and not flushed out. If this is not so, then the old method of delayed block count takes place, where only the transaction entry in the rollback segment header is marked as commited. And I believe, a snapshot too old could also occur because of some modifications to a data block, before the next transaction reading that block started. Thats what I think. I could stand corrected. Insights?? Raj Baker, Barbara [EMAIL PROTECTED]@fatcity.com on 01/28/2002 03:56:38 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: OK, I'm moving this question back to the list, since my understanding of delayed block cleanout is so weak. Here's the definition I found of delayed block cleanout: When a data or index block is modified in the database and the transaction committed, Oracle does a fast commit by marking the transaction as committed in the rollback segment header but does not clean the datablocks that were modified. The next transaction which does a select on the modified blocks will do the actual cleanout of the block. This is known as a delayed block cleanout. According to this definition, your problem cannot be delayed block cleanout unless a data block has been modified. If delayed block cleanout is the problem, the recommended solution is FTS before you start your query. (Note 40689.1: If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur prior to the transaction that returns the ora-1555. .. [set to rule and select count(*), or don't change optimizer and select count(*) with full hint] Forcing this FTS should not cause an ora-1555, because you have not modified any blocks. Here's where someone on the list can enlighten me. In your case, I believe you're doing the large data load, then the transaction completes. The dataload is followed by a query against the table as a separate transaction. I'm guessing that the FTS will NOT cause a 1555 because it's query only, and will resolve any outstanding block issues. I would think the follow-on FTS would only cause a 1555 if it was running at the same time as the original transaction that's loading the data. Can anyone on the list confirm this?? Barb -- From: Walter K[SMTP:[EMAIL PROTECTED]] Sent: Monday, January 28, 2002 12:34 PM To: Baker, Barbara Subject: Re: FW: ORA-01555 - Delayed Block Cleanout No, it's the same issue as before except I am trying to come up with a way of preventing the 1555 error. According to my understanding, 1555 due to delayed block cleanout occurs when a block is left as uncommitted, and the corresponding rollback segment block, which hopefully contains the SCN for when the block was committed isn't available because the segment wrapped and the block is no longer in the rollback segment, thus causing the 1555 error. So, according to what I have read, until all blocks are read, then and only then, or if the DB is bounced, will the blocks get cleaned out (marked committed). So, if the FTS fails due to 1555 it would seem to me that it failed on the first block that it encountered a problem with, that block gets cleaned but no more blocks are read in because of the error and therefore it is possible for the remaining unscanned blocks to still be flagged as uncommitted and the 1555 will keep occurring until every block has been scanned. If this is in fact the case then one may need to perform the FTS numerous times until all the blocks have been scanned successfully. This is obviously not practical and is what I am trying to get to the bottom of. The table in question is 20 million rows large, is truncated and loaded weekly, and no DML is ever performed on it. We put an exclusive lock on the table to ensure the 1555 wasn't the result of concurrent DML occurring. I am pretty confident that the 1555 we have seen is due to delayed block cleanout but again, if the solution is a FTS to clean the blocks out it would seem to me that a FTS could need to be done several times until a clean select can be performed. I'm not sure
RE: FW: ORA-01555 - Delayed Block Cleanout
Hi Barb... Check out Note:45895.1 cause 3... I think this was the same one Jared posted the other day... I've posted part of the note below with an example that relates to your question... Cause #3: = Delayed block cleanout on old committed updates. An update operation completes and commits; the updated blocks are not touched again until a long-running query begins. Delayed Block Cleanout (DBC) has never been done on the blocks. This can result in a scenario which happens only under specific circumstances in VLDB, causing ORA-01555 errors when NO updates or inserts are being committed on the same blocks a query is retrieving. All of the following must be true for an ORA-01555 to occur in this case: (i) An update completes and commits and the blocks are not touched again until... ( Here is the large update - For arguments sake lets say SCN = 990 ) (ii) A long query begins against the previously updated blocks. ( Here is the FTS you are performing to cleanup - Read consistent SCN needs to be 1000 ) (iii) During the query, a considerable amount of DML takes place, though not on the previously updated blocks which the query is currently fetching. ( Other sessions are performing DML but not on the table you are interested in - Let's say a SCN of 1010 ) (iv) Under condition (iii) there is so much DML relative to available rollback space that the rollback segment used in the first update wraps around, probably several times. ( Not good ) (v) Under condition (iv), the commit SCN of the first update is cycled out of the rollback segment. ( You know where this is going ) (vi) Under condition (iv) the lowest SCN in the rollback segment is pushed higher than the read consistent SCN in the query. ( Bingo... - Rollback only knows about SCN's greater then 1005 due to this activity ) The above conditions imply that when a query reaches a block that has been updated but not cleaned out, the query quickly learns that the update committed, and accordingly cleans out the block. But because the update SCN is no longer in the rollback segment (condition (v)), the query doesn't know WHEN the update committed. ( Your read consistent SCN is 1000 but the rollback no longer contains this information ) This is important because if the commit happened before the query began, the current value in the block can be used by the query; but if the commit happened after, the old value must be fetched from the rollback segment. ( Read consistency 101 ) Now, because the rollback segment wrapped in (iv), we know that the update SCN can't be higher than the lowest SCN in the rollback segment, which gives us a nice upper bound. ( The upper bound would be 1005 in this example ) If we only knew that the read consistent SCN was higher than this upper bound, we would know that the update committed before the query started. ( This is not the case in my example since the read consistent SCN of 1000 is greater then the SCN of 1005 that is currently available in rollback ) But we don't know this because of condition (vi), so we can't even accurately estimate the update SCN. Hence, we get an ORA-01555. ( Since the lowest value SCN in rollback is 1005, we know that the block was committed before SCN 1005... But we do not know if the block committed before or after the start of the query at SCN 1000... Therefore, Oracle can not guarantee read consistency since it does not know if the block it is currently looking at changed before or after it started the query and therefore issues a snapshot too old ) HTH Tim -Original Message- Sent: Monday, January 28, 2002 3:57 PM To: Multiple recipients of list ORACLE-L OK, I'm moving this question back to the list, since my understanding of delayed block cleanout is so weak. Here's the definition I found of delayed block cleanout: When a data or index block is modified in the database and the transaction committed, Oracle does a fast commit by marking the transaction as committed in the rollback segment header but does not clean the datablocks that were modified. The next transaction which does a select on the modified blocks will do the actual cleanout of the block. This is known as a delayed block cleanout. According to this definition, your problem cannot be delayed block cleanout unless a data block has been modified. If delayed block cleanout is the problem, the recommended solution is FTS before you start your query. (Note 40689.1: If it is suspected that the block cleanout variant is the cause, then force block cleanout to occur prior to the transaction that returns the ora-1555. .. [set to rule and select count(*), or don't change optimizer and select count(*) with full hint] Forcing this FTS should not cause an ora-1555
ITL, DELAYED BLOCK CLEANOUT
Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT?Thanks
RE: ITL, DELAYED BLOCK CLEANOUT
Vladmir, Whenever we modify any data or index blocks and commit it, Oracle will do a fast commit by marking the transaction as committed in the rollback segment header. But Oralce does not actually clean up the locks in the modified data or index blocks. The next transaction which does a select on the those modified blocks will do the actual cleanout of the block. This is delayed block cleanout. Sorry, I cant shed much light on the Interested Transactions list though. Samir Sarkar Oracle DBA - Lennon Team SchlumbergerSema Email : [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418 Ext. 76217 Fax : +44 (0) 115 - 957 6018 -Original Message-From: Vladimir Barac - posao [mailto:[EMAIL PROTECTED]]Sent: 29 October 2001 14:25To: Multiple recipients of list ORACLE-LSubject: ITL, DELAYED BLOCK CLEANOUT Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT?Thanks ___ This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600. ___
RE: ITL, DELAYED BLOCK CLEANOUT
Vladimir, Have a look at http://www.geocities.com/kgkrish/transactions.html This will solve most of your questions. If not Pls let me know. Thanks Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Vladimir Barac - posao Sent: Monday, October 29, 2001 6:25 AM To: Multiple recipients of list ORACLE-L Subject: ITL, DELAYED BLOCK CLEANOUT Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT? Thanks
RE: ITL, DELAYED BLOCK CLEANOUT
Hi, The document is a draft copy. There are few (not much tech) errors. So take that with a pinch of salt Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of K Gopalakrishnan Sent: Monday, October 29, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: ITL, DELAYED BLOCK CLEANOUT Vladimir, Have a look at http://www.geocities.com/kgkrish/transactions.html This will solve most of your questions. If not Pls let me know. Thanks Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Vladimir Barac - posao Sent: Monday, October 29, 2001 6:25 AM To: Multiple recipients of list ORACLE-L Subject: ITL, DELAYED BLOCK CLEANOUT Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT? Thanks
RE: ITL, DELAYED BLOCK CLEANOUT
is this page still available or has been moved. I got an error trying to access the link Thx Deepak --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Hi, The document is a draft copy. There are few (not much tech) errors. So take that with a pinch of salt Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Gopalakrishnan Sent: Monday, October 29, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Vladimir, Have a look at http://www.geocities.com/kgkrish/transactions.html This will solve most of your questions. If not Pls let me know. Thanks Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Barac - posao Sent: Monday, October 29, 2001 6:25 AM To: Multiple recipients of list ORACLE-L Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT? Thanks __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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: ITL, DELAYED BLOCK CLEANOUT
I have no problems in accessing that. Alternatively you can find that at http://www.revealnet.com/newsletter/newsletter_1100.htm Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Thapliyal Sent: Monday, October 29, 2001 11:05 AM To: Multiple recipients of list ORACLE-L is this page still available or has been moved. I got an error trying to access the link Thx Deepak --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Hi, The document is a draft copy. There are few (not much tech) errors. So take that with a pinch of salt Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Gopalakrishnan Sent: Monday, October 29, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Vladimir, Have a look at http://www.geocities.com/kgkrish/transactions.html This will solve most of your questions. If not Pls let me know. Thanks Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Barac - posao Sent: Monday, October 29, 2001 6:25 AM To: Multiple recipients of list ORACLE-L Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT? Thanks __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: ITL, DELAYED BLOCK CLEANOUT
I have no problems in accessing that. Alternatively you can find that at http://www.revealnet.com/newsletter/newsletter_1100.htm Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Thapliyal Sent: Monday, October 29, 2001 11:05 AM To: Multiple recipients of list ORACLE-L is this page still available or has been moved. I got an error trying to access the link Thx Deepak --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Hi, The document is a draft copy. There are few (not much tech) errors. So take that with a pinch of salt Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Gopalakrishnan Sent: Monday, October 29, 2001 9:40 AM To: Multiple recipients of list ORACLE-L Vladimir, Have a look at http://www.geocities.com/kgkrish/transactions.html This will solve most of your questions. If not Pls let me know. Thanks Best Regards, K Gopalakrishnan Bangalore, INDIA + (91) 98451 78868 -Original Message- Barac - posao Sent: Monday, October 29, 2001 6:25 AM To: Multiple recipients of list ORACLE-L Just curious... Could someone shed some light on INTERESTED TRANSACTIONS LIST and DELAYED BLOCK CLEANOUT? Thanks __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan 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: delayed block cleanout
Hi, You are right bouncing the database won't do the trick. The next access to the block will do the cleanout no matter if the database has gone down in between. Jack Russell Brooks russell.brooks@amctechn To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ology.com cc: Sent by: Subject: delayed block cleanout [EMAIL PROTECTED] 27-02-2001 15:40 Please respond to ORACLE-L Hi, In order to avoid ORA-1555s due to delayed block cleanout during a particular conversion, we are running full table scans before the conversion. Someone has asked if bouncing the system prior to the conversion, which would be much faster, would suffice. I think the rollback segment entries are already flagged as commited, and the header of the data blocks would still contain the pointer to the RBS. Even if you bounce the system, I think you still get to wait until the next access of the block. Does anyone else have an opinion? Cheers, Russ Brooks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Russell Brooks 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). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopien te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young
RE: delayed block cleanout
Hi Russ, Yes, a bounce will not achieve anything, and neither will a full table scan if it is done in parallel or if 'delayed_logging_block_cleanouts' is TRUE (which is the default at 7.3 and 8.0). @ Regards, @ Steve Adams @ http://www.ixora.com.au/ @ http://www.christianity.net.au/ -Original Message- Sent: Wednesday, 28 February 2001 0:41 To: Multiple recipients of list ORACLE-L Hi, In order to avoid ORA-1555s due to delayed block cleanout during a particular conversion, we are running full table scans before the conversion. Someone has asked if bouncing the system prior to the conversion, which would be much faster, would suffice. I think the rollback segment entries are already flagged as commited, and the header of the data blocks would still contain the pointer to the RBS. Even if you bounce the system, I think you still get to wait until the next access of the block. Does anyone else have an opinion? Cheers, Russ Brooks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Adams 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).
