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
Yes. It could also be the data is different or that the clustering
factor is different or init parameters are different, or any number
of other factors. But definitely ... something IS different.
--
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:40:16 CDT
