Re: Fooling Oracle re: extent allocation

Svend Jensen <Svend.S.Jensen_at_it.dk> wrote in message news:<3f1c394f$0$32467$edfadb0f_at_dread16.news.tele.dk>...
> Jared wrote:
> > Normally when one is creating a table in a striped tablespace one can,
> > with a little trouble, force extent allocation in a round-robin
> > fashion. I never do things the easy way :-); what I want to do (in an
> > 8.1.7.4.1 db) is create a striped tablespace and do an 'alter table
> > move' and get somewhat balanced extent allocation over four datafiles;
> > more important, get balanced allocation going forward.
> >
> > I am trying to do this with some (hash-)partitioned tables and some
> > non-partitioned.
> >
> > I have yet to devise a reasonable way to do this. I've come up with
> > some schemes - e.g., setting the striped tablespace just slightly
> > larger than the table, moving it, then setting datafiles to autoextend
> > - but they always have the flaw of getting round-robin allocation
> > afterwards. Unfortunately this is a production, third-party
> > application so a programmatic change for inserts is out of the
> > question.
> >
> > If anyone has done this, or can point me in a useful direction for
> > further reading or research, I would be grateful.
> >
> > TIA -
> >
> > Best regards,
> > jh
> Hi jh
>
> You are working hard on a problem that does not exist!
> Oracle will automatic stripe the extents for you.
> If you create a tablespace locally managed uniform size
> and the tablespace is extended with - say - tree more datafiles,
> sized like the first datafile.
> Then your objects in that tablespace will have extents that spawn all
> datafiles if they have more extents than you have datafiles. First
> extent will go on file 'a', next extent of the same object will go on
> datafile 'b' and so forth. It might not be quite what you wanted, but it
> is close.
> This behavior is seen on windos versions of 8.1.7 - and i think linux
> too, but don't nail me on that.
>
> You can test that pretty easy, create small tablespace as described,
> and a table (with small extents) from select * from all_objects. Insert
> as select from.... a couple of times to get more extents.
> Then check the dba_extents for your object, look for file_id and
> relative_fno.
>
> Regards
>
> /Svend Jensen

JH, there is no real value that I can see from attempting to force Oracle to stripe the extent allocations accross files. If RAID-0 or RAID-5 are in use then the files are stripped accross disks to begin with. If no stripping is in effect then unless the files are on different disks you are not gaining anything either.

Meanwhile since at least 7.3 Oracle automatically attempts to stripe extents accross the files that make up a tablespace at least when you load an empty tablespace, and for already loaded tablespace where space exists in multiple files and you insert enough data to fill multiple extents Oracle will spread them. But with an existing loaded tablespace the location of available free space is the most important factor to where extents are taken from.

IMHO there are better ways for a DBA to be spending their time.

• Mark D Powell --