Re: Algorithm for calculating extent size in LMT

Thanks for adding the clarification to my posting.

But I still see performance problems with large numbers of extents. Especially when doing things such as emptying a table with DELETE rather than TRUNCATE (something that is sometimes necessary). And to me a large number of exents is a sign that someone doesn't understand their schema or didn't take the time to calculate the size of a table and its data ... in short ... sloppy and unprofessional workmanship.

Daniel Morgan

"Howard J. Rogers" wrote:

> There *is* such a thing, of course... there is an 'autoallocate' policy for
> locally managed tablespaces, and as best I can tell it goes something like
> this:
>
> 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.
> At the 200th extent, you get 64M extents.
> After that, I can't tell you... because I ran out of disk space!
>
> What Daniel is hinting at, I guess, is that having odd-sized extents within
> a tablespace is not a good idea, because it risks fragmentation. I agree
> with him that 'autoallocate' is not a terribly good idea for your own
> tablespaces, and that you should take charge of the extent allocation
> policy.
>
> The essential feature of locally managed tablespace is that we no longer
> really give a damn how many extents a segment acquires, because extent
> allocation is now a trivial operation for the database (though I agree that
> having the extent map for a segment fit into one block makes for some small
> performance improvement, and therefore limiting the number to the old hard
> limits (121 for 2K blocks, 504 for 8K blocks and so on) is still not a bad
> idea).
>
> Regards
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
> "damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
> news:3C829FD7.51727A5B_at_ci.seattle.wa.us...
> > There is no such thing. Extent sizes are determined by developers and DBAs
> > that do their homework and determine the idea size for a class of tables.
> > Often ... creating multiple tablespaces with different extent sizes to
> avoid
> > both fragmentation and keep the number of segments small.
> >
> > Daniel Morgan
> >
> >
> >
> > EP wrote:
> >
> > > Hi
> > >
> > > Does anybody know the algorithm used by Oracle when calculating the
> extent
> > > size for LMT when autoallocate option is specified ???
> > >
> > > TIA
> > > EP
> >
Received on Sun Mar 03 2002 - 17:07:00 CST