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). > > > > >
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 ser
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 ?
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).
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, 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 ?
"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 ?
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).
