Re: Question about spreading I/O accross multiple datafiles for a tablespace

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.

> - 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 Fri Sep 17 2004 - 16:00:28 CDT