Re: Any Final Verdict on Nr. of Extents?

"Joe Maloney" <mpir_at_bellsouth.net> wrote in message news:d17bad25.0109061235.4f19a9b9_at_posting.google.com...
> 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.

Non-contiguous extents is *not* fragmentation. Fragmentation has a very precise and well-defined meaning, and its a free space issue only. And discontiguous extents will only ever be an issue for full table scans (and even then I doubt you'd be able to measure a performance degradation under any normal circumstances), which is where I guess you get your comments about 'few, large extents for OLAP'.

Where you get the idea that 'many, small extents' somehow gives a benefit to OLTP systems, I can't imagine.

>
> 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.
>

Nope. It scans db_file_multiblock_read_count at a time. And only then during full table scans. And sure, there is an interaction between multiblock_read_count and your extent sizes... the latter should be a multiple of the other, since otherwise an extra i/o at the end will always be induced.

Regards
HJR
>
>
> 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 - 19:16:27 CDT