What do you mean by the "first one on the list?" The list ordered
how? The order in which dynamic extent allocation is reading fet$
appears to favor distributing extents equally among files.
--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton
On Sat, 21 Jul 2001, Jon Walthour wrote:
> Kirti and others:
>
> Actually, Oracle only uses a round-robin extent allocation method for direct
> loads and parallel CTAS operations. For dynamic extent allocation, Oracle
> doesn't fill up one file and then the next. Actually, Oracle doesn't
> distinguish between datafiles, but rather looks at the total free space
> extents for the tablespace as listed in SYS.FET$ (Oracle's free extent
> cache). Basically, Oracle will look for a free extent equal to the size of
> the one it needs to allocate(see Doc ID #69343.1 on MetaLink of details on
> Oracle's extent algorithm). The first one on the list, regardless of the
> datafile, gets picked first. If one of exact size doesn't exist, Oracle will
> then split an existing larger extent. This larger extent, again, will be the
> first one found in SYS.FET$ which can provide the necessary space. So, it
> can at times appear to be round-robin allocation or one-file-at-a-time when,
> in point of fact, it is much more complex.
>
> Jon Walthour
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, July 20, 2001 6:30 PM
>
>
> > Jeremiah is right. Depending on the version of the database this is an
> > imaginary problem.
> > I have been using this 'auto round robin' feature since 8.0.6 (HP-UX
> > 32-bit). Just did quite a bit of re-organization involving partitioning a
> > large table and used this auto round robin extent allocation feature to
> > spread out I/O...
> >
> > Anyone seen this in any lower versions ??
> >
> > Cheers!
> >
> > - Kirti Deshpande
> > Verizon Information Services
> > http://www.superpages.com
> >
> > > -----Original Message-----
> > > From: Jeremiah Wilton [SMTP:jwilton_at_speakeasy.net]
> > > Sent: Friday, July 20, 2001 4:57 PM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re: Extent allocation
> > >
> > > On Fri, 20 Jul 2001, Adrian Roe wrote:
> > >
> > > > Is there any way to get Oracle (816) to do round robin extent
> > > > allocation eg. if a tablespace has 4 data files and each file is on
> > > > a different disk, can extents be allocated from each file in
> > > > sequence ? As I understand, Oracle will fill one file and then go
> > > > onto the next file.
> > >
> > > It appears we may be discussing an imaginary problem. At least on the
> > > version I am using (8.1.6.2 HP-UX 64 bit), Oracle round robins among
> > > files with available space automatically. It does not just fill up
> > > one datafile and move on to the next.
> > >
> > > I did a little experiment:
> > >
> > > SQL> create tablespace jeremiah_temp1
> > > 1 datafile '/tmp/jeremiah_temp-01.dbf' size 10m,
> > > 2 '/tmp/jeremiah_temp-02.dbf' size 10m,
> > > 3 '/tmp/jeremiah_temp-03.dbf' size 10m,
> > > 4 '/tmp/jeremiah_temp-04.dbf' size 10m;
> > >
> > > Tablespace created.
> > >
> > > SQL> create table foobar
> > > 1 (baz varchar2(10))
> > > 2 storage (initial 32k next 32k pctincrease 0 maxextents 4)
> > > 3 tablespace jeremiah_temp1;
> > >
> > > Table created.
> > >
> > > SQL> insert into foobar (baz)
> > > 1 select substr(trash,1,10) from garbage where rownum <= 6000;
> > >
> > > 6000 rows created.
> > >
> > > SQL> commit;
> > >
> > > Commit complete.
> > >
> > > SQL> select segment_name, e.bytes/1024 kb, file_name
> > > 1 from dba_extents e, dba_data_files d
> > > 2 where e.file_id = d.file_id
> > > 3 and e.segment_name = 'FOOBAR';
> > >
> > > SEGMENT_NAME KB FILE_NAME
> > > ------------------------------ ---------- ------------------------------
> > > FOOBAR 32 /tmp/jeremiah_temp-01.dbf
> > > FOOBAR 32 /tmp/jeremiah_temp-02.dbf
> > > FOOBAR 32 /tmp/jeremiah_temp-03.dbf
> > > FOOBAR 32 /tmp/jeremiah_temp-04.dbf
> > >
> > > So, it looks like it "round robins" automatically, and there is no
> > > need to do so manually. I don't know which version of Oracle was the
> > > first to do this.
> > >
> > > --
> > > Jeremiah Wilton
> > > http://www.speakeasy.net/~jwilton
> > >
> > > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Deshpande, Kirti
> > INET: kirti.deshpande_at_verizon.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: Jon Walthour
> INET: jonw_at_fuse.net
>
> 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: Jeremiah Wilton
INET: jwilton_at_speakeasy.net
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 Sat Jul 21 2001 - 09:35:47 CDT
