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