Re: Preventing delayed block cleanout due to loading a datawarehouse

From: Randolf Geist <mahrah_at_web.de>
Date: Fri, 15 Apr 2011 05:32:37 -0700 (PDT)
Message-ID: <7d38c256-a2ca-475d-a661-3b8099077de2_at_r6g2000vbz.googlegroups.com>



On Apr 14, 4:58 pm, Donatello Settembrino <donatello.settembr..._at_gmail.com> wrote:
> Hi Randolf,
> I do not think that this happens

I depends on what you exactly mean by "this" means. Note I didn't say that delayed block cleanout will happen in the same fashion as for conventional DML, but you simply can take some block dumps after the direct-path insert and you'll see that there is no commit SCN available from the ITL slots, so the block is not really "clean".

If you check the session statistics of your session running the query after committing the direct-path insert you'll notice that the first time the session accesses a block from the table it will perform a block cleanout and generate redo for that single block, from then on it will re-use the "cached" commit SCN for accessing / processing the remaining blocks.

The corresponding statistics are:
cleanouts only - consistent read gets (increases by one after first access in the session)
Commit SCN cached (increases depending on the number of blocks accessed)

That is also the explanation why you don't see a difference in the amount of redo generated if you use a larger table - it will still do a cleanout of a single block to determine the commit SCN.

But you're right that my explanation above was not really sound regarding direct-path inserts and cleanout since running a query afterwards does not really clean out every block.

Regards,
Randolf Received on Fri Apr 15 2011 - 07:32:37 CDT

Original text of this message