Re: Algorithm for calculating extent size in LMT

No, we're not in agreement at all, because you claim extent numbers and contiguity affect performance, and I say they don't. I've demonstrated the issue with some hard, repeatable numbers, in a post made earlier, and in a document readily downloadable at
http://www.hjrdba.com/adminpdfs/contiguous.pdf . You *claim* to have benchmarked it ...but haven't provided the numbers.

Face it: for extent contiguity to make any difference to performance, you've got to make the assumption that extents which are logically contiguous are also physically contiguous on disk. They're not, which means there can be no relationship between the two things, and performance cannot therefore be affected. And as is also demonstrated with those tests, the extent numbers make precisely zilch difference either: 957 extents or 15, the numbers come out the same.

For the record, I have about as much faith in your claim to have performed benchmarks as I have in your infamous claim I include here to remind us all of the good ol' days:

"The create database command does not, in and of itself, create any rollback segments anywhere. You must specify them. At least that is my previous experience and my experience with 8.1.7. The only rollback segments created were
the ones I coded." (Posted by Daniel A. Morgan (dmorgan_at_exesolutions.com) Date: Wed, 14 Mar 2001 20:50:15 -0800 )

Good old Google.

HJR

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


"damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
news:3C83CCCB.60E8F7F9_at_ci.seattle.wa.us...


> I think we are, possibly, all in agreement here. I can't imagine a single

reason


> not to use uniform size.

>

> But all of my testing indicates performance degredation with large numbers

of


> extents. Given that those extents force multiple disk reads/writes as

opposed to


> multiple contiguous extents. I have benchmarked it on an NT4SP5, Solaris

Ultra,


> and an HP 9000 all giving the same result.

>

> Daniel Morgan

>

>

>

> "Howard J. Rogers" wrote:

>

> > Hi Mark.

> >

> > I agree.  Uniform size is so easy to do, I can't think why anyone would

want


> > to use autoallocate for their own tablespaces (something in the back of

my


> > head tells me 9i Release 2 uses autoallocate for SYSTEM.  I may have got

my


> > neurons crossed, though.  And undo tablespaces are autoallocate, of

course).


> >

> > On the other hand, the autoallocate policy is not as crazy as ye olde

> > PCTINCREASE, and the possible fragmentation penalties seem less severe.

> >

> > Regards

> > HJR

> > --

> > ----------------------------------------------

> > Resources for Oracle: http://www.hjrdba.com

> > ===============================

> >

> > "Mark D Powell" <mark.powell_at_eds.com> wrote in message

> > news:178d2795.0203040905.5460dbda_at_posting.google.com...

> > > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

> > news:<a5u9is$1r7$1_at_lust.ihug.co.nz>...

> > > > 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

> > > > ===============================

> > > >

> > >

> > > Howard, thanks for posting your findings.  I find the results

> > > interesting, and potentially good to have in the back of my mind in

> > > case I encounter auto extent in use.  I perfer to either use uniform

> > > extents or manage them manually using a limited set of extents, but

> > > you never know what you will encounter.

> > >

> > > -- Mark D Powell --

>