Oracle-L: RE: Number of extents

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 08 Nov 2002 09:25:12 -0800
Message-ID: <F001.004FEFB0.20021108092512@fatcity.com>

The statement "Any object that is returned from the following query will decrease performance" is false. Having large numbers of extents matters only (1) if your application drops tables frequently and you're not using LMT, or (2) your application inserts into a table that endures new extent allocation every few seconds, or (3) if your application uses Oracle7 Parallel Server. Since 1, 2, and 3 are all bad ideas, having 20 or 100 or even 20,000 extents in a segment is of virtually no performance consequence whatsoever. (For details on what's up with #3, see www.jlcomp.demon.co.uk/extent.html.)

The information in the MetaLink document is at least as old as a paper written for Support by some friends in Oracle Application Development back in about 1987. The now-infamous document still serves as an embarrassment to my friends who wrote it. The idea had nearly died a proper death until the author of "part number 2434-6 (Oracle Press)" re-invigorated it in 1999.

In spite of this statement's falseness, it has been a godsend for some:

It created an easy way out of many tough Support calls. "Just rebuild your database and call me back if things are still slow." Things of course *would* still be slow afterward, but at least when the customer called back the next week, the call would probably get picked up by different analyst.
It created an opportunity for the intake of tens of millions of dollars in consulting revenue and de-fragmentation software sales.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on Oracle® System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas

-----Original Message-----
Nguyen
Sent: Friday, November 08, 2002 9:24 AM
To: Multiple recipients of list ORACLE-L

Hello,

I was very surprise when I saw what is below in the note 100960.1 at Metalink about the number of extents. Could-you give me your opinion please ?

1.4 OBJECTS WHICH DECREASE PERFORMANCE      Any object that is 
returned from the following query will decrease     
performance.  While the performance hit on over extended 
objects is not      significant, the aggregate effect on many 
over extended objects does impact     performance.       
ttitle 'All segments with >20 extents belonging to SYS' 
skip        clear breaks       clear computes       clear 
columns        column tablespace_name  format a15       column 
segment_name     format a30       column segment_type     
format a8        select  substr(tablespace_name,1,15) 
Tname,               segment_name,               
segment_type,               substr
(owner,1,10) "OWNER",               count
(*) "Extents",               blocks       from    
sys.dba_extents       where   owner != 'SYS'       group by 
tablespace_name,               segment_name,               
segment_type,               owner,               blocks

having count(*) >20;

Accédez au courrier électronique de La Poste : www.laposte.net ; 3615 LAPOSTENET (0,13 €/mn) ; tél : 08 92 68 13 50 (0,34€/mn)"

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: INET: nguyen_at_laposte.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------