Re: locally managed tablespaces performance

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. Received on Tue Oct 12 2004 - 08:04:34 CDT