Re: Extent allocation

I mean just that--that Oracle will look in its free extent cache for the first free extent the tablespace that is the same size as that which it needs. There is no particular ordering first. And that would make it "appear" to be distributing extents equally among the files, but not always. Sometimes it will also look, as another lister has pointed out, as if Oracle is always going to the biggest datafile in the tablespace.

Jon Walthour
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, July 21, 2001 11:45 AM

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

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