Re: Algorithm for calculating extent size in LMT

Well, I did for LMTs, because that's what the original claim that contiguity (and number) of extents making a difference to query and DML performance was made in relation to. But a quick edit of the test script later (replace "extent management local...." with "default storage (initial 64k next 64k pctincrease 0 maxextents 1000)" (or 4M where appropriate, of course), and here's the average results over two runs (so this *is* for dictionary managed tablespace, times in seconds):

766 small extents:
Discontiguous - Select: 13.90 Delete: 65.42 Contiguous - Select: 13.44 Delete: 64.42

15 large extents:
Discontiguous - Select: 11.66 Delete: 63.93 Contiguous - Select: 11.94 Delete: 64.42

So as to contiguous and discontiguous in dictionary managed tablespace -it makes no difference. As to few extents versus many extents, there's nothing in it based on this test (maybe a little on the select speeds).

But why don't you (or anyone else for that matter) run the script yourself, and report back with your own findings?

Regards
HJR Raw Data (first testrun/second testrun):

Many Small Extents Select:

Table 1: Discontiguous 16.01/16.01  Contiguous 16.09/16.09
Table 2: Discontiguous 11.07/14.09  Contiguous 14.04/13.06
Table 3: Discontiguous 14.00/16.03  Contiguous 14.08/13.03
Table 1: Discontiguous 11.02/13.01  Contiguous 10.01/11.08

Many Small Extents Delete:

Table 1: Discontiguous 63.06/72.04  Contiguous 62.04/67.08
Table 2: Discontiguous 65.03/65.02  Contiguous 61.02/68.05
Table 3: Discontiguous 64.07/65.09  Contiguous 60.07/68.05
Table 1: Discontiguous 63.03/66.04  Contiguous 61.09/68.03

Few Large Extents Select:

Table 1: Discontiguous 15.07/14.03  Contiguous 14.08/14.09
Table 2: Discontiguous 12.04/11.01  Contiguous 12.08/11.05
Table 3: Discontiguous 12.08/10.07  Contiguous 13.06/12.05
Table 1: Discontiguous 09.02/10.00  Contiguous 10.07/09.04

Few Large Extents Delete:

Table 1: Discontiguous 61.09/67.00  Contiguous 62.04/67.08
Table 2: Discontiguous 58.08/67.08  Contiguous 61.02/68.05
Table 3: Discontiguous 61.02/67.03  Contiguous 65.03/66.00
Table 1: Discontiguous 63.05/67.07  Contiguous 62.06/64.01

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"dkrpata1" <dkrpata1_at_cox.net> wrote in message
news:ejvh8.241$H04.25685_at_news2.west.cox.net...


> 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

> > > > > > >

> > > > >

> > > >

> > > >

> > >

> > >

> >

> >

>

>