Re: Is there a performance limit of extents?

In article <956092535.16586.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> Don't agree.

OTOH, I fully agree with Dave. Its time to kill the old "reorg all the time, extents are bad, get rid of them" folklore.

> Quite often you don't have any chaining!

It (along with migrated rows and indexes that need to be re-org'ed) are almost always the culprit. It is not the number of extents.

> Also, Oracle typically tries to process an extent as one unit, cache
 it
> completely in memory etc.

no we don't. we cache blocks and such. extents (which may be upto 2gig in size) will not be cached as an entity.

If we did actually cached by extent -- that would be an argument FOR, not against haveing MANY small extents (to improve the usefullness of the data in the cache -- why cache 2gig of data when all you wanted was a block? This is why 2k block sizes are still good for OLTP systems -- it improves the cache usage as you pull in and cache the data you are using, not the other data that you are not).

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

Don't go crazy and create tables with 10's of thousands of extents but do not be afraid of tables with hundreds or even perhaps low thousands of extents.

there is a performance penalty with lots of extents if you do ddl (but not DML) on it (eg: dropping a table with 1,000 extents in a DICTIONARY managed tablespace will take a while -- however, we are proposing that you NEVER drop and recreate that table in the first place so its sort of a non-issue. with locally managed tablespaces, this overhead is virtually gone, especially with uniform sizing -- i'm a big fan of uniform sizing)...

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

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.