Re: Extent allocation

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