Re: Cached blocks and performance

"Jonathan Bliss" <bliss_jonathan_at_hotmail.com> wrote in message news:ae530df0.0208300137.219a8e1c_at_posting.google.com...
> Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message
news:<E2F6A70FE45242488C865C3BC1245DA702935A68_at_lnewton.leeds.lfs.co.uk>...
> > Oracle caches blocks in either the DB_BUFFER_CACHE or the KEEP_POOL or
> > the RECYCLE_POOL (depending on the version and any options you specified
> > when creating the object of course).
> >
> > So if a read requires a single row from a table, Oracle has to check if
> > it is already in the cache and if so this counts as a logical read. If
> > not, go to the disc and read one block Oracle block that is) into the
> > cache (= one physical read) then read from the cache (= one logical
> > read).
> >
> > Does this help ?
> >
> > Regards,
> > Norman.
>
> Thanks Norman,
>
> The system I am working with (8.1.7.x mostly NT - Data
> Cleansing/Warehouse) was originally developed in Oracle 6 and I was
> originally an application specialist not a DBA. (These are my excuses
> and I am sticking to them)
>
> 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)

>
> This relates to my earlier question, basically I think performance is
> being adversely affected because sometimes I am accessing more blocks
> than I should have to, to get the rows I need back.

This is the crux to tuning. Ensuring your applications get the job done with as little work as possible. If you unnecessarily access more blocks, that directly impacts performance.

Although my
> overall cache hit ratio is good (I've read the myths thread and know
> it isn't an indicator to be used on its own)

Good. This statement should keep you relatively flame proof :)

some queries seem to
> generate excessive I/O activity, i.e. Physical reads much higher for
> one of two queries that have similar execution plans and similar rows
> returned.

Focus first on the 'logical' I/Os. Get these down to an absolute minimum. All things being equal, once you have successfully reduced the logical I/O's, many of the so-called physical I/O issues will disappear. Then concentrate on reducing physical I/O issues by tuning the cache, defragmenting the tables, eliminating migrated rows, etc.

What forces a query to go to disc? As far as I can gather
> among other things
> 1 - Not enough cache.

Yes. But you need to tune the applications before you can determine if this is really an issue per se ...

> 2 - Sparse rows in objects (too large pct free, stale index leaf
> blocks, rows selected just happen to be spread across many blocks)

"Wasted" space below the HWM of an object is always best avoided. Use DBMS_SPACE to determine how much of an issue this is. The clustering_factor or 'rows selected just happen to be spread' is also a possible issue as discussed previously.

> 3 - Rows chained across blocks (too small pct free, regular updates)

Analyse the tables and check the chain_cnt in *_tables. A non zero value means either migrated rows (which are best eliminated) or large rows that can't fit in a block (which only a larger block size would eliminated)

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

> Which of these is true in my case, I think I have ruled out 1,4 and 5
> could it be 2 or 3, how can I diagnose this, what is the cure?
>
> Any thoughts welcome

Cheers

Richard Received on Fri Aug 30 2002 - 08:26:45 CDT