Re: type of striping

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 Received on Wed Dec 08 2004 - 02:08:16 CST