topic Re: VMS and Oracle in Operating System - OpenVMS

VMS and Oracle

Jlienema — Tue, 27 Feb 2007 16:03:35 GMT

Hello,
We are running VMS 8.2 and Oracle 9.2.0.5, I am being told that our application is slow. I have an ES45 4 processor's and 16gb of memory. I will attach the output of monitor mode and disk, show mem/cache. As far as I can tell this system isn't doing anything.
Certain parties are trying to say that my systems aren't tuned or I have disk contention. I was wondering if anyone can give me any pointers on how to monitor a system for Oracle or how to tell if the database is properly tuned.

Thanks Julie

Re: VMS and Oracle

John Travell — Tue, 27 Feb 2007 19:02:57 GMT

Julie, You do not say how long the Monitor disk was left running for, it looks like a brief snapshot rather than from an extended run, but I would certainly concur that your system appears on the face of this evidence to be doing nothing...
In truth we need a lot more information about your environment before any diagnosis could be given. Where are the consumers of the data in the Oracle database ? Logged into the machine, or elsewhere on your intranet ? If the latter, the entire problem could be external to VMS.
Should the need arise, there are a number of contributors to this forum who can offer expert paid consultancy. Unfortunately, I am the wrong side of 'the pond' to help more than a reply here.
JT:

Re: VMS and Oracle

Hoff — Tue, 27 Feb 2007 19:34:23 GMT

Hello and welcome to ITRC.

Fire up MONITOR and run a recording pass over a week or two. You might want to restart the job on a daily basis, but you get the idea, I'd run MONITOR ALL set to record data to a file at intervals of, say, five or ten minutes. This is your baseline performance collection. The interval is fairly long to avoid accumulating stonking huge wads of data.

I'd prepare a second MONITOR command (with recording) with a much shorter interval (say fifteen seconds) and have this ready to go upon the arrival of a verifiable performance complaint. This to capture the specific interval. This can accumulate said stonking huge files, so mind your disk space and how long you run this.

If you should have available disk storage to burn, have at.

Next wander over to the HP OpenVMS site and rummage around for a copy of the T4 tool. This will help you analyze the MONITOR data.

And poke around inside Oracle itself, and set that up for performance monitoring as best you can manage. Statspack or its analog, if you can manage it.

With Oracle and MONITOR, you're typically looking at the MODES activity first, and specifically exec-mode activity.

As for Statpack, that's Oracle DBA-land. But with the MONITOR data, you can show that OpenVMS is or is not busy when the performance degrades.

The disk queue length, FWIW, should be 0.5 or less and preferably less. A sustained value of 0.5 indicates half of all disk I/O is waiting for a previous I/O; that there's either an overload or an under-bandwidth lurking in the disk path. There are spikes in disk activity likely on most any system, and it really boils down to what the I/O looks like, what memory caching can be enabled, etc., and your tolerance for throughput.

For grins and giggles, also look at system parameter settings, and ensure these meet Oracle requirements, and ensure that the process quotas for the usernames also match, etc. Also take a look at using the AUTOGEN with FEEDBACK over a week or a month, to better dial in the parameter settings.

What was provided for information from MONITOR looked to be a near-quiescent system. This implies that the abominable performance monster wandered past when the camera wasn't recording, or it's not these particular monitored areas of OpenVMS that are feeding the monster.

If you wish to investigate how HP indicates a system performance and tuning operation can be conducted for OpenVMS, there's the performance management manual in the OpenVMS documentation set.

Stephen Hoffman
HoffmanLabs

Re: VMS and Oracle

Robert Walker_8 — Tue, 27 Feb 2007 21:08:57 GMT

Hi,

Oracle is very reliant upon UNDO and REDO logs. These are very write intensive files and should be moved to spindles and busess away from the data.

Indexes are also a problem, left on for mass updates slows things down and not on for lookups slows it up as well. Maybe someone left an index off - this has happened to us in the past and is a usual suspect.

Robert.