Re: Oracle/OpenVMS Performance Question

Nick,

Kudos to you for being so thorough in your description! Seems that you come from a system mgmt background, true? Do you have a DBA on site? Are you the DBA, too? Let's go through some of your post...

In article <1994Oct10.142138.1_at_rhine.is.rice.edu>, nmetro_at_rhine.is.rice.edu (Nick Metrowsky) wrote:

> FYI, our Oracle SGA is 32 mb.

You may want to consider raising this. More on this a bit later.

> Our disks are setup as follows:

You didn't identify which disks were being used for redo logs (as opposed to your garden-variety database disk). Could you provide that? Commit times are directly related to how quickly redo information can be written to the log file. If the LGWR process is having to compete with other traffic on the DSSI (to other disks or the same disk), this could impact your commits...although, from the sounds of it, you have other problems. But I'm trying to be thorough for you.

> Our spike peak CPU utilization very rarely goes above 70%, our average
> Prime Time CPU Utilization rarely goes above 40%.

Interesting, but a bit too coarse. What is happening by CPU? Do a $ MONITOR MODE/CPU to figure out if your primary CPU (the one responsible for I/O completion) is getting swamped.

> Our Memory Utilization
> averages about 60% (very rarely do we peak above 65%).

Cool...you should determine if Oracle could make efficient use of this memory that isn't doing anything for you currently. First, determine your current buffer cache hit rate using the statistics available in ESTAT/BSTAT (in your ORA_RDBMS directory).

   Cache hit ratio

• 1 - ( physical reads / ( db block gets + consistent gets)).

Next, raise the size of the buffer cache (change DB_BLOCK_BUFFERS) and check the hit ratio again. Better? Then keep on giving Oracle more memory... No change? Why waste the memory on Oracle if it isn't going to use it...

You can also use the X$KCBRBH table to estimate improvements in cache hit ratio without actually adding the buffers (as described above), but if it were me, I'd do it the hard way.

> Also, our page
> faulting is very low (1% hard, less than 100 faults/VUP soft' most of our
> page faults are Global Valid (90%).

The Global Valid Faults are probably because the working sets of your Oracle users are much smaller than the size of the SGA. This is not necessarily a bad thing, especially if your users may run some other less memory-friendly application than Oracle. (If I get even just ONE flame for saying that Oracle is memory-friendly, I will post a very long explanation as to why I say that, how Oracle uses OpenVMS memory, and the problems the two have with each other...you've been warned!)

> Across all disks, disk queue lengths average around 1 or less. We have seen
> a particular disk very rarely go above 1 for a queue length. The I/O rate is
> well below 1mb/second (averages about 300kb/second). We have seen it peak
> at a maximum of 800 I/Os for a disk. The number of I/Os per second average
> below 100/second *the learges on any particular disk was 80 I/Os per second).
> The disks are nearly all contiguous, and the DEC Defragger states that all
> disks fall into the good or excellent range. The disks are rated at a
> 2.0 mb/second maxiumum transfer rate and 50 I/Os/second, the controllers
> can handle over 500 I/Os/second and over 8 mb/second maxium transfer rate.

What was that about 80 I/O's sec? To which disk? What Oracle files (if any) are on that disk? Hmmm...

> External to Oracle, things look and act very fine, but ...

Seems like. You've tuned the OpenVMS knobs pretty well.

> Some users report a 5 minute
> response time in performing a commit.

Yikes! Depending on what your users are doing, this sounds *way* unacceptable.

> I ran the DEC Performance
> Capacity Planner Software (V1.1) and it gives me user response time in
> double digits and transactions/second well below less than 1/second.

Hmmm, some DEC software that can read Oracle user response times and transactions per second? Did I get this wrong?

> All batch and interactive processing is run between the hours of 8:00 AM and
> 5:00 PM. I have strongly recommended against doing this practice, but my
> warnings go unheeded. Because of our low CPU load, this seems like a
> contributing factor, but I do not think it is the only cause.

Batch jobs during the day? I know you know this, but jobs are done in batch many times because they are going to take a long time. These jobs could be interfering with your on-line users. Could you try one day without batch jobs running?  

> Of course, this problem could be related to the way the application is
> written or to how the Oracle database is set up.

Yup! There are Oracle tools (Explain plan, TKPROF) for analyzing the performance of the application at the SQL statement level. Then again, you probably don't care since you bought this package (as opposed to writing it yourself), right?

> I personally do not
> believe that OpenVMS, VAX, or the DEC products are functioning poorly.

Agreed. You could probably due a couple of things around memory usage, but that's not the silver bullet.

Additional information I'd be interested in:

   o cache hit ratio
   o $ MONITOR MODE/CPU results, especially for the primary CPU    o what disk is seeing 80 I/O's per sec

One last thing you can do. Check out the V$SESSION_WAIT table to figure out who's waiting at any given point in time and why. I have a little C application to query that table and present the results in a kind of MONITOR-esque fashion. If you can compile it, I'll post it for you...it's not *real* robust, but it gets the job done.

> ==============================================================================
> Nick Metrowsky |
> System Programmer | Internet: nmetro_at_rice.edu
> Rice University |
> P. O. Box 1892 | Phone: (713)285-5409 FAX: (713)527-6099
> Houston, Texas 77251-1892 |
> ==============================================================================

Thanks and good luck, Nick!

BTW, is this information generally useful to the rest of you NET people?

-- 
Jef
  "...I wouldn't normally do this kind of thing." -- Pet Shop Boys