Re: Cached blocks and performance

Thanks Richard for the help.

Richard wrote
> > There is no keep pool or recycle pool (yet) just the default pool. I
> > have some candidate tables and indexes for the keep pool but one of
> > the indexes attaches to a regularly modified reference table (it
> > changes but doesn't get much bigger). If I understand the manual (and
> > Jonathan Lewis' excellent book, and the current thread on caching) I
> > would need to either make my keep pool larger than really needed or
> > coalesce my index regularly to reduce the risk of it flushing warm
> > blocks (blocks that are warm enough not to be flushed from my default
> > pool and therefore the keep pool reducing performance).
>
> I'm not entirely sure I follow this.
>
> If you use a KEEP pool, yes you want to ensure it's large enough to house
> your KEEP objects, else aging occurs in this pool which you really want to
> avoid.
>
> But if your index is only being modified but not growing, then there
should
> be no need to either coalesce or rebuild your index (incrementally
> increasing index values could be an exception)
>

If I understand things correctly Oracle (8i at least) does not merge nearly empty leaf blocks when indexes have been updated and this can lead to indexes covering more blocks than required . It seems logical that regular coalescing would prevent this, and some planning is required to avoid inappropriate aging from a keep pool. Updating indexes may be bad theory but, my application is a cleansing/warehouse and some indexes feed queries.

> > 4 - poor execution plans
>
> The 'Main Man' !! And the elimination of which deserves highest priority
>
> > 5 - contention between users
>
> I don't see how this causes a query to go to disk but yes, is a possible
> cause of bad performance.
>

I just assumed the more activity going on the hotter the stuff being aged from the cache and the more likely a revisit to disc would be required.

>
> I would also add bad database design as another common reason for queries
> going to disk (and one that needs addressing sooner rather than later).
> Issues such as non-normalisation of tables, inappropriate normalisation of
> tables, unnecessary business data structures, inappropriate or incorrect
> table structures, inappropriate or missing index structures, etc. ....
>

I was a little disingenuous when I said I had ruled out bad database design. There are a number of structures that probably seemed like a good idea in Oracle 6 (before I started) but without a core system rewrite will not be going anywhere soon.

Thanks again

Jonathan Received on Sun Sep 01 2002 - 13:19:00 CDT