Re: Algorithm for calculating extent size in LMT

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 - 13:31:24 CST