From: CRDGW2::CRDGW2::MRGATE::"SMTP::CRVAX.SRI.COM::RELAY-INFO-VAX" 20-AUG-1989 18:08 To: MRGATE::"ARISIA::EVERHART" Subj: Re: Use of ORACLE on VMS Received: From KL.SRI.COM by CRVAX.SRI.COM with TCP; Sun, 20 AUG 89 14:42:12 PDT Received: from ucbvax.Berkeley.EDU by KL.SRI.COM with TCP; Sun, 20 Aug 89 14:24:35 PDT Received: by ucbvax.Berkeley.EDU (5.61/1.37) id AA12479; Sun, 20 Aug 89 14:12:11 -0700 Received: from USENET by ucbvax.Berkeley.EDU with netnews for info-vax@kl.sri.com (info-vax@kl.sri.com) (contact usenet@ucbvax.Berkeley.EDU if you have questions) Date: 13 Aug 89 15:35:24 GMT From: att!icus!dasys1!walrus@ucbvax.Berkeley.EDU (David Filip) Organization: The Big Electric Cat, NYC, NY Subject: Re: Use of ORACLE on VMS Message-Id: <10512@dasys1.UUCP> References: <89Aug2.090514edt.57423@ugw.utcs.utoronto.ca> Sender: info-vax-request@kl.sri.com To: info-vax@kl.sri.com In article <89Aug2.090514edt.57423@ugw.utcs.utoronto.ca>, 01696@AECLCR.bitnet (Chris Tanner) writes: > I hope that this is the correct place to ask this question? We are running > Oracle on an 8550 (running VMS 5.1). We are experiencing performance problems > when a few of our Oracle applications are busy. I was wondering if anybody > has had similar experience and what they did about it? > > Chris Tanner > Chalk River Nuclear Labs We are currently rolling out an application distributed over 86 VAX nodes, ranging from 3300's to 6340's, all running Oracle, and most of the problems we have had are related to performance. Although I cannot give you any secrets that will solve all of your problems, I will share a few of the things we have done to help performance (and I hope anyone else on the net will similiar advice will post it to the net as well) : First, I assume that you are using v5 of Oracle, since v6 is still quite buggy, and even our sales rep has warned us against moving to v6 until the next release (we are running v5.122 both on v4.7 VMS and v5.1 VMS, and everything I am about to say applies to both environments) 1. First, the obvious is to increase the working sets of all Oracle users; we are using : WSDEF 500 WSQUO 1024 WSEXT 5000 although we rarely have enough memory in the machine to get up to WSEXTENT. 2. The Oracle detached processes (ORACLEx$BWR, ORACLEx$ARH, ORACLEx$BIW, and ORACLEx$CLN, where 'x' is the Oracle instance) page fault like crazy. Don't bother raising the WSDEFAULT of the UAF record for Oracle, becuase the value is HARD CODED into IOR !!! Oracle has a patch tape which doubles the working set sizes of these processes ... contact your sales rep. 3. As with any I/O intensive application, MAXBUF should be increased (SYSGEN always seems to calculate too small a value). We are using 8192. 4. Look for resource contention within the application you are using; use ODS to look for table locks. Oracle has this nice feature whereby if you maintain too many row level locks for a given period of time, it will escalate you locks to a table level lock (which is less overhead for Oracle). To eliminate this, if possible, break up your tranactions into smaller units of work, and perform COMMITs wherever possible (if you lock a table, every other process needing access to that table will HIBernate until you release that table). Also remember that in v5 of Oracle, there is no true row level locking ... Oracle locks the 2K bucket that contains the 'row' that you are locking, so you may be locking more than you think). 5. Do not index any table containing a couple hundred rows or less. Doing so will actually slow down SELECTs slightly, and kill you in INSERT/s/UPDATEs. 6. Although I am going out on a limb for this, and our Data Administration people would disagree, be very careful when you JOIN two tables, especially when there is a very dissimilar number of rows. We had view which joined a table with a couple dozen rows with a table with a couple hundred thousand rows, and it took upwards of 30 minutes to to a single row SELECT. By de-normalizing the table, we got it down to about 2 seconds ! Although normalizing is generally a very good thing with relational databases, what looks nice in an Entity Relationship Diagram, may perform VERY poorely once implemented (I would ask any ERD advocates to please not yell at me too much for that one !). That is about all that we ha done so far ... hope some of it helps. As you can probably tell from this message, I am a VMS techie, and not an Oracle guru, and have found tuning Oracle systems very different from tuning other VMS systems I have worked on. If anyone else can suggest something obvious which I have missed, I would greatly encourage that it be posted to this topic area, since I think a lot of people are probably in the same boat. Regards, Dave. -- David Filip Big Electric Cat Public UNIX ..!cmcl2!phri!dasys1!walrus