Re: Preventing delayed block cleanout due to loading a datawarehouse

From: Donatello Settembrino <donatello.settembrino_at_gmail.com>
Date: Tue, 19 Apr 2011 04:08:45 -0700 (PDT)
Message-ID: <d766314d-3715-4c88-832e-003671a62a8a_at_i39g2000prd.googlegroups.com>



On 18 Apr, 23:30, Randolf Geist <mah..._at_web.de> wrote:
> On 18 Apr., 09:59, Donatello Settembrino
>
> <donatello.settembr..._at_gmail.com> wrote:
> > I think that's you  want to tell me in  your reply,
> > or am I wrong?
>
> 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

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_at_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_at_ORA11>select count(*) from t;

  COUNT(*)


       100

settembrino_at_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_at_ORA11>set serveroutput on; settembrino_at_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_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

Regards,

Donatello Settembrino Received on Tue Apr 19 2011 - 06:08:45 CDT

Original text of this message