Some glance plus reporting help

[Liam Curtis]

Hi All,

We tend to get very high disk utlization on our Oracle DB, and everything seems tuned okay. The disk utilization centers around the database and index filesystems, which would be expected, but I'd like to try and further tune things.

I've read the docs, searched the forums, called HP, and I still am a bit lost. I've got the glance basics down and can navigate fine through GPM, which led to my finding about high disk utilization.

Are there some down to earth tutorials out there for using Glance Plus? What I would like to do is be able to capture statistics over a week or more, so I guess to break it down:

1)Do you need to have the glance process (glance or gpm)running interactively to capture data or does a portion of it run as a daemon?

2) Are there certain counters you can report on after the fact or do you need to set counters up when trying to capture data (aka Windows Perfmon.)

3) any other great tricks or tips for analyzing system performance?

thanks!

[mobidyc · Accepted solution]

Hello,
you can look this thread for method using glance in background:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1114975

however, you can use sar (man sar(1m), sa1(1m), sa2(1m)) for system utilisation.
in my crontab i have:
0,2,4,6,8,10,12,14,16,18 * * * 0-6 /usr/lib/sa/sa1
20,22,24,26,28,30,32,34,36,38 * * * 0-6 /usr/lib/sa/sa1
40,42,44,46,48,50,52,54,56,58 * * * 0-6 /usr/lib/sa/sa1

this command run sar -a and write files in /var/adm/sa/saX
(the X is the day of the month).

you can run:
sar -a -f /var/adm/sa/sa01
for system utilisation informations.

you can use ksar from any computer for analyze this file graphically:
http://sourceforge.net/projects/ksar

it's a jar file so, you just need java to launch it, i use it from my graphical workstation.

hope this helps you.

Regards,
Cedrick Gaillard

[Liam Curtis]

Thanks much. Is there anyway that I can use Glance to report on the measureware agent I have running? (yes..I only just realized we are running that.)

[A. Clay Stephenson]

You should note that Glance (and especially Glance alarms) is very misleading when it reports high utilization. Glance (or any other host-based performance tool) doesn't have a clue whether or not a "disk" is in fact a single physical disk or is an array "disk" which is actually comprised of many physical disks. What might be extremely busy for one might be nearly nothing for the other. The more important metrics include disk queue lengths and average service times. If the queue lengths are small then you are fine.

Perhaps the most overlooked aspect of disk i/o is the SQL code itself. Poorly written (or inadequately indexed) SQL can outdo your best tuning and i/o layout efforts.

[Bill Hassell]

Glance and Measureware are excellent tools for monitoring your system both in realtime and over long periods (days). Glance comes in two forms: glance the program which is character-based and gpm which is Xwindows based. If you have an X11 display device (ie, a PC with an Xwindow emulator), gpm will give you a LOT of data very quickly and can show trend over minutes to hours.

Measureware is the collection system that produces the log files in /var/opt/perf/datafiles and extract (man extract) can pull out whatever measurements you would like. The place to start is to /opt/perf/ where there are many, many documents and examples to show how things can be measured. extract can easily create Excel data files (.wk1 files). The paperdocs directory has all the .pdf files you'll need.

Measureware (and glance/gpm) track several hundred different measurements as shown in /opt/perf/paperdocs/mwa/C/metrics.txt, all available all the time from the log files.

But all those measurements need to be interpreted -- there is very likely nothing wrong with your system and only small improvements are possible by changing disk layouts. In other words, there is no go-fast button. The metrics will tell what is being consumed. It is up to you to explain this to the developers and DBAs. It is VERY simple to have Oracle run extremely inefficiently - just leave out important indexes and let the database search sequentially. The DBA will say everything is fine because the results are there, but the underlying method is flawed. Get Oracle's Statspack to see the real picture of performance.

[Hein van den Heuvel]

>> We tend to get very high disk utlization on our Oracle DB, and everything seems tuned okay. The disk utilization centers around the database and index filesystems, which would be expected, but I'd like to try and further tune things.

From the system level that's probably about all you need to know / confirm. The next level of detail, and any corrective measure will have to come from Oracle in all likelyhood.

Just run a couple of statspack reports over representative test windows (morning peak, nightly close, 9 - 5, whatever.). Take your snapshots at level 7 or 10.
I would encourage to focus on the various areas in the day / week, not 24 windows or 7 day windows. Those dilute/average out too much and will not allow for progress verifications in the future.

The statspack report will have
- top wait events -> more confirmations
- the top queries -> application code to pay attention too, object to tune.
- the per-tablespace, per-file io stats. -> thos will tell you better then GPM how many IOs, which files, what timings
- the hot object lists.
- SGA tuning hints.

Work with your DBA(s) allow them to explain what needs to be done. Explain to them the system resources used / available. Open your kingdom, don't protect it. Hope they will reciprocate.

Good luck!

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Liam Curtis]

All excellent suggestions and have me headed in the right direction. Cant thank you enough for taking the time to reply...