Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How are extents allocated in a multi-datafile tablespace

RE: How are extents allocated in a multi-datafile tablespace

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Wed, 27 Dec 2000 10:30:58 -0600
Message-Id: <10723.125361@fatcity.com>


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).



Get your FREE download of MSN Explorer at http://explorer.msn.com
-- 
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-L
Received on Wed Dec 27 2000 - 10:30:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US