Re: Extent map block

As you will have noted from Steve's reply, I was answering the wrong question - The figure of 32K I produced was for the size of the bitmap for extents that Oracle keeps in the file for locally managed tablespaces.

Your idea is not a good one. Even though space allocation is cheaper in locally
managed tablespaces, it is not free, it still takes time, and it still takes an exclusive ST lock (though this should change).

Taking your extreme case - if you have
256K extents and a 25GB object , you
get 100,000 extents Try rebuilding that object, and the resulting space management ALONE will still take a couple of hours. (Not that you should really be prepared to allow any single segment to get that big anyway).

The big problem with picking just one size for everything is you may end up putting very large segments in the same tablespace as very small segments. Since very large
segments tend to have very different i/o characteristics from very small segments you can make it much harder to pinpoint
an I/O problem if you mix them.

My rule of thumb is to aim for a range of 1 to 50 extents per segment, and pick a
small number of extent sizes that make
this happen. In your case (if you really want to create 25GB segments) then
you might look at 1GB, 32MB, 1MB if
you want to keep the number of different sizes down to just 3.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:                 Addison Wesley Longman
Book bound date:     8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i

cust102_at_my-deja.com wrote in message <8ufbbt$mrg$1_at_nnrp1.deja.com>...


>Thank you Jonathan for the reply. On a database with 8k block size, it

>takes only 4 blocks. Is it not?  I am trying to avoid having to worry

>about extent sizing altogether. That is, all user tablespaces I create

>will be locally managed tablespaces with uniform extent size of say

>256k. Any segment of size, say, upto 25 GB will use these tablespaces.

>Only in special cases where a segment is going to be over 25GB I will

>create a tablespace with bigger extent size. What will be the impact of

>such a scheme? If 1GB segment requires only 32k then 10GB will require

>only 320K of extent map block space. If these blocks are cached then

>performance won't be impacted even for a full table scan. As the oracle

>allocates the new extents efficiently from the bitmaps stored in 64k

>block in the beginning of each datafile, having many extents should not

>be a problem. Can you comment on this? Thank you.

>

>raghuvir

>

>

>In article <973805040.12216.0.nnrp-04.9e984b29_at_news.demon.co.uk>,

>  "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>>

>> 1Gb at 64K needs about 32K of map.

>>

>> --

>>

>> Jonathan Lewis

>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>

>> Practical Oracle 8i:  Building Efficient Databases

>>

>> Publishers:                 Addison Wesley Longman

>> Book bound date:     8th Dec 2000

>> See a first review at:

>> http://www.ixora.com.au/resources/index.htm#practical_8i

>>

>> cust102_at_my-deja.com wrote in message <8uf21u$ea8$1_at_nnrp1.deja.com>...

>> >Hi,

>> >   Does anyone know how many extents can an extent map block cover?

 For


>> >a locally managed tablespace with uniform extent size of 64k how many

>> >extent map blocks are needed for 1GB segment?

>> >

>> >

>> >raghuvir

>> >

>> >

>> >

>> >Sent via Deja.com http://www.deja.com/

>> >Before you buy.

>>

>>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.