Re: Delayed block cleanout

In article <em9e28300028_at_novalink.com>, rmanieri_at_novalink.com (Robert L. Manieri) writes:
>
>Does anybody out there have any experience with a phenomenom Oracle
>support refers to as "delayed block cleanout"?
>.

Yes...

>Here's the situation:
>We are loading a 5.7 gigabyte input file into one table and committing
>every so many records. Because of the way Oracle does its fast commit,
>the headers of the new data blocks do not get updated with the
>lastest system commit number (SCN) until someone actually queries
>the table. So what happens is you load the data just fine and the
>first time you issue:
> SELECT COUNT(column_name) FROM table_name ;
>you wait for a good long time while the SCN is updated in every
>block that was just loaded. This is the delayed block cleanout.
>.
>The second time to execute the query
>about in runs in 5% of the time it took the first time.
>.
>Any suggestions will receive immediate consideration.

...but I don't believe there's a great deal you can do. In most situations the fast write / delayed block cleanout mechanism works well: say 10,000 rows are updated and committed, and are then read, a few at a time, over a longish period, the block cleanout work (of reading the transaction table and writing back to the block) is split up into barely noticeable amounts, and shared by different users. In this way the overhead is shifted from the (single) writer of the blocks to the (many) initial readers of each of the effected blocks. The problem arises when you take the cleanout hit in one go (by a single user) and where there's a lot of blocks to cleanout, as in your case.

>I tried querying the table as the load runs, but that makes the
>load take more than twice as much as the load and query by
>themselves. Also, tried increasing the block size and the jury
>is still out on that one, test is running now.
>.

I think your choices are either to avoid the problem by not performing any full table scans (like your "SELECT COUNT(column_name) FROM table_name ;") so that the cleanout occurs piecemeal as intended, or, if that's not possible, at least prevent the cleanout hit from catching you unawares, by maybe performing a full scan of the table immediately FOLLOWING the completion of the load (NOT during it). If you go down this latter route, and the table that you loaded has indexes on it that you didn't rebuild after the load, then you might also consider issuing SELECTs that will cause full index scans of these indexes, so that you suffer the index block cleanout in one go, too!

The frequent commits that you mention won't help reduce the block cleanout; it'll just save extra work for any other users that are reading this table whilst the load is running (by avoiding rollback segment access). Doubling the block size may halve the cleanout hit, but that's a guess.

Sorry that this isn't much help...

-Tony

>
>========================================================================
> Bob Manieri email : rmanieri_at_novalink.com
> Database Administrator voice : (302) 594-4163
> First USA Bank, Wilmington, Delaware pager : (609) 342-0314
>========================================================================

Tony Rothwell                                rothwelt_at_heac004.gb.ec.ps.net