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 ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, July 14, 2003 11:49 PM > 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: Tanel Poder 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).
