Re: Preventing delayed block cleanout due to loading a datawarehouse
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