Re: missed Anjo's webcast..

On 2002.08.08 18:04 Cary Millsap wrote:
> I agree wholeheartedly with your "no such thing as an 'overall system'"
> comment. That is key. For the record, my view on this general topic
> includes the following observations:
>
>
>
> * If you have a really high database buffer cache hit ratio (>99%), then
> you almost certainly have inefficient SQL in your application.

Yes, that is correct. My preferred tool for finding the hog is top session monitors and I sort by db_block_gets, which is an official name for LIOs. However, my experience tells me that the opposite is also true, i.e. when the BHR is <=50%, then there is a problem.

>
>
>
> * From the hundreds of situations we've seen in the past two years (we
> help people analyze at least one Hotsos Profiler run per workday), not
> one single site would have improved their performance noticeably by
> caching their entire database in memory. (It's Amdahl's Law: not one
> performance problem we've seen has been caused by too much time spent
> waiting for PIOs.)

Yes. Excessive LIOs are extremely detrimental. full table scan from within nested loops is the typical situation and it doesn't help much if the scanned table is cached in its entirety.

>
>
>
> * Nearly every slow application we've seen since 1999 either spends the
> preponderance of its elapsed time doing too many LIO calls (which
> manifests itself as an apparent "CPU problem" or a "latch contention
> problem"), or too many inter-tier database calls (which manifests itself
> as an apparent "network problem").

To be exact, people sometimes forget that Oracle*Net tends to bring the whole dataset over the network and do join locally, in the temporary tablespace. Query like
select ename,dname,loc
from emp e,dept_at_remote d
where e.detptno=d.deptno

will bring the whole dept table over the database link. Add nested loops and here is your recipie for disaster.

>
>
>
> * The solution for 99%+ of performance problems I have ever seen or
> heard of is to reduce LIO count (fix SQL), or database call count (fix
> application code). The key is to figure out *which* SQL, *which*
> application code, and *what* to go do about it.

Tkprof is instrunmental here and I've had good results with using sort=exeela or sort=exerow options.

>

As a comment, I've read both of your "hit ratio papers" from the Hotsos site and they are extremly well written and instructive. You methodology is fundamentally sound and I agree with it wholeheartedly. I'm just not yet ready to completely discard and ignore BHR. May be it's a superstiton, may be I'm in this business for too long, but I believe that it can give me an indicator whether I have a problem or not.

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).