Re: Extent sizes

Jonathan,
That explains it very well.
The level 1 bitmap that covers the present and the next extents - is it normal for all locally managed tablespaces ?

Thanks again,
PK

Jonathan Lewis wrote:
> "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
Received on Sun Sep 25 2005 - 21:00:32 CDT