Re: Index creation - performance problem

There are two different forms of block cleanout, the original type that you are seeing here, and the 'delayed-logging' block cleanout which occurs as a consequence of the 'fast-commit' algorithm introduced some time around 7.2.3.

In your case, you are importing a very large table, and committing at the end of the import. Consequently most of the blocks that make up the table are written to disc long before the actual COMMIT occurs.

Some of the in-buffer table blocks (I think the limit may be 400 or 10% of the buffer, but haven't checked that) are marked as committed, but the rest of the buffer, and those blocks already on disc are not marked.

The next process to read those blocks see that there is a 'pending' transaction on the block and checks to see whether or not
the transaction has committed. Finding that the transaction has committed, it cleans the block - which means it sets a commit number, clears lock flags etc. Because this changes the block, the block has to be written out to disc in the normal course of buffer flushing or checkpointing.

In your case, it is the index creation process that keeps finding these dirty blocks.

One option for reducing this problem is
to set a reasonable sized buffer for the import, and then commit on each array insert. Suitably chosen you will get good performance, reduce rollback costs, and get all the blocks marked with a fast commit, and not have the subsequent cleanout take place.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Adam Roze wrote in message <38CCC838.319D35FE_at_royalpbk.com>...


>It may be block cleanout. When select count(*) is executed just after the

>table was imported oracle reads & writes.

>As I understand block cleanout applies to data block in buffer cache that

>have not been written to disk yet ??? (correct me if wrong) while oracle

>reads/writes to the table (6GB)  that is much bigger than buffer cache

>(384MB).

>Next time I will try to import just tables then restart the database and

>recreate indexes.

>

>Adam

>

>Jonathan Lewis wrote:

>

>> It IS block cleanout.

>> Try a select count(*) from table

>> before creating the index.

>>

>> --

>>

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>