On Apr 16, 9:43*pm, j.w.vandijk.removet... (AT) hetnet (DOT) nl (Jaap W. van
Dijk) wrote:
Well, the basic idea is that you clean the blocks out while the
required information is still in the undo available, so it's all about
timing in principle.

But how about providing more details about your particular case:

1. Do you have indexes enabled during the load or not?
2. Are you sure that the insert is actually a direct-path insert?
There are various reasons why Oracle might silently fall back to
conventional insert without any notice.

You can check the latter by looking at the amount of undo generated
since a direct-path insert will not generate undo (provided that no
indexes are enabled during the load, otherwise undo will be generated
by the necessary index maintenance) or by simply attempting to query
the table after the insert but before the commit - any attempt to
access a table after a direct-path insert within the same transaction
will error out with "ORA-12838: cannot read/modify an object after
modifying it in parallel"

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On 15 Apr, 14:32, Randolf Geist <mah... (AT) web (DOT) de> wrote:



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@ORA11> create table t (x varchar2(4000), y varchar2(4000),
z varchar2(4000), w varchar2(4000));

Table created.


settembrino@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@ORA11>select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in
parallel


settembrino@ORA11>commit;

Commit complete.


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

settembrino@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@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@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

On 18 Apr., 09:59, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:
Yes, quite exactly. You might want to check your test case setup -
your table and data definition very likely causes chained rows and I
wonder if this has an impact on the number of dirty blocks found.

Randolf

On 18 Apr, 23:30, Randolf Geist <mah... (AT) web (DOT) de> wrote:
Randolf,
I do not think that in my tests are chained rows.
If I look at the statistics before and after the
full table scan

settembrino@ORA11>select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name in ('table scan rows gotten', 'table fetch continued
row', 'table scan blocks gotten');

NAME
VALUE
----------------------------------------------------------------
----------
table scan rows gotten
984564
table scan blocks gotten
14699
table fetch continued
row 17


settembrino@ORA11>select count(*) from t;

COUNT(*)
----------
100


settembrino@ORA11>select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and a.name in ('table scan rows gotten', 'table fetch continued
row', 'table scan blocks gotten');

NAME
VALUE
----------------------------------------------------------------
----------
table scan rows gotten
984664
table scan blocks gotten
14799
table fetch continued
row 17


which confirms that there are no chained rows.

Moreover, using the procedure of Thomas Kyte to
monitor the space used, observe:

settembrino@ORA11>set serveroutput on;
settembrino@ORA11>exec show_space('T', 'SETTEMBRINO', 'TABLE');
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 0
Full Blocks ..................... 100
Total Blocks............................ 128
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 21
Unused Bytes............................ 344,064
Last Used Ext FileId.................... 40
Last Used Ext BlockId................... 861,248
Last Used Block......................... 43

PL/SQL procedure successfully completed.

which means that have been allocated 128 blocks,
100 blocks are full("Total Blocks", 1 row
for block), 21 are unused ("Unused Blocks") and
7 (128-100-21) are those used by ASSM to manage
the space

which are also the seven dirty blocks additional
in buffer cache(at least I think)

settembrino@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

Regards,

Donatello Settembrino

On Apr 19, 1:08*pm, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:
Thanks, I assumed a 8KB default block size but you seem to have 16KB
according to the output, given the default PCTFREE of 10 (I don't see
any non-default setting in your CREATE TABLE statement) your 4 times
4000 bytes must be very close to the limit of free space available in
a 16KB block...

Randolf

On 19 Apr, 14:27, Randolf Geist <mah... (AT) web (DOT) de> wrote:
Exactly, that's right

On Apr 19, 2:51*pm, Donatello Settembrino
<donatello.settembr... (AT) gmail (DOT) com> wrote:
By the way - we digress - my comment about PCTFREE is irrelevant in
this case since Oracle will stuff this row into a single block no
matter what the PCTFREE setting is as long as the row fits into the
block.

And: You don't have chained rows but your test case is flawed: A
select count(*) never reports "table fetch continued row" because it
does not have to visit the columns but only accesses the row entries
in the row directory. So even with chained rows your test will not
report any "table fetch continued row" statistics. You would need to
count/access a column that is part of the "chained" row pieces - and
it would have to be nullable, otherwise recent versions of Oracle will
transform a count(col) to a count(*).

Randolf

On 20 Apr, 11:37, Randolf Geist <mah... (AT) web (DOT) de> wrote:
Thanks for the correction,
when I did the second time the test
I forgot to change the query
(select count (col) from table) then,
the test may be incorrect
for the reasons you highlighted.


Regards,
Donatello