| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How are extents allocated in a multi-datafile tablespace
Maybe the test should also be run using minextents when the table is created
just to be sure...
Jeffery Stevenson
Chief Databeast Tamer
Medical Present Value, Inc.
Austin, TX
-----Original Message-----
From: Rachel Carmichael [mailto:carmichr_at_hotmail.com]
Sent: Wednesday, December 27, 2000 9:31 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: How are extents allocated in a multi-datafile tablespace
Brian is right, at least from what I understand about 8i....
I was told that this round-robin approach is the way allocation is now done.
>From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: How are extents allocated in a multi-datafile tablespace
>Date: Wed, 27 Dec 2000 06:40:27 -0800
>
>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).
>
>
>__________________________________________________
>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: Rachel Carmichael INET: carmichr_at_hotmail.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-LReceived on Wed Dec 27 2000 - 10:30:58 CST
 |
 |