Re: Oracle speed issue between 8i and 9i

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:1a75df45.0310220056.3dab15a7_at_posting.google.com...
> 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

Having fought with this for a couple of weeks, I think we have solved our problem. Our create table statements (when setting up the database originally) used the nocache parameter. Changing our script to not use this, has given us performance from 9i like we get from 8i. Not sure exactly why we used this in the past - will be taking this up with the guys who originally wrote the table creation scripts. Maybe this points to better cache handling by oracle 9, but I guess we will have to see whether this has any other adverse effects on our software.

Thanks for the suggestions.

David. Received on Wed Oct 22 2003 - 23:47:38 CDT