Nice paper, but interested why you used LMT and did you do any tests against
a normal dictionary managed tablespace, just interested to see what the
comparable results would be.
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:a5ve1j$5hc$1_at_lust.ihug.co.nz...
> Utter nonsense, I'm afraid. You seem to be assuming that congruity of
> extents from the logical perspective translates into physical congruity.
It
> doesn't. Even a single extent is comprised of physical o/s blocks
scattered
> all round the disk platter, so the additional latency induced by not
having
> extents 'next to each other' is barely detectable, if ever.
>
> The number of extents has absolutely zero impact on the performance of
full
> table scans or on bulk deletes (with the sole proviso that having to read
> the extent map itself in multiple i/o operations *is* a slight performance
> overhead).
>
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "dkrpata1" <dkrpata1_at_cox.net> wrote in message
> news:14Gg8.26219$FY5.1048917_at_news2.west.cox.net...
> > 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 Wed Mar 06 2002 - 14:51:22 CST
