RE: Performance problems VMS 8i

Or indeed level 8 would do....

Niall

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] 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: niall.litchfield_at_dial.pipex.com
>
> 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: ListGuru_at_fatcity.com (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
Received on Tue Jul 15 2003 - 15:10:20 CDT