Very Computer

by EP » Mon, 04 Mar 2002 22:14:38

Hi

Does anybody know the algorithm used by Oracle when calculating the extent
size for LMT when autoallocate option is specified ???

TIA
EP

by **damorga** » Tue, 05 Mar 2002 07:12:33

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

> Hi

> Does anybody know the algorithm used by Oracle when calculating the extent
> size for LMT when autoallocate option is specified ???

> TIA
> EP

by **Howard J. Roger** » Tue, 05 Mar 2002 07:54:15

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

> 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

> > Hi

> > Does anybody know the algorithm used by Oracle when calculating the
extent
> > size for LMT when autoallocate option is specified ???

> > TIA
> > EP

by **damorga** » Tue, 05 Mar 2002 08:07:00

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

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

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

> > > Hi

> > > Does anybody know the algorithm used by Oracle when calculating the
> extent
> > > size for LMT when autoallocate option is specified ???

> > > TIA
> > > EP

by **Howard J. Roger** » Tue, 05 Mar 2002 08:31:45

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

> 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

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

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

> > > > Hi

> > > > Does anybody know the algorithm used by Oracle when calculating the
> > extent
> > > > size for LMT when autoallocate option is specified ???

> > > > TIA
> > > > EP

by **dkrpata** » Tue, 05 Mar 2002 17:17:01

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

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

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

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

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

> > > > > Hi

> > > > > Does anybody know the algorithm used by Oracle when calculating
the
> > > extent
> > > > > size for LMT when autoallocate option is specified ???

> > > > > TIA
> > > > > EP

by **Howard J. Roger** » Tue, 05 Mar 2002 18:16:26

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

> 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

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

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

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

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

> > > > > > Hi

> > > > > > Does anybody know the algorithm used by Oracle when calculating
> the
> > > > extent
> > > > > > size for LMT when autoallocate option is specified ???

> > > > > > TIA
> > > > > > EP

by **Howard J. Roger** » Tue, 05 Mar 2002 18:26:05

Incongruously, I wrote 'congruity'. God knows why. I meant 'contiguous',
or 'contiguousness'.

Apart from that daft error, the comments still stand!

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

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

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

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

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

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

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

> > > > > > > Hi

> > > > > > > Does anybody know the algorithm used by Oracle when
calculating
> > the
> > > > > extent
> > > > > > > size for LMT when autoallocate option is specified ???

> > > > > > > TIA
> > > > > > > EP

by **Nuno Sou** » Tue, 05 Mar 2002 21:36:01

Quote:>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).

I won't buy into the "no more problems with LMTs" concept, Howard.
I'm currently running them in a production system with 8.1.7/NT.
There are indeed problems. Things slow down dramatically once you go
over a certain boundary in terms of number of extents. Would love to
have the numbers for you at the drop of a hat, but I don't.

First noticed the problem with a recursive PL/SQL update that ate up
the RLB. All the rollback segment traffic became horrendously slow as
the number of extents used went ballistic. Wasn't full, but there
were a lot of 16K extents(block size is 8K). I mean a LOT. Boom,
performance of the system went out the window.

Maybe Jonathan will have some numbers? I can't afford the time to
chase this up, gotta get this system out by the end of April and no
time left for experiences. But it's worth doing some research in this
area.

IMHO, it's not an absolute number. Relative to the size of the
datafile. Which leads me to believe there is some "sweet" boundary or
ratio between datafile size, db block size and fixed extent size for
LMT beyond which things will go "bonk". It's important that someone
does a little bit of investigative work in this area to make sure we
don't collectively stick our feet in mud with a real live system. Any
takers?

Cheers
Nuno Souto

by **Howard J. Roger** » Wed, 06 Mar 2002 01:56:50

Hi Nuno,

Wasn't suggesting that LMTs were problem-free, just that the number of
extents *for a table* has no performance impact on deletes done to *that
table*, subject to the proviso I made about the extent map. (For the record,
I still don't like seeing more than 500 or so extents for anything on an 8K
block system).

How many extents were we talking about for the rollback segment, though?
I've never encountered anything as serious as you describe, but I can well
imagine that internal rollback handling is not bazillion-extent aware.

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

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

> I won't buy into the "no more problems with LMTs" concept, Howard.
> I'm currently running them in a production system with 8.1.7/NT.
> There are indeed problems. Things slow down dramatically once you go
> over a certain boundary in terms of number of extents. Would love to
> have the numbers for you at the drop of a hat, but I don't.

