Re: Algorithm for calculating extent size in LMT

You'd have to explain why a delete would take longer for a segment comprised of (say) 100 1M extents than for a segment comprised of 10 10M extents.

As a quick test, I created a tablespace with uniform size 256K. I created a table, BLAH, as a select * from dba_objects. I then 'inserted into blah select * from blah' multiple times until I had 239 extents (and half a million records). Set timing on, and a 'delete from blah' took 59.09 seconds.

Bounce the Instance, repeat all the above procedures, only this time create the table in a tablespace with 2M extents, meaning that BLAH ended up with half a million records as before, but this time in only 29 extents. Set timing on, delete from blah: 1 minute and 1.04 seconds.

The difference is not significant, and I wouldn't expect it to be. The number of extents has practically nothing to do with the work involved in deleting records. And when the number of extents is more or less irrelevant to such matters, not spending time worrying about them is not a sign of sloppiness or unprofessionalism, but of sensible prioritising of the DBAs time.

HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
news:3C82AC99.4BFD214B_at_ci.seattle.wa.us...


> 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

> > >

>