Re: Oracle speed issue between 8i and 9i

davidm_at_thoughtweb.com (David McFarlane) wrote

> My company has used Oracle 8i quite extensively, and have started to
> look at 9i. We have noticed a huge degradation in performance from 8i
> to 9i (where both of these databases are setup using a default
> installation). A set of read, calculate and write which was taking in
> the order of 10 seconds on 8i, now takes approx 6 minutes on 9i. Our
> application has a large number of small queries that are being
> executed during this process and it's not really possible at this
> stage to re-write this process.

I have seen similar issues and both ways (i.e. where the old 8i is faster and where the new 9i is faster).

First, we need to establish a baseline. 9i performance is equal or better than 8i ito performance using the same query, same table, same physical db layout, same stats, same platform, etc. Period.

If this someone wants to dispute this, well then they are saying that Oracle's db developers downgraded the performance of 9i in comparison with 8i. And that is bullshit.

Okay, so why the performance difference?

The first factor is that it is more than 99.99% likely that your 8i and 9i physical databases and instances are *different*. And this is likely one of the major reasons why you are witnessing performance differences.

Are they on the same platform? This will give rise to resource and CPU contention.

Do they have equivalent disk i/o performance and is the 9i db properly striped?

If on different platforms, do they have the same o/s kernel settings? (in many cases 9i is also run on a newer o/s version than 8i).

The second factor is that 9i is different than 8i - it is after all a different product to 8i.

How long did it take for you to make your 8i applications work as well? I'm sure that there were performance issues in the beginning. And these were resolved - as you said, you have use 8i extensively. And I'm also sure that your 8i still no longer contains the out-of-the-box parameters.

The same process is to be expected from 9i. Upgrades can be painless, but that's not to be said that they're also mindless. :-) (i.e. thinking that upgrade means simply upgrading the db and that's it) You need to go through the 9i learning curve and fit 9i to your platform (looking at locally managed tablespaces and so on).

What then to do? You need to determine *why* the performance is different. That means IMO two things to do first: 1) monitoring wait events and stats of slow processes 2) tkprof

I found TKPROF very useful in a similar situation. I enabled it automatically (via a database logon trigger) on both the 8i and 9i databases and had them run their production software on both. Then I did a SQL command to command comparison between the 8i and 9i reports. One can then very easily identify where the performance degradation occur and then focus on those specific SQL statements to determine why.

--
Billy