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: [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: [EMAIL PROTECTED] 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: [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).
