Re: Poor performance after oracle 8i upgrade

From: Matt <mccmx_at_hotmail.com>
Date: 7 Sep 2004 06:12:09 -0700
Message-ID: <cfee5bcf.0409070512.10bc66f0@posting.google.com>

> The DBA's tried the upgrade 2 different ways
> 1. upgrade original database from 8.0.5 to 8.1.7.4
> 2. built brand new database from scratch then importing the data.
>
> Both ways have the same performance problem.
>
>
> Does anyone have any idea why this is occurring on 8.1.7.4 and not on
> 8.0.5, and how we can fix it.
>

The first thing I would do is make sure that your statistics are up to date on your tables... If you used step 1 above this should be the case, but confirm this anyway.

Query user_tables for last_analzed and num_rows...

If these are up to date you need to run the process under 8.0.5 with sql_trace enabled and then compare this with the same for 8.1.7.

Use tkprof to format the trace files and sort by "prsela,fchela,exeela" to identify the heaviest SQL and determine the difference between the 2 elapsed times.

If you get vastly different values for 'cpu time' and 'elapsed time' then you may need to enable a 10046 (level 8) trace to identify the wait events.

Good luck..

Matt Received on Tue Sep 07 2004 - 08:12:09 CDT