Re: Preventing delayed block cleanout due to loading a datawarehouse

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

Randolf,
I misunderstood what he meant, with my test I simply wanted to demonstrate the
difference, the delayed block cleanout using inserts in direct path, compared to a conventional insert.
I am aware of the existence of a mechanism of "SCN caching" but honestly
I have helped to identify a" flaw " in the reasoning I used in my tests and
for this, thank you a lot.

In summary, then ..

settembrino_at_ORA11> create table t (x varchar2(4000), y varchar2(4000), z varchar2(4000), w varchar2(4000));

Table created.

settembrino_at_ORA11>insert /*+ append */ into t   2 select lpad('x', 4000, 'x'), lpad('y', 4000, 'y'), lpad('z',4000, 'z'), lpad('w', 4000, 'w')
  3 from dual
  4 connect by level <= 100;

100 rows created.

settembrino_at_ORA11>select * from t;
select * from t

              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

settembrino_at_ORA11>commit;

Commit complete.

• control the values of the moment for the statistics "cleanouts only - consistent read gets" and "Commit SCN cached"

settembrino_at_ORA11>select n.name, m.value   2 from v$mystat m, v$statname n
  3 where m.STATISTIC# = n.STATISTIC#
  4 and n.name in ('cleanouts only - consistent read gets' , 'Commit SCN cached');

NAME                                                       VALUE
----------------------------------------------------------------
----------
Commit SCN cached                                           4
cleanouts only - consistent read gets                   11

• I run a full table scan on table

select count(*) from t ;

COUNT(*)

          100

• At this point I capture the image of these blocks in buffer cache

settembrino_at_ORA11>select v.dirty, count(*)   2 from v$bh v
  3 where v.objd = (select object_id from user_objects where object_name = 'T')
  4 group by v.dirty;

D COUNT(*)
--- ---------------

Y               8
N              99

• and note that only one block is dirty (I think that the other seven (8-1) are those used by ASSM to manage the space)
• after the full table scan, I check the values of the moment for the statistics "cleanouts only - consistent read gets" and "Commit SCN cached"

settembrino_at_ORA11>select n.name, m.value   2 from v$mystat m, v$statname n
  3 where m.STATISTIC# = n.STATISTIC#
  4 and n.name in ('cleanouts only - consistent read gets' , 'Commit SCN cached');

NAME                                                       VALUE
----------------------------------------------------------------
----------
Commit SCN cached                                           5
cleanouts only - consistent read gets                   12

I think that's you want to tell me in your reply, or am I wrong?

Regards,

Donatello Settembrino Received on Mon Apr 18 2011 - 02:59:01 CDT