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: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Tue, 05 Mar 2002 19:38:05 +0000
Message-ID: <3C851E9D.407E@yahoo.com>


Thomas Kyte wrote:
>
> In article <a61g2r$ubr$1_at_lust.ihug.co.nz>, "Howard says...
> >
> >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.
>
> Howard --
>
> this is the holy grail. This entire topic is shrouded in so much mysticism and
> hocus pocus -- its silly after a while.
>
> If I had a penny for every time I've said "so what if you have x hundred
> extents, no big deal". Having everything in one extent is not desirable. It is
> not bad to have lots of extents. Yadda Yadda Yadda and so on.... Well, I could
> easily retire many times over.
>
> People just don't get it. Even when you show them -- they still don't believe
> you.
>
> It is a shame, most mistakes I see are made by people trying to reorg in order
> to get rid of 5 extents and put it into that 'perfect' one extent table. They
> lose a grant, index or something else. They should just leave it be.
>
> Its also funny to hear the "hey you want contigous extents so the head doesn't
> have to move on the disk". They may have been true in 1983 or something but
> today with disk arrays, volume managers, raid everywhere, MULTI-USER SYSTEMS
> (this one just totally kills that head moving theory), and so on -- well, you
> get the picture.
>
> >
> >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
> >> > > > > >
> >> > > >
> >> > >
> >> > >
> >>
> >
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp

Its still out there...

"Generally speaking, fragmentation..." (mentioned in the context of extents) "...decreases performance by 10-20 percent on average"

   p109, very popular book,

         very popular, very "esteemed" author,
         very recent publishing date.

Same book has a glowing recommendation from some high-up Oracle people..

You can't blame the novice DBA for getting the wrong picture...

pity really..

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."
Received on Tue Mar 05 2002 - 13:38:05 CST

Original text of this message

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