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

On 2004-09-18, Howard J. Rogers <hjr_at_dizwell.com> wrote:
> 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?

        After the objects have been accessed in their deployed environment for at time you will truely know which ones would be appropriate to each type of tablespace. You could even do this in a test environment and extract the results.

>
>> 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.

        Sure there are. All of your most active objects could end up in the same tablespace. Depending on the underlying storage architecture this could be very significant.

        You simply don't need to put tablespace details in the inital DDL script. That can be changed fairly easily once you deploy the schema. You just need a maintenance outtage.

        The process need not be manual.

-- 

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