Re: Oracle CBO / Performance Issue !!!

On Aug 13, 2:40 pm, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> Johne_uk wrote:
> > On 13 Aug, 16:21, DA Morgan <damor..._at_psoug.org> wrote:
> >> Johne_uk wrote:
> >>> Hi,
> >>> We have just introduced a new application (java based) which accesses
> >>> an Oracle 9i db. The app is quite complex and we have experienced some
> >>> oracle back end performance issues.
> >>> Explain plans indicate that certain indexes were not being used on the
> >>> instance (9208 on Solaris) whereas when we tested the same app on a
> >>> Linux server (9207) it used indexes where practical.
> >>> One particular sql ran in 57s on the 9208 and 4s on the 9207.
> >>> Strangely, enought when I deleted statiscs on the 9208 instance it
> >>> executed in about 4 secs also.
> >>> Both instances have CBO set as CHOOSE and I can't see any notable
> >>> differences in the init.ora to suggest what the problem is. I just
> >>> find it strange how having stats for a schema will make things run
> >>> slower.
> >>> The app schemas are also identical (used schema comparison tool) -
> >>> they were dervied from the same export dump. All indexes are valid on
> >>> 9208 schema.
> >>> We also tried using optimizer hints on the 9208 sql to try and make it
> >>> use indexes but it still opted for full tables scans.
> >>> Just wondering where to start looking at this problem from.
> >>> Any guidance would be appreciated
> >>> Thanks in advance
> >>> John
> >> Seems to me you already found the issue. Your statistics are leading
> >> the 9i (now obsolete and in desupport) optimizer to make a bad choice.
> >> Don't collect statistics on the table(s) in question or use a histogram
> >> or hints.
> >> --
> >> Daniel A. Morgan
> >> University of Washington
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>
> > Hi,
>
> > Yes the statistics seem to be the main issue. Out of curiousity -
> > could the CBO be working differently on the Linux/9207 instance (which
> > runs fast using stats) compared to the Solaris 9208 instance because
> > of the diff patch level / OS.
>
> > thanks
>
> Of course, you could test by exporting statistics
> from your 9207, and import them into 9208.
> If the problem emerges - it's not the stats.
>
> Then again - you should not test in production, and
> tests should have been conducted on same platform,
> at similar or same conditions.
>
> - --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
>
> iD4DBQFGwKWNLw8L4IAs830RAp+gAJ4xDpl6cYYWO9mrzp09CS/M67SzNQCWOETm
> JjPjewiHxa1i901gwIymKg==
> =YlY5
> -----END PGP SIGNATURE-----
Do you have explain plans for the queries in the two instances (when they both have statistics)? Is the data the same in both instances?

It seems odd that a query with optimizer hints would still do a FTS. What hints did you give it?

Jon Received on Tue Aug 14 2007 - 13:10:29 CDT