Oracle/OpenVMS Performance Question

Background:

I am supporting a VAX 6000-560 system, with 384 mb of memory, 10 RF72 DSSI disks and 8 RF73 disks, 2 TF857 tape drives, and 2 KFMSA controllers. We are running OpenVMS V5.5-2 and Oracle V6.0.36.?. The system uses an application called Banner, which is an Oracle based financial application. FYI, our Oracle SGA is 32 mb.

Our disks are setup as follows:

        KFMSA-1                       KFMSA-2

DSSI-1		DSSI-2		DSSI-3		DSSI-4

System Disk	Page/Swap	Database	Database
Database	Database	Database	Database
Database	Database	Oracle Archive	Database
Oracle Exports	Database	User Disk	Database	
		Oracle/Banner
		Sys Perf Data		

   RF72s	   RF72s	  RF73s		 RF73s

Our spike peak CPU utilization very rarely goes above 70%, our average Prime Time CPU Utilization rarely goes above 40%. Our Memory Utilization averages about 60% (very rarely do we peak above 65%). Also, our page faulting is very low (1% hard, less than 100 faults/VUP soft' most of our page faults are Global Valid (90%). We support an average of 60 user processes, in addition to 4 Oracle processes. In adition to Oracle, we have the following DEC products active: Security Tool Set (INSPECT, RID and DSRF), and Polycenter Performance Data Collector. We do not run the Defragger during the day, and very rarely do we run SLS (average about once a month to backup about 100 mb of data).

Our network is TCP/IP based, with limited DECnet. All our users connect to the VAX 6000-560 via TCP/IP (MACs, PCs and DECservers). We only use DECnet for copying files between nodes. MOP is used to download our LPS20 printers and DECservers. The VAX, PCs and Printers are on their own ethernet segment and are connected to a Cisco router. We are experiencing a collision rate of less than 1%.

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.

Situation:

We are experiencing slow response time and a very low transactions/second by our users. External to Oracle, things look and act very fine, but internal to Oracle and Banner, things run very slow. Some users report a 5 minute response time in performing a commit. 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. One should expect the opposite results.

We support a mix of interactive users and batch jobs. A recrurring batch jobs runs every 15 minutes, at priority 4. The interactive users and Oracle run at priority 4. The remaining batch jobs runs at priority 3. The batch jobs consist of payroll, accounts apayable and some large reporting jobs. 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.

Of course, this problem could be related to the way the application is written or to how the Oracle database is set up. I personally do not believe that OpenVMS, VAX, or the DEC products are functioning poorly. I will provide Performance Reports, upon request. The size of the reports exceed the 60 block limit for DSNlink.

Any ideas would be most welcome. Thanks.

-- 


==============================================================================

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          | 


==============================================================================