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

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?

>
> 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. Then you won't have to worry about guessing wrong when you try and tweak 100's of create statements.

[deletia]

-- 

	vi isn't easy to use.				 |||
							/ | \
	vi is easy to REPLACE.