Re: Monitoring question

"Anton Buijs" <remove_aammbuijs_at_xs4all.nl> schrieb im Newsbeitrag news:3f0ca29c$0$49109$e4fe514c_at_news.xs4all.nl...
>
> Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de> schreef in berichtnieuws
> bee1ob$3v507$1_at_ID-152732.news.dfncis.de...
>
> Think you just hit the common performance tuning problem. Don't jump into
> the details at the beginning, but find the cause first: where are the
> sessions waiting for?

That is the very problem: I do not know (yet). It is like a temporary "traffic jam out of nothing",
with no obvious reason or "accident" when You look at v$session or v$lock; but I may have not used those
views properly enough, a poster sent me some very valuable suggestions ... I will evaluate them.
Just know I am still guessing, according to my experience with this system for what I have seen
in the last year:
old hardware, short ram, and - most important - large temporary tables that derive from a
"suboptimal" data model, which inherites its odds into the app ... and there are only few things
that I am allowed to change in it. For some things it is too late for tuning, as too
many things are basing on it. At least I was able to fix the worst things of the past ...

>Run the famous bstat/estat report in a 10 minutes
> interval one or more times during the peak period, maybe also when the db
> performs well but has significant load for comparison. If you are not
> familiar with bstat/estat use the newer tool statspack (if you need to
learn
> one: learn statspack). Check the output for what is waited for most. Is it
> IO (waited most on for instance db_file_sequential_read, check the
datafiles
> in the datafile io section), is it enqueus (blocking locks problem), is it
> for latches (check the latches session, library cache latch and shared
pool
> latch high misses -> problems with the shared pool) etc. etc. Many more
> situations possible, just giving some examples from my experience.

So far as I can tell I found mostly IO waits (accumulated), the rest of Your suggestions looked OK (well, at least to me, maybe a guru or oraperf might have judged different ...)

> Problems interpreting the output? Visit www.oraperf.com and upload the
> output file (free registration required). You will get instant advice.
> Also use OS utilities like top and sar. If sar is not running ask your
> system manager to run it, always, with a 10 or 15 minutes interval. Sar
> shows cpu load (user, system, waitio and idle time), disk io load,
runqueue
> etc.

I never used sar so far, will try it; I used vmstat and iostat, and found swapping at the critical times.

> It could be that there is a runqueue on the machine. Switching to MTS
could
> be a solution then. In my experience don't start more shared servers as
1.5
> * #cpu's. On a Sun Solaris machine I had the situation once that is was
> better sessions had to wait on an available shared server than have more
> server processes fighting to get a cpu time slice (so a runqueue again).
How
> many cpu's are in this AIX machine?

4; they are about 3 years old, no way to change that in the short run :-( ...

> Again: I mention some of my experiences, every situation is unique. First
> measure what's wrong, than take action.
> Visit www.orapub.com (free registration required, again) and download
paper
> #113 "Direct Contention Identification Using Oracle's Session Wait Event
> Views (April-2002) Craig Shallahamer"
> (http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs113) and #119. The key
> is: where did the sessions waited for the most of the time, then
investigate
> that area further. Skip the non-events, like "sqlnet message from client".
> Events are explained in the "Reference Guide", Appendix A.

I also found some valuable tips in Guy Harrisons tuning book. But maybe I have to read things one more time ...

> Quest has a tool Spotlight on Oracle (you have to pay for that), giving a
> very nice real-time presentation of what's going on in the database and
you
> can zoom in to any area. Those screens look a lot like the Toad screens
> (most of them are included in free Toad). Only the overview screen is
> impressive and makes it a nice-to-have tool.
>

That is why I asked for data dictionary tips in already existing views. Because generally, using extra tools to pay for is strongly discouraged at my place ...
So I have some limitations to my actions, unfortunately.

As a round up, Thanks to all of You who have given me good advices ! It will take some time to evaluate all of it, but now I feel better where to start from.

Jan :-) Received on Thu Jul 10 2003 - 02:48:08 CDT