Oracle CBO / Performance Issue !!!

From: Johne_uk <edgarj_at_tiscali.co.uk>
Date: Mon, 13 Aug 2007 13:46:50 -0000
Message-ID: <1187012810.611397.110620@r34g2000hsd.googlegroups.com>

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