Re: Poor mans RAID10...

Ah well. There you go.

Learn something new every day. And make mistakes all the time, too.

Another test:

SQL> create tablespace test2
  2 datafile 'c:\oracle\ora92\lx92\blah201.dbf' size 5m,   3 'c:\oracle\ora92\lx92\blah202.dbf' size 5m   4 extent management local
  5 uniform size 64k;

Tablespace created.

SQL> create table blah2 tablespace test2   2 as select * from emp;

Table created.

SQL> alter table blah2 allocate extent;

Table altered.

SQL> / (repeat ad nauseam)

SQL> select file_id from dba_extents where segment_name='BLAH2';

   FILE_ID

         9
        10
         9
        10
         9
        10
         9
        10

And you are right, too, that from the 17th extent onwards, even my original table (which was indeed in autoallocated tablespace) starts round-robining. More to the point, perhaps, is that autoallocate is capable of dispensing with the 64K extents if your initial request is sufficiently large, and if that happens, the round-robin behaviour kicks in immediately:

SQL> create table blah tablespace test storage (initial 8M next 1M)   2 as select * from emp;

Table created.

SQL> select file_id from dba_extents where segment_name='BLAH';

   FILE_ID

         7
         8
         7
         8
         7
         8
         7
         8

Cheers Jonathan. An important caveat to the 'round-robin' theory, nevertheless. And a quirk which I've put up with for a couple of versions now finally explained.

Regards
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:c35e1i$ik7$1_at_hercules.btinternet.com...
>
> Note in line
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> April 2004 Iceland
> June 2004 UK - Optimising Oracle Seminar
>
>
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:405614d5$0$3957$afc38c87_at_news.optusnet.com.au...
> >
> >
> > That has not been true since 8i, if memory serves. It definitely isn't
> true
> > in 9i.
> >
> > The algorithm now appears to be to fill one disk up first, and then move
> on
> > to the other. It is probably subtler than that, but it's not round
robin.
> >
> > Proof?
> >
> > SQL> create tablespace test
> > 2 datafile 'c:\oracle\ora92\lx92\test01.dbf' size 5m,
> > 3 'c:\oracle\ora92\lx92\test02.dbf' size 5m;
> >
>
> Can I take a guess that you are defaulting to
> a locally managed tablespace with system
> managed allocation ? (I can never remember what
> the defaults are, and what DBCA kicks you with
> these days).
>
> > Tablespace created.
> >
> > SQL> create table blah tablespace test
> > 2 as select * from emp;
> >
> > Table created.
> >
> > SQL> alter table blah allocate extent;
> >
> > Table altered.
> >
> > SQL> /
> >
> > <Repeat several times>
> >
> > SQL> select file_id from dba_extents
> > 2 where segment_name='BLAH';
> >
> > FILE_ID
> > ----------
> > 7
> > 7
> > 7
> > 7
> > 7
> > 7
> > 7
> >
> > 7 rows selected.
> >
> > This behaviour has always puzzled me, since I am fairly certain that in
> 8.0
> > and before the extents really *were* distributed alternately between the
> two
> > datafiles (or round-robined in a multi-file tablespace).
> >
>
> If you are on autoallocate / system allocated extent sizes, then the
> first 16 extents (i.e. 1M) go into the first file, after which you get
> the round-robin. (Unless it has changed since I last tested it, which
> was some time ago)
>
>
>
Received on Mon Mar 15 2004 - 17:49:56 CST