RE: LMT MAXEXTENTS for 8K Blocksize

hhmmmmm, I agree to an "extent":). Any good DBA should check for all objects in a database reaching MAXEXTENTS, on a fairly regular basis. I DIDNT say that MAXEXTENTS should have a default of 2000 for a LMT just because I'm too lazy too get of my slim a$$ to check every now and then, I said wouldn't it make sense to set the default at a point where performance can start to degrade.

Personally I wouldn't dream of letting anything grow to anywhere near 2000 extents.. My complaint was tongue in cheek to an "extent":)..

And hey, SS7 does everything for you why not Oracle :^)

-----Original Message-----
Sawmiller
Sent: Thursday, February 15, 2001 06:01
To: Multiple recipients of list ORACLE-L

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.

>>> mark_at_cool-tools.co.uk 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: sawmillert_at_state.mi.us

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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

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: ListGuru_at_fatcity.com (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).