Re: Is there a performance limit of extents?

In article <956170241.12883.2.nnrp-03.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> There is a little room for hand-waving in this
> area when using parallel query. (Though the
> 'cache an extent' line is incorrect).
>
> When calculating the rowid ranges to pass
> to parallel query slaves, there is an algorithm
> that accumulates contiguous extents, then
> divides them up and passes them out on
> a nominal 9:3:1 ratio.
>
> If you have large numbers of relatively small
> extents which are NOT adjacent to each other
> then the Query co-ordinator can end up passing
> out a very large number of very small chunks to
> query slaves, which can result in a silly amount
> of overhead. It is therefore sensible to aim for
> a relatively low number of extents per segment
> on objects likely to be targeted by Parallel Queries.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Thomas J. Kyte wrote in message <8dkg4r$jl5$1_at_nnrp1.deja.com>...
> >In article <956092535.16586.0.pluto.d4ee154e_at_news.demon.nl>,
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> >
> >> 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.
> >
>

Excellent point! But since PQ works best with larger objects this is another reason to separate very large objects into separate tablespaces from very small objects to begin with. I am a strong believer in trying to standardize the extent sizes as it does make DBA life easier.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


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