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

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 18 Sep 2004 10:31:52 +1000
Message-ID: <414b81f2$0$29780$afc38c87@news.optusnet.com.au>

JEDIDIAH wrote:

> On 2004-09-17, Howard J. Rogers <hjr_at_dizwell.com> wrote:

>> G Dahler wrote:

> [deletia]


>>> 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.
>
> Oh, that's all?

In terms of the information content provided by the OP's post, yes, that's all.

>>
>> 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.
>
> Better yet, script these changes based on actual workloads.

What changes are you talking about? Why would getting a table into the right tablespace, in the context of this post, have anything to do with workloads?

> Then you 
> won't have to worry about guessing wrong when you try and tweak 100's of
> create statements.

The point is, there's no tweaking involved, and there are no 'wrong guesses' in practice, because there are no *consequences* from guessing wrong.

Regards
HJR > [deletia]
> Received on Fri Sep 17 2004 - 19:31:52 CDT