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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Aug 13 2007 - 10:21:10 CDT
