Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Algorithm for calculating extent size in LMT

Re: Algorithm for calculating extent size in LMT

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Tue, 5 Mar 2002 15:03:29 +1100
Message-ID: <a61g2r$ubr$1@lust.ihug.co.nz>


I'm not going to debate the issue with you, Daniel, because the last time I tried reasoning with you, you claimed you'd created a database without a system rollback segment, and went mysteriously silent on the subject when asked to prove it.

If you (or anyone else) would care to visit http://www.hjrdba.com/adminpdfs/contiguous.pdf, you'll find a testing script I knocked up to create a number of segments, comprising lots of small extents, non-contiguous; followed by lots of small extents, contiguous. Then it tried fewer large extents, non-contiguous, and finally fewer large extents, contiguous. You'll also find the timings for full tablescans and complete deletes in that document. By way of a summary (in case you don't want to read the thing in detail), selecting and deleting half a million rows under various extent configurations went like this:

Many small extents :  Select Discontiguous - 11.78 seconds
Many small extents :  Select Contiguous - 12.06 seconds
Many small extents :  Delete Discontiguous - 63.04 seconds
Many small extents :  Delete Contiguous - 63.81 seconds

Few large extents : Select Discontiguous - 12.17 seconds
Few large extents : Select Contiguous - 10.41 seconds
Few large extents : Delete Discontiguous - 62.79 seconds Few large extents : Delete Contiguous - 66.28 seconds

You tell me where the performance difference arises between any of these tests, particularly given the natural degree of variation between and within test runs anyway.

There isn't one.

HJR

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


"damorgan" <dan.morgan_at_ci.seattle.wa.us> wrote in message
news:3C83CB94.496FA07_at_ci.seattle.wa.us...

> And I agree with everything you said.
>
> Thank you,
>
> Daniel Morgan
>
>
>
> dkrpata1 wrote:
>
> > You're right when you cause all those extents to fragment like that
> > continuously. It's a single move for the disk controller to go to the
disk
> > to get that info. But what if you had 100s of tables with 100s of
extents
> > in the same datafile spread out.
> >
> > Ex. tables a, b and c
> >
> > extent map
> > AAABBBCCCABACCABCABACCABCABCCABBC
> >
> > Then you're going to see the time increase in this scenario because of
the
> > jumping around of the I/O to get the info for that table from disk.
> >
> > But Mr Morgan, the only thing I'd have to say to your statement
> >
> > "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."
> >
> > Is that there are quite a few scenarios of DBA's creating, from scratch,
a
> > new database and sized it according to the functional specs only to find
out
> > that the "functional" people, don't really function. But in most other
> > occasions I would agree with your statement.
> >
> > Dan Krpata
> >
> > "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
> > news:a5ubp7$3lv$1_at_lust.ihug.co.nz...
> > > 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
> > > > > >
> > > >
> > >
> > >
>
Received on Mon Mar 04 2002 - 22:03:29 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US