> First noticed the problem with a recursive PL/SQL update that ate up
> the RLB. All the rollback segment traffic became horrendously slow as
> the number of extents used went ballistic. Wasn't full, but there
> were a lot of 16K extents(block size is 8K). I mean a LOT. Boom,
> performance of the system went out the window.

> Maybe Jonathan will have some numbers? I can't afford the time to
> chase this up, gotta get this system out by the end of April and no
> time left for experiences. But it's worth doing some research in this
> area.

> IMHO, it's not an absolute number. Relative to the size of the
> datafile. Which leads me to believe there is some "sweet" boundary or
> ratio between datafile size, db block size and fixed extent size for
> LMT beyond which things will go "bonk". It's important that someone
> does a little bit of investigative work in this area to make sure we
> don't collectively stick our feet in mud with a real live system. Any
> takers?

> Cheers
> Nuno Souto

by **Mark D Powe** » Wed, 06 Mar 2002 02:05:52

Quote:> 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
> ===============================

Howard, thanks for posting your findings. I find the results
interesting, and potentially good to have in the back of my mind in
case I encounter auto extent in use. I perfer to either use uniform
extents or manage them manually using a limited set of extents, but
you never know what you will encounter.

-- Mark D Powell --

by **Howard J. Roger** » Wed, 06 Mar 2002 02:46:29

Hi Mark.

I agree. Uniform size is so easy to do, I can't think why anyone would want
to use autoallocate for their own tablespaces (something in the back of my
head tells me 9i Release 2 uses autoallocate for SYSTEM. I may have got my
neurons crossed, though. And undo tablespaces are autoallocate, of course).

On the other hand, the autoallocate policy is not as crazy as ye olde
PCTINCREASE, and the possible fragmentation penalties seem less severe.

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

Quote:> > 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
> > ===============================

> Howard, thanks for posting your findings. I find the results
> interesting, and potentially good to have in the back of my mind in
> case I encounter auto extent in use. I perfer to either use uniform
> extents or manage them manually using a limited set of extents, but
> you never know what you will encounter.

> -- Mark D Powell --

by EP » Wed, 06 Mar 2002 03:48:45

Quote:> 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.

You are mistaken here, as with the autoallocate option you also have to
calculate such parameters as INITIAL, NEXT and MINEXTENTS, so DBA homework
is done as well !!!
As far as the number of extens goes, Oracle's calculation is based on the
above parameters, so you don't need to worry about too many extents (unless
you have made a mistake while estimating these parameters).

Regards,
EP

by **Yong Hua** » Wed, 06 Mar 2002 04:15:25

Is this because the number of extents in the LMT goes above 30,000?
Jonathan's book p.155 says the 64K bitmap header corresponds to 27,000
to 30,000 bits. Not sure how this is calculated. But he says if 64K is
used up because you have too many extents, another map is created in
the middle of the datafile and space management performance suffers a
little.

Yong Huang

> I won't buy into the "no more problems with LMTs" concept, Howard.
> I'm currently running them in a production system with 8.1.7/NT.
> There are indeed problems. Things slow down dramatically once you go
> over a certain boundary in terms of number of extents. Would love to
> have the numbers for you at the drop of a hat, but I don't.

> First noticed the problem with a recursive PL/SQL update that ate up
> the RLB. All the rollback segment traffic became horrendously slow as
> the number of extents used went ballistic. Wasn't full, but there
> were a lot of 16K extents(block size is 8K). I mean a LOT. Boom,
> performance of the system went out the window.

> Maybe Jonathan will have some numbers? I can't afford the time to
> chase this up, gotta get this system out by the end of April and no
> time left for experiences. But it's worth doing some research in this
> area.

> IMHO, it's not an absolute number. Relative to the size of the
> datafile. Which leads me to believe there is some "sweet" boundary or
> ratio between datafile size, db block size and fixed extent size for
> LMT beyond which things will go "bonk". It's important that someone
> does a little bit of investigative work in this area to make sure we
> don't collectively stick our feet in mud with a real live system. Any
> takers?

> Cheers
> Nuno Souto

by **damorga** » Wed, 06 Mar 2002 04:31:24

And I agree with everything you said.

Thank you,

Daniel Morgan

> 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

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

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

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

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

> > > > > > Hi

> > > > > > Does anybody know the algorithm used by Oracle when calculating
> the
> > > > extent
> > > > > > size for LMT when autoallocate option is specified ???

> > > > > > TIA
> > > > > > EP

Very Computer

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT

Algorithm for calculating extent size in LMT