Re: Extent sizes

"PK" <pk26au_at_yahoo.com> wrote in message news:1127609070.433539.113230_at_f14g2000cwb.googlegroups.com...
>
> Hi,
>
> I am trying to understand optimal extent sizes when trying to create
> tablespaces and tables for 9i and higher.
>
> I created a tablespace with 64k uniform size local managed and segment
> space management auto (9.2.0.6 on HP-UX). The database block size is
> 8k.
>
> I created a table pk_temp (col_1 char(2000), col_2 char(2000)) in the
> above tablespace. Then I inserted 35 rows. Since each row is 4k in size
> and the extents are 64k in size each, I would think only two or three
> extents would be needed -considering row size overheads. But when I
> enquired in dba_extents and dba_segments views, there are 5 extents
> !!!!
>
> Any ideas why ???
>
> Thanks for your time.
> PK
>

Your rows require 4,011 bytes each.
Allowing for overheads and the default
pctfree (10%) you can only get one row
per block..

For your example:
In extent 1,

    block 1 is a level 1 bitmap (which covers extents 1 and 2)     block 2 is a level 2 bitmap
    block 3 is the segment header
    Five blocks free for rows

Extent 2

    8 blocks free for rows

Extent 3

    block 1 is a level 1 bitmap (which covers extents 3 and 4)     7 blocks free for rows

Extent 4

    8 blocks free for rows

Extent 3

    block 1 is a level 1 bitmap
    7 blocks free for rows

5 + 8 + 7 + 8 + 7 = 35

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005