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 -> Re: Is there a performance limit of extents?

Re: Is there a performance limit of extents?

From: <decompton_at_worldnet.att.net>
Date: 2000/04/18
Message-ID: <8dii4e$fo8$1@nnrp1.deja.com>#1/1

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

Original text of this message

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