Re: Algorithm for calculating extent size in LMT

In article <ejvh8.241$H04.25685_at_news2.west.cox.net>, "dkrpata1" says...
>
>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.
>

been there, done that -- read/write perf is the same.

the BIG different for DMT's is doing ddl (truncate, drop) on an object with tons of extents can take a while. But then again, in a production system -- do you truncate or drop often (and if you do -- you can easily fix the problem by better sizing the extents -- point being that if you have a DMT table in production with 1,000 extents and you are not planning on truncating/dropping it -- don't worry about it. If you do plan on dropping/truncating it -- fix the extent size after you do that in order to avoid this issue in the future.)

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

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