Re: locally managed tablespaces performance

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 16 Oct 2004 09:57:59 +1000
Message-Id: <417064a4$0$20128$afc38c87@news.optusnet.com.au>

Ed Stevens wrote:

> On 12 Oct 2004 00:03:08 -0700, hopehope_123_at_yahoo.com (utkanbir)
> wrote:
>

>>Hi ,
>>
>>My system is a datawarehouse. I use locally managed tablespaces with
>>extent size of 1MB on OCFS . I have large tables , and since these
>>tables reside in locally managed tablespaces , with 1MB extent size ,
>>i see lots of extents allocated. For instance one of the tables has
>>about 6000 extents .
>>
>> Is there any performance impact of having too many extents?  I
>>usually use parallel query and parallel query baypasses the buffer
>>cache. But , when i monitor the buffer pool , i see some of the blocks
>>are cached. It seems that these blocks are the first or second blocks
>>of each extent allocated to the table . (And these are read by using
>>db seq.scan not direct path read ) Why are these blocks read by using
>>db seq scan and cached? I guess these are the bitmap blocks which
>>defines the extent but i really wonder the truth.
>>What happens internally when oracle reads a table? How does it clarify
>>the blocks that are read ?
>>
>>Does having a large number of extent impact performance ?
>>
>>Kind Regards,
>>hope

>
> Here's my take. Others may correct me if I have something wrong ...
>
> An extent is nothing but a *logically* contiguous set of data blocks
> that have been reserved for a particular segment (table, index, etc.).
> The performance hit involved with extents comes when one fills up and
> another has to be acquired. Thus, one could argue for larger (and
> thus fewer) extents so that new ones have to be acquired less often.
> Is the aquisition of a large extent more expensive than a small one?
> I don't know. On the other hand, overly large extents *can* be seen
> as a waste of disk space. Acquisition of new extents only occurs when
> data is being added to a segment ... not on reads.
>
> If you look at the makeup of a row-id, you'll find no reference to
> segment id, and Physical I/O is done by blocks, not by segments, so I
> find it very difficult to believe that the number of segments has any
> impact on any form of a read operation.
>
> But if you are using LMT with autoallocate, you can quit worrying
> about any of this.

Largely, but not quite.

In the first place, the OP might like to read http://www.dizwell.com/html/extent_numbers.html

Secondly, and specifically because you mentioned it at the end of your reply, be aware that LMT and ASSM makes worrying about extent numbers a valid sport once more. The more extents you have, the more bitmap blocks there are, and the more bitmap blocks you have, the more Oracle overhead there is. And that's potentially buffer cache overhead where it hurts, and not just disk space, where it isn't supposed to matter.

Of course, LMT *and autoallocate* and ASSM re-addresses some of that concern, because the autoallocate algorithm will be doing its best to increase extent sizes and hence reduce the growth in extent numbers. That combination, of course, is precisely what you mentioned.

But I'd still have nerves about LMT with autoallocate *and ASSM*. Which may not be an issue for the OP, of course. But on rare occasions, in the presence of ASSM, it is possible that the manual creation of an LMT with DBA-determined huge extent sizes might work out better than relying purely on autoallocate.

Regards
HJR   Received on Fri Oct 15 2004 - 18:57:59 CDT