What, do you want 'Big Brother' to handle everything for you, and just tell you what
to do? The cry was long and loud for Oracle to fix the extent limitation. Now it's
fixed and still the complaints keep coming. Default values are not a replacement for
personal responsibility.
>>> [EMAIL PROTECTED] 02/15/01 09:47AM >>>
Hi List,
I have been reading through the 8i Tuning & Performance Guide, and
remembered a topic on the list a few weeks back discussing the maximum
number of extents for a LMT.
In the Tuning I/O section of this guide there is a reference to this
stating:
---snip---
Evaluating Unlimited Extents
Even though an object may have unlimited extents, this does not mean that
having
a large number of small extents is acceptable. For optimal performance you
may
decide to reduce the number of extents.
Extent maps list all extents for a particular segment. The number of extents
entries
per Oracle block depends on operating system block size and platform.
Although
an extent is a data structure inside Oracle, the size of this data structure
depends on
the platform. Accordingly, this affects the number of extents Oracle can
store in a
single operating system block. Typically, this value is as follows:
Block Size (KB) Maximum Number of Extents
2 121
4 255
8 504
16 1032
32 2070
For optimal performance, you should be able to read the extent map with a
single
I/O. Performance degrades if multiple I/Os are necessary for a full table
scan to get
the extent map.
Avoid dynamic extension in dictionary-mapped tablespaces. For
dictionary-mapped tablespaces, do not let the number of extents exceed
1,000. If extent allocation is local, then do not have more than 2,000
extents. Having too many extents reduces performance when dropping or
truncating tables.
---snip---
Now, I also remember somebody stating that MAXEXTENTS should be set to 504
for an 8K block size, and he was flamed.. I also added to this, and now
appologise..
Now, the question arises :
I created an LMT with the default storage parameters, and Oracle in its
infinite wisdom, gave a MAXEXTENTS value of 2147483645!!
A few more than 2000, was Larry thinking of his bank balance when he decided
on this? :)
You know, it's so great that they put these little "pearls" of wisdom in to
tuning guides, that you have to dig in to, just to find a ghost of an
answer, but when it comes to putting them as standard in to the bloody
product they give stupid values that would grind a system to a halt if ever
allowed to happen!! Would it not then make sense to just set this as a
default??
Am I being naive? LOL..
Can we all join hands and shout "Thank you LARRY!!!!"
Mark
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
