BLOCK CLEAN OUT

Oracle has a feature called BLOCK CLEAN OUT. When Oracle makes changes in data blocks the blocks are marked as dirty. The first time a block is read from the database (even by a SELECT) it is cleaned and put back. If you import a table all the blocks in the table are dirty. If you 'SELECT COUNT(*) FROM table' all the blocks are cleaned. If you do the same select statement again you will find that it goes dramatically faster than the first time. You can try this yourself and please note that this is NOT because the table has been cached in the SGA. The 'dirty' blocks are used because when updating a block Oracle does not know if the transaction will finally be committed to the database. When Oracle first reads a block, it may detect that it does not know if the transaction was committed so it must check in the rollback segments and if it finds nothing it can mark the block as committed. Once you've imported your tables and started the index build, Oracle has to clean the blocks which slows it down. Currently, you can't avoid block clean out. We thought we could speed up index builds while waiting for other imports to complete by counting the rows of the completed imports. When we start the index creation the tables are already clean so the index build would be faster. During our recent migration we found that running counts while loading data would dramatically slow the data loading (block cleanout alone added 6 hours to our index builds). As an example I loaded a 100 megabyte table, it took 9 minutes to load the data. The first 'Select count(*)' on the new table took 14 minutes. I then did another 'Select count(*)' on the same table and it took under 2 minutes (Yes, that's TWO MINUTES!!!).