Re: type of striping

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 08 Dec 2004 22:00:22 +1100
Message-ID: <41b6dec4$0$9390$afc38c87@news.optusnet.com.au>

Holger Baer wrote:
> Howard J. Rogers wrote:
> [snip]
>

>>
>>
>> Just thought I'd mention that in fact, things were and are, precisely
>> the opposite way around. In a multi-file tablespace in 8.0, for example,
>> Oracle would allocate the initial extent on file 1, the next on file 2,
>> the next on file 3, then back to file 1, 2, and so on. It would, in
>> short, "round robin" the extents.
>>
>> In locally-managed tablespace, it hammers disk 1 to death and only moves
>> on to disk 2 when no more space is available on disk 1 *IF* the
>> tablespace is AUTOALLOCATE LMT. If you do UNIFORM SIZE LMT, then the
>> round robin behaviour is unchanged.
>>
>> As this little test proves (done on 9i, so LMT is the default):
>>
>> SQL> create tablespace t
>>   2  datafile '/oracle/oradata/sapphire/t1.dbf' size 5m,
>>   3  '/oracle/oradata/sapphire/t2.dbf' size 5m,
>>   4  '/oracle/oradata/sapphire/t3.dbf' size 5m
>>   5  autoallocate;
>>
>> Tablespace created.
>>
>> SQL> create table T1 tablespace T as select * from dba_objects;
>>
>> Table created.
>>
>> SQL> select file_id from dba_extents
>>   2  where segment_name='T1';
>>
>>    FILE_ID
>> ----------
>>          6
>>          6
>>          6
>>          6
>>          6
>>          6
>>          6
>>          6
>>          6
>>
>> [So, file 6 hammered to death in the autoallocate tablespace, and the 
>> other two files don't get a look-in]

>
>
> [another snip]
>
> Howard, I think you run into a trap others also got caught in: Could you
> please check what happens if your table grows beyound 1 MB? For all my
> tests I've found (initially I believe I read it in a post by Jonathan
> Lewis)
> that after the first 1MB of extent allocation, the expected round robin
> behaviour
> kicks in.
>
> I've created a table as in your example on 10g (4k blocksize), and this
> are my results:
>
> sys_at_DEMO10G>select file_id, extent_id, bytes, blocks from dba_extents
> where segment_name = 'T1';
>
> FILE_ID EXTENT_ID BYTES BLOCKS
> ---------- ---------- ---------- ----------
> 10 0 65536 16
> 10 1 65536 16
> 10 2 65536 16
> 10 3 65536 16
> 10 4 65536 16
> 10 5 65536 16
> 10 6 65536 16
> 10 7 65536 16
> 10 8 65536 16
> 10 9 65536 16
> 10 10 65536 16
> 10 11 65536 16
> 10 12 65536 16
> 10 13 65536 16
> 10 14 65536 16
> 10 15 65536 16
> 11 16 1048576 256
> 9 17 1048576 256
> 10 18 1048576 256
> 11 19 1048576 256
> 9 20 1048576 256
>
> You'll notice that after 16 extents the round robin allocation kicks in
> just as it's expected.
>
> Regards,
> Holger

You're quite right Holger, and I'd forgotten that. We had this discussion a few ages back, as I recall (and I think I'd forgotten it then, too!). It makes rather a big difference, doesn't it? It means Fabrizio's should be even less concernsed about autoallocate tablespace than I was suggesting.

Thank you for the correction.

Fabrizio: I hope you noticed Holger's input.

Regards
HJR Received on Wed Dec 08 2004 - 05:00:22 CST