Re: Any Final Verdict on Nr. of Extents?

TJI: I support about 32 Oracle databases, sized from 50GB to 1.7TB. IMHO, number of extents depends on the type of application and box. OLTP benefits from a large number of small extents. OLAP, a small number of large extents. Back in the old days, most of us thought of Relational access in OLAP mode (list all the workers in the Loney operation, not THE worker.) That made small number of extents an issue, as was fragmentation, where the extents were all over the map instead of contiguous.

Another factor is your box. What is it's io buffer size and algorithm. If it always retrieves 2M of disk data and you are OLTP, then a 2M extent is the 'natural' size. A large table (I have one which is 160GB itself) is going to have lots of extents. An extent size that significantly exceeds the OS buffer and cache is itself going to force extra io as Oracle tends to ask for an extent at a time, at least I seem to remember hearing that somewhere.

Sylvain Leclerc <SLeclerc_at_magrit.com> wrote in message news:<CCD755725CEABC448827E1E1C16E6833019869_at_conceptum04.magrit.int>...
> This is the best I have found
>
> http://www.ixora.com.au/tips/creation/extents.htm
>
> Sylvain Leclerc, DBA
> Conceptum Informatique
> sleclerc_at_magrit.com
>
> -----Original Message-----
> From: Reza [mailto:reza_at_digital-dispatch.co.uk]
> Posted At: Thursday, September 06, 2001 6:18 AM
> Posted To: server
> Conversation: Any Final Verdict on Nr. of Extents?
> Subject: Any Final Verdict on Nr. of Extents?
>
>
> Hi guys
> I've been having a hot debate with our new say DBAs, on effect of Number
> extents in an object like a table or index on performance.
> Basically I didnot agree to take a 7x24 system down for 6 hours to just
> reduce the number of extents on few huge tables and their indexes.
> I would personally believed few years ago that lower number of extents
> will
> be better performance and even remember arguing this with some Oracle
> Tutors
> in their lectures. Since then I have somehow revised my view due to
> working
> with few production databases, and also hearing more from other DBAs in
> favor of ineffectiveness of Nr. of extents in Performance.
>
> Anyway I would appreciate if anyone could direct me to a definitive
> article
> or somehow share their view on this so I can supprt my argument better
> or at
> list to revise it again.
> I think somehow fragmentation problem might come to this argument as
> well,
> if the extents are not contiguse in the Objects table space. Any view on
> this aspect.
>
> May thanks for any help or feedback.
> Best Regards
> Reza Oskouie
Received on Thu Sep 06 2001 - 15:35:11 CDT