Re: Extent Size on Table Performance Question

On May 13, 5:36 pm, sybra..._at_hccnet.nl wrote:
> On Sun, 13 May 2007 17:07:21 -0500, "Dereck L. Dietz"
>
>
>
>
>
> <diet..._at_ameritech.net> wrote:
> >I've been reading and came across a section about the impact of table extent
> >size on performance.
>
> >I'd like to apply what I read but before I go off and do it wrong I thought
> >I'd post what I'm considering and see what observations/comments/suggestions
> >anybody would like to share.
>
> >Basic assumptions:
>
> >1. Operating system I/O buffer size is 128K.
> >2. NEXT extent size will be 128K.
> >3. During table loads/reloads the table size will not grow to where it
> >needs more than one more extent.
> >4. The table's initial size will be 55,433 rows for a current size of
> >7,737,465 bytes.
> >5. Datawarehouse environment. No real data design/normalization.
> >6. Oracle 10.2.03, Windows 2003 Server, 16GB memory.
>
> >When I initially create the table, what would be the best option:
>
> >1. Create it with an initial extent of 7,737,465 bytes allocating all the
> >space it needs at once.
>
> >2. Create it with an initial extent as a multiple of 128K but large
> >enough to hold the initial rows.
>
> >3. Just create it with an initial extent of 128K.
>
> The best option would be
> 1 forget about this old myth
> 2 In a LMT: just create the table and stop worrying. Your extent
> clause will be recalculated in terms of tablespace extent sizes
> automagically anyway.
> 3 Consider migrating to a real operating system.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Dereck, table and index sizing decisions as to number and size of extents pretty much have to be made only for objects stored in dictionary managed tablespaces. Since Oracle introduced Locally managed tablespaces the number and size of extents is determined by the database for you based on the tablespace definition.

Locally managed tablespace extent allocation is fully explained in the Concepts and DBA Administration manuals. All your tablespaces should be using local managment.

HTH -- Mark D Powell -- Received on Sun May 13 2007 - 18:58:11 CDT