Re: Performance and number of extents

Take a look at the White Paper Oracle 7 Server Space Management, http://tiburon.us.oracle.com/odp/archive/library/white.htm

Best Regards,
Rob Mocking

Nuno Souto <nsouto_at_acay.com.au> wrote:

>Hi. For a while now there has been this folk tale here in Australia on
>how a DBA needs to maintain all objects at a single extent otherwise
>performance drops DRAMATICALLY.
 

>Every single time I've challenged this tale, it's turned out to be
>related to some old benchmark that someone ran on an old and un-tuned
>version of ORACLE. I have been unable to notice any SIGNIFICANT (notice:
>I said SIGNIFICANT!) performance degradation due to more than one extent
>in an object - table or index, since V7 came out. Even in V6, with a
>large dc_used_space and dc_free_space I couldn't notice any
>degradation. OF COURSE, I'm NOT talking about tables or indexes with >
>100 extents. These are sure bad, but up to 10 extents I can't find any
>problems every time I've measured.
 

>I've been to sites where DBA's religiously defrag 2 extent tables EVERY
>night, as a result of this folk tale. These people don't defrag indexes,
>because they say it doesn't affect indexes! Nobody has explained to
>them that if they defrag with a larger initial extent and make NEXT a
>bit larger, the table won't fragment at all in any significant fashion
>that may affect performance and they can have easier nights...

>What are other DBA's experiences in this area? Please don't bother
>replying if your experience is based on following blindly someone else's
>recommendation: I'm interested in measurements that are recent, for both
>OLTP and batch oriented databases, not "hear-say" stuff.
 

>Thanx in advance for any feedback.
 

>Nuno Souto
>nsouto_at_acay.com.au
Received on Tue Aug 19 1997 - 00:00:00 CDT