Re: Oracle CBO / Performance Issue !!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Johne_uk wrote:
On 13 Aug, 16:21, DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (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-----
.

Relevant Pages

  • Re: Oracle CBO / Performance Issue !!!
    ... oracle back end performance issues. ... The app schemas are also identical - ... Don't collect statistics on the tablein question or use a histogram ... It seems odd that a query with optimizer hints would still do a FTS. ...
    (comp.databases.oracle.server)
  • Re: UTL_FILE Permissions
    ... The schema APP has already been granted READ and WRITE to the APP_DIR, ... I used the usermod command so that oracle user has a secondary group ...
    (comp.databases.oracle.server)
  • Re: Oracle CBO / Performance Issue !!!
    ... The app is quite complex and we have experienced some ... oracle back end performance issues. ... The app schemas are also identical - ... We also tried using optimizer hints on the 9208 sql to try and make it ...
    (comp.databases.oracle.server)
  • Re: Oracle CBO / Performance Issue !!!
    ... The app is quite complex and we have experienced some ... oracle back end performance issues. ... The app schemas are also identical - ... Don't collect statistics on the tablein question or use a histogram ...
    (comp.databases.oracle.server)
  • Oracle CBO / Performance Issue !!!
    ... The app is quite complex and we have experienced some ... oracle back end performance issues. ... find it strange how having stats for a schema will make things run ... The app schemas are also identical - ...
    (comp.databases.oracle.server)