Re: type of striping

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 08 Dec 2004 22:13:03 +1100
Message-ID: <41b6e1bd$0$17883$afc38c87@news.optusnet.com.au>

Fabrizio wrote:

> Howard J. Rogers wrote:
> 

>> Fabrizio wrote:
>>
>>> Howard J. Rogers wrote:
>>>
>>>>
>>>> Autoallocate is probably my preferred way of creating LMTs.
>>>>
>>>
>>> From your old posts I always thought you preferred the "uniform
>>> size" for LMTs.
>>>
>>> May I ask you the advantages of autoallocate?
>>>
>>
>> Ah... I just answered you via email, because that showed up before
>> this post. Could I ask you not to email me things you are also going
>> to post to the newsgroup? That way, I don't get confused! (Happy to
>> have email for other stuff, of course). Anyway...
>>

> 
> Opss, this is because I use "reply all". I'll strip your personal email 
> from the list. My apologies.
> 
> However I haven't received any email from you. If you simply replied it 
> should have bounced back.

It might have done. But I get a lot of spam 'Mail could not be delivered'. I wouldn't notice a real one of those amongst the others.

> 

>> It's true I was always a little suspicious of autoallocate, because it
>> was very difficult to pin down precisely what extent sizes you were
>> going to get. I hesitate to recommend anything I don't really understand.
>>
>> But that changed a long time ago. I now believe that DBAs really
>> shouldn't have to worry about extent sizing at all, and autoallocate
>> takes that chore away completely. PCTINCREASE used to do something
>> similar, of course, with the same sort of 'automation' claims, but the
>> consequence was horrendous fragmentation issues... autoallocates don't
>> fragment (or exceedingly rarely, anyway).
>>
>> > May I ask you the advantages of autoallocate?
>> >
>>
>> Total automation for one.
>>
>> For another, ASSM is in widespread use, and that makes the number of
>> extents an issue again. Autoallocate minimises the number of extents.
>> Poorly chosen uniform sizes will cause grief with ASSM.
>>

>
> But what about uniform sized extents without ASSM (in a non-RAC system)?

What about them? They work. They don't fragment. I have to think about what extent sizes to use, perhaps. And if I get that decision wrong, I could suffer. But probably I'll get it right. In which case, they're fine.

>> Besides which... look at what 10g is doing with ASM, and you'll see
>> that the days of ever worrying about extents are (or should be) long,
>> long ago.
>>

> 
> I believed that with ASSM a DBA should go back to count extents... and I 
> can see fragmentation introduced by autoallocate.
> 
> These and fear about ASSM performance and bugs made me wonder if to 
> recreate some of the tablespaces to "uniform size".
> Now I'm more confused.
> 
> (I'll reserve questions on ASM for another thread).
> 

>> But I'm not religious about it. If you want to steer clear of
>> autoallocates (perhaps because of the no striping issue discussed
>> earlier, and your particular hardware) that's fine, too.
>>

>
> I have not position here. I'm trying to evaluate pros and cons.

I wasn't suggesting you had a position. I was merely pointing out that I don't particularly have one either.

Autoallocate very rarely fragments. Richard Foote's posted some good examples of that here not too long ago (search at Google). *Can* it fragment? Yes. But it's difficult to make it do so. But even if it does fragment, is that a performance problem? No... it's a waste of space issue, and you can still do coalescing of free space if it's really serious (which it won't be, probably).

The pros are, therefore, that it's entirely automatic. It frees you up from worrying about extent sizes in the future. It fits into the 10g ASM framework of total automation. It is hard to fragment. It spares you the embarrassment of having made the wrong judgement about which UNIFORM SIZE to choose. It fits with ASSM nicely.

The cons are that it can, with difficulty, fragment a bit.

That's about it as far as I can tell. (Don't forget to read Holger's post where he points out something I'd totally forgotten: that if your table is big enough (>1M), autoallocate even starts round robin-ing the extents again).

There's really very little to say against them. I think most DBAs are more likely to make damaging mistakes in extent size choice with uniform size LMTs than are ever going to suffer from choosing autoallocate.

[Going back to your original question, by the way: I have long advocated uniform sized LMTs should have 64K, 1MB, 8MB, 64MB and 256MB extents. Those are, in fact, precisely the extent sizes you'd get in autoallocated tablespace. In other words, even my old posts suggested doing things manually in a way that the automatic way would have done in any case. It's just taken me a while to realise that such a position is a bit daft: if I'm going to recommend doing what autoallocate does anyway, why not simply recommend doing autoallocate and have done with it?!]

Regards
HJR Received on Wed Dec 08 2004 - 05:13:03 CST