Re: Oracle CBO / Performance Issue !!!

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 Received on Mon Aug 13 2007 - 10:36:53 CDT