Or indeed level 8 would do.... Niall
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Niall Litchfield > Sent: 14 July 2003 21:49 > To: Multiple recipients of list ORACLE-L > Subject: RE: Performance problems VMS 8i > > > Hi Barbara > > After an upgrade from Oracle 7.3.4 to 8.1.7.4 on > > OpenVMS, some (but not all) of our batch jobs are > > suffering severe performance degradation. One of our critical jobs > > went from 3 hours to 9 hours elapsed time. > > > > The reason is obvious. The solution is not. One of > > our jobs increased from 45 minutes to 1 hr 30 min. > > The direct i/o for this job increased from 480 to > > 1,046,938. (Identical everything. Only difference > > 7.3.4 versus 8.1.7.4) This direct i/o number is from > > the parent process - the process that is communicating > > with the detached process actually running the oracle > > code via a mailbox (using the bequeath adapter). The > > jobs causing trouble are batch jobs running on the > > server, and are using bequeath. > > This sounds like a piece of SQL has hit upon a different > execution plan. I'd recommend the following course of action > > 1. get up to date statistics as Jared says (compute them if you can). > 2. modify the job so that it does the following > alter session set events '10046 trace name context > forever, level 12'; > your job > alter session set events '10046 trace name context off'; > 3. run the job. > > You will get a trace file in the udump directory with waits > and elapsed time in it, you can run that thru tkprof. Look > for large values of elapsed time. Chances are excellent > (better than 90%) there will be 1 (or at an outside 2) > statements that take up more than an hour of your hour and a > half. Chances are pretty good ( better than 75%) that faced > with those statements you can tune them to take less than 20 > minutes - a good index, a rewritten statement. > > If you don't like all this set events stuff connect internal > to the db and run @?/rdbms/admin/dbmssupp and replace the > trace stuff with exec > sys.dbms_support.start_trace(true,true); <your job> exec > dbms_support.stop_trace(); It does the same thing. > > If in the unlikely event the above does not hold true, well > you will see what you spend your time waiting on. If it is > network stuff then maybe you can think about raising a tar > with more info. I honestly expect it to be the sql. > > Do feel free to post the results of the above, if only to > show how wrong I am. > > Good luck. > > Niall > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Niall Litchfield > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
