Ed, sorry for the delay in response but I was out for the holidays. I
ran a test this morning on a database (8.1.6) I have on my laptop and I
stand by my earlier response.
create tablespace test datafile 'c:\oracle\oradata\bsw\test1.dbf' size
5M, 'c:\oracle\oradata\bsw\test2.dbf' size 5M;
create table test (test_col varchar2(20)) storage (initial 100K next
100K pctincrease 0) tablespace test;
I then ran a script which maps out where the tablespace and where the
extents are allocated. The first extent existed in file #8
(test1.dbf). I then altered the table and allocated another extent
(alter table test allocate extent;) it was allocated into file #9
(test2.dbf). I just continued to type slash and return and in another
window watched where the extents were being allocated and it was in a
round-robin approach between the two datafiles. I then added another
datafile to this tablespace #10 (test3.dbf) after there was ~40 extents
allocated, 20 in each tablespace. The last extent allocated existed in
test1.dbf, the next one I allocated went to test2.dbf and the next one
to test3.dbf (The newly created datafile). It continued on this
round-robin approach.
Try it out and let me know what you find because this is what I've seen
for quite some time now.
- Brian
- "Shevtsov, Eduard" <EShevtsov_at_flagship.ru> wrote:
> Brian,
>
> I always thought that Oracle chooses next extent from the first
> continious
> piece of free space which satisfies reqs (equal or bigger in size).
> It's
> simplification because Oracle does some rounding in order to optimize
> space
> allocation. So first it always tries to fill the first file of a
> particular
> tbs, then the second, etc. I believe the order somehow depends on
> file's
> creation/addition time. I suggest that round-robin algorithm is
> almost
> useless for extent allocation, because in that case Oracle needs to
> know
> what discs the files are located, is it raid or not and so on. But
> Oracle
> use round-robin method for assignment transactions to rbs.
>
> Ed
>
> > Whenever I've payed attention to this it has allocated them in a
> > round-robin approach amongst the various datafiles as the note
> stated.
> > I believe this is how it worked in 8.0.5 (maybe earlier) and up.
> >
> > - Brian
> >
> > --- Chuck Hamilton <chuck_hamilton_at_yahoo.com> wrote:
> > >
> > > It's always been my understanding that when a tablesapce has
> multiple
> > > datafiles, extents are allocated in 1 datafile at a time until
> that
> > > datafile is full, then it switches to the next datafile. Today I
> read
> > > a note on a metalink forum where someone stated quite
> authoritatively
> > > that extents are allocated in a striped fashion amongst the
> > > datafiles. For example if you have three datafiles, the first
> extent
> > > goes to file 1, the second to file 2, the third to file three,
> and
> > > then back to file 1. Is this true? Is this something that's new
> in
> > > one of the versions of 8i? I've never heard of such a thing. It
> would
> > > be great if that were true because it would distribute i/o more
> > > evenly. But I thought the only way to accomplish that in Oracle
> was
> > > with hash paritioning.
> > >
> > > On a similar note, I have a composite partitioned table spread
> across
> > > 3 tablespaces each with one datafile. The hash key for the
> > > subpartition is a sequentual number. When I bulk load into that
> > > tablespace, I don't see even i/o distribution amongst the data
> files.
> > > One file consistently gets 2x the # of writes as the other two.
> Why
> > > is that?
> > >
> > >
> > >
> > > ---------------------------------
> > > Do You Yahoo!?
> > > Yahoo! Shopping - Thousands of Stores. Millions of Products.
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Shopping - Thousands of Stores. Millions of Products.
> > http://shopping.yahoo.com/
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Brian Wisniewski
> > INET: brian_wisniewski_at_yahoo.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing
> Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shevtsov, Eduard
> INET: EShevtsov_at_flagship.ru
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Dec 27 2000 - 08:40:13 CST
