Re: Algorithm for calculating extent size in LMT

I think that the auto-allocate policy (rather than algorithm) is one of those little things that Oracle Corp. may choose to tweak in minor releases of the database.

When testing, remember that if you start with a completely empty tablespace, and simply keep creating and dropping a single table, then you are operating a very special case.

In the simplest case:

    create table
you get 1 x 64K extent

if you then keep allocating an extent you will probably get 16 x 64K extents, then N x 1MB extents (in 8.1.5 N was 16, I am happy to believe HJRs note that it is now 64 - such details are nice to know, but the general principal is more important), and then switch to 8M extents and so on.

However if you try to create a table of 100MB (say) Oracle knows that the table is supposed to be big, and may skip the 'progression' and jump straight into 8MB extents.

Moreover - if in an empty tablespace, Oracle DOES just straight to 8MB extents and nothing else, this doesn't mean it will do so in a tablespace with lots of pre-existing gaps. It is quite possible that if there are 21 gaps of 64K and 8 gaps of 1M, then Oracle may use them up before jumping to 8MB extents. It is possible that if there are 450 gaps of 64K, then Oracle may use 64 of them, and ignore the rest. (I haven't tested this last idea - it may be completely untrue, the previous observation came from actual tests on 8.1.5).

Bottom line - there is no 'algorithm', only a policy, and the general directives seems to be:

    use space close to the start of the file     don't generate large numbers of extents if you don't need to     Use extents in the list 64K, 1M, 8M, 64M, 256M

            (I'm not sure about that 256MB - I think I tested it once)

There are also no doubt a few hard-coded numbers (such as the 16 extents of 64K for slow growth)

There is, by the way, a note I spotted once on metalink in one of the Forums which stated explicitly that there is no published algorithm, and it is information deliberately undisclosed as Oracle may choose to vary it from time to time.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

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

Author of:
Practical Oracle 8i: Building Efficient Databases


Niall Litchfield wrote in message
<3c85dba7$0$238$ed9e5944_at_reading.news.pipex.net>...


>"Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message

>news:a8aed4.0203060031.12152066_at_posting.google.com...

>> > It's harder to predict in an autoallocate, since it

>> > depends on what empty space actually exists

>> > already, but there is a fair chance that your

>> > starting segment would have

>> >     3 x 1MB and 5 x 8MB.

>>

>> Sorry lost you here !!! What is the Logic Behind this.

>

>PMFJI. Howard offered the following for the autoallocate algorithm.

>

>"

>The first 16 extents of a segment will be 64K in size.

>The next 64 extents will be 1M in size

>Then extents become 8M in size."

>