| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Algorithm for calculating extent size in LMT
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?
"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
>
>
>
>
>
>
>
>
 |
 |