On Aug 13, 11:21 am, 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 -
9i may be desupported, but extended support will be available for many
many years. If everyone was as fortunate as Daniel, then we will be
using 11G, But in industry environment it takes years (forever) to
convince your business managers to move to a new release,
Received on Fri Aug 17 2007 - 00:10:28 CDT
