Adamenos, thanks for your showing.
adamenos_at_yahoo.com (adamenos) wrote in message news:<4e0e67ef.0409181701.a26c576_at_posting.google.com>...
> If you are using automatic space management and locally managed
> tablespaces then you will find that a new algorithm is being used to
> allocate extents. What you should find is that only one datafile will
> get filled up to a certain point before Oracle will start utilizing
> extents in the tablespace's other datafiles. At some point (you'll
> have to monitor your tablespace and it's datafiles to find out the
> exact point), Oracle will begin to kick into the old, expected
> round-robin behavior and grab extents from the other datafiles.
>
> yls177_at_hotmail.com (yls177) wrote in message news:<c06e4d68.0409171944.1dd76341_at_posting.google.com>...
> > "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<414b5067$0$20127$afc38c87_at_news.optusnet.com.au>...
> > > G Dahler wrote:
> > >
> > > > Hi,
> > > >
> > > > I searched the internet, and I wanted to confirm somthing with the
> > > > experts.
> > > >
> > > > Suppose you create a tablespace using 'x' datafiles instaed of a single
> > > > one. Will oracle allocate extents in a round robin manner accross all
> > > > datafiles ?
> > > >
> > > > According to what I've read:
> > > >
> > > > - If you use a locally managed tablespace with an UNIFORM extent size, it
> > > > will allocate extents in a round robin manner
> > >
> > > Tick.
> > >
> > > > - If you use a locally managed tablespace with AUTO allocation, it will
> > > > NOT allocate extents in a round robin manner, that is, it will fill the
> > > > first datafile and then proceed to the next
> > >
> > > Tick.
> > >
> >
> > The difference between the UNIFORM extent size and AUTO allocation
> > will affect
> > the contention of the tablespace since the latter will just fills up
> > the first datafile while the UNIFORM will allocate in a round robin
> > manner?
> >
> > > > - I have absolutely no idea what will happen in the case of dictionnary
> > > > managed tablespaces.
> > >
> > > You shouldn't be using DMTs, so the question is moot (but alright, it round
> > > robins).
> > >
> > > > The reason I'm asking this, is that I have a big application here (An ERP
> > > > system) that has numerous tablespaces. I have many disks. When the ERP
> > > > was implemented, it was decided to separate the tables for GL, AR, AP
> > > > etc... in different tablespace. Index tablespaces were also separated.
> > > >
> > > > The problem is that the ERP vendor sometims send us scripts to change the
> > > > data model (add tables, index, etc...) but the scripts are generic and do
> > > > not take in consideration the tablespace or (worst) the storage
> > > > parameters.
> > >
> > > So, unless you carefully pre-edit the scripts, you end up with segments
> > > created in the wrong place, and with silly storage parameters? (I have to
> > > say, though, that the lack of storage parameters when you create a table is
> > > a thoroughly good idea: you should always let a tablespace's default
> > > storage clause do its magic. LMTs make that happen automatically, of
> > > course. But it was also best practice with DMTs).
> > >
> > > > To alleviate the problems, I rebuilt the database and used locally managed
> > > > tablespacs with auto allocation to fix the storage parameter problems.
> > >
> > > Perfect. But now you will be clobbering all your datafiles one at a time
> > > instead of round-robin-ing them (I doubt that's a verb, but whatever...).
> > >
> > > > I
> > > > still have to move things around and rebuild index after running the
> > > > vendors scripts, but it's less painful than before.
> > > >
> > > > I was thinking of creating just a couple of tablespaces (LMT, auto
> > > > allocation) now (one data, one index) and spread the load accross many
> > > > disks. I do not have hardware raid, the disks are mirrored with sun volume
> > > > manager (aka disksuite, I know there is a write penalty but I can live
> > > > with that)
> > > >
> > > > But according to what I read, if I try to to this, and use LMT with auto
> > > > allocation, oracle will not spread the load. I could use uniform extent
> > > > size, but I would then have to segregate tables by size, which will be as
> > > > worst has before when come the time to run one of the vendors scripts.
> > >
> > > No, you wouldn't. Not much, anyway. Just create 5 different tablespaces (10,
> > > if you still insist on separating indexes from tables), each using 64K,
> > > 1MB, 8MB, 64MB and 256MB extents. Those are the same extent sizings that
> > > the autoallocate method uses. Feel free to miss out some of these sizes if
> > > you never have tables that small or that big. Then just create a table in
> > > whichever tablespace seems most appropriate at the time. If you get it
> > > horribly wrong, the 'alter table X move tablespace Y' command will be your
> > > friend.
> > >
> > > So now your vendor-generic scripts arrive, mentioning no tablespace, and
> > > mentioning no storage parameters. Your job would be to add a 'tablespace X'
> > > clause to the end of each 'create...' statement, and that's it.
> > >
> > > If one of your vendor's scripts creates something in the "wrong"
> > > tablespace... who cares? Just move it to something more appropriate when
> > > you get the chance. Otherwise, leave it be... because it really won't
> > > matter if a big table has got thousands of small extents by accident.
> > >
> > > And because you're using uniform sizes, you'll get the striping effects of
> > > extents being evenly distributed around your data files.
> > >
> > > (And, I might add, your post makes it plain to me that ASM in 10g is an
> > > exceedingly-necessary technology. So thanks).
> > >
> > > Regards
> > > HJR
> > >
> > > >
> > > > Povided someone understand my bad english and my situation, does anyone
> > > > here have a good suggestion for dealing with this ?
> > > >
> > > > Thanks a lot
Received on Mon Sep 20 2004 - 11:12:09 CDT
