Re: Is there a performance limit of extents?

Don't agree.
Quite often you don't have any chaining! Also, Oracle typically tries to process an extent as one unit, cache it completely in memory etc. When there are many extents, it needs to return to the table header frequently to retrieve the next extent info. This usually results in all kinds of recursive calls.

Regards,

Sybrand Bakker, Oracle DBA

<decompton_at_worldnet.att.net> schreef in berichtnieuws 8dii4e$fo8$1_at_nnrp1.deja.com...
> If data is by index access, then extents have little to no overhead.
> If you are performing full table scans or full index scans, then there
> is overhead in that ORACLE will have to lookup the starting address of
> the next extent of data it needs to scan.
>
> A lot of folks get the impression that extents are bad for the
> following reason. Querys against a table are slow. The DBA sees that
> the table is in 50 extents, so he/she exports/drops/imports the table
> into one extent. Performance is now much better. Conclusion, multiple
> extents causes the performance problem. Wrong! What really caused the
> performance problem was some combination of chained rows and
> unbalanced/scrambled indexes. The export/import takes care of these
> problem as well, fixing the problem.
>
> IMHO, as long as you are not chaining rows, let the extents run and
> periodically rebuild your indexes.
>
> dave
>
> In article <20000418.5530600_at_noname.nodomain.nowhere>,
> Jerry Gitomer <jgitomer_at_erols.com> wrote:
> >
> >
> > I just read a report from Oracle consulting where the
 consultant=20
> > advised reorganizing the database in order to bring all tables and=20
> > indexes under 20 extents in order to improve performance. My
 personal=20
> > preference is for no more than 10 extents per table or index and I
 am=20
> > happier when I have less than four.
> >
> > Based on that I think your extent sizes are okay, by the way,
 I=20
> > presume you are planning on one tablespace for each extent size
 with=20
> > PCTINCREASE 0 in order to eliminate the need to ever coalesce or=20
> > reorganize your database ;-) I do think that you should change the=20
> > table size ranges for each extent so that you have no more than 10=20
> > extents per table.
> >
> > >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> >
> > On 4/17/00, 1:43:07 PM, Gonzalo <gonzarg_at_my-deja.com> wrote regarding
 Is=
> > =20
> > there a performance limit of extents?:
> >
> > > Hi,
> > > I'm migrating to Oracle 8.0.5 - Solaris 2.7.
> > > I know exactly the size of my tables. The question is:
> > > Which is the better extent size? Is there a rule for this?
> > > I was told than in earlier version of Oracle, having more
> > > than 20 extents in a segments, is not recomended (performance
> > > decrease), is it true?
 

> > > The rule I'm using is the following:
> > > Table Size EXTENTS SIZE
> > > 0 a 5 MB 64kb
> > > 5Mb a 50 Mb 1Mb
> > > 50 Mb a 100Mb 10Mb
> > > > 100 Mb 50Mb
 

> > > but I=B4m not sure it's rigth
 

> > > Any help will be apreciated,
 

> > > Thanks,
> > > Gonzalo.
 

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Apr 18 2000 - 00:00:00 CDT