Re: Performance problems VMS 8i

Hi!

One of first thing I'd check when migrating from 7 to 8i, is settings for optimizer_index_* parameters. And of course, your tables&indexes should be analyzed (if not still explicitly using RBO).

Tanel.

• Original Message ----- From: "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, July 14, 2003 11:49 PM 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
Received on Tue Jul 15 2003 - 06:13:20 CDT