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 Received on Mon Jul 14 2003 - 14:50:14 CDT