Delayed block cleanout

From: Robert L. Manieri <rmanieri_at_novalink.com>
Date: Thu, 28 Apr 94 16:29:34 EST
Message-ID: <em9e28300028_at_novalink.com>


Does anybody out there have any experience with a phenomenom Oracle support refers to as "delayed block cleanout"?
.

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

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

Any suggestions will receive immediate consideration.


  Bob Manieri                           email : rmanieri_at_novalink.com
  Database Administrator                voice : (302) 594-4163
  First USA Bank, Wilmington, Delaware pager : (609) 342-0314
          It has been said that a DBA is a professional paranoid.
                    Just because you're paranoid,
             doesn't mean that they're not out to get you.
========================================================================
Received on Thu Apr 28 1994 - 23:29:34 CEST

Original text of this message