Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Performance and number of extents

Performance and number of extents

From: Nuno Souto <nsouto_at_acay.com.au>
Date: 1997/08/14
Message-ID: <33F2EDAC.7027@acay.com.au>#1/1

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 Thu Aug 14 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US