Re: Algorithm for calculating extent size in LMT

In article <4x9h8.754$1c3.32427_at_news2.west.cox.net>, "dkrpata1" says...
>
>Ok, so then help me out, cause I've been under this impression for a while.
>
>I'm not saying somethig with 5 or maybe even 100 extents is necessarily bad,
>but I have seen quite a few databases with extents in the range of 500 to
>1000's of extents. So can we say that the I/O time on accessing data
>through that table is not going to be very affected?
>

yup

suppose you access it via an index.

index -> rowid -> file/block/slot -> read that file+block and get the data. That really won't matter if you have 1 or 10,000 extents.

suppose you full scan. Well, if the extents are multiples of your multi-block read count -- it'll be just fine. It is only if your extents are not multiples of that, that you might do an extra IO/extent to pick up some more data.

with raid arrays, volume managers, multi-user systems this stuff about "leaving the head where it was for the next io" (not to mention the fact that data within an extent is LOGICIALLY CONTIGOUS but PHYSICALLY NON-CONTIGOUS on disk (else why does norton sell a disk defragmentor that defrags files???)) just isn't reality.

>
>"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
>news:3C851E9D.407E_at_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..."
>
>

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