Oracle wont use an index and performance sucks

From: Rob Edgar <robedgar_at_hkstar.com>
Date: Fri, 5 Feb 1999 16:30:20 +0800
Message-ID: <79ea69$m404@news.hk.linkage.net>

Since we upgraded to 805 from 733 a month ago we have been having performance problems, not across every tbale but on certain specfic tables.

An example is a small table of 8000 rows occupies 8mb in 4 extents with an index on STATUSNO, CUSTOMERPONO

The follwoing statement gives an execution plan of a full table scan and then a sort

SELECT *
FROM XYZ
WHERE STATUSNO = 10
ORDER BY CUSTOMERPONO Now this takes 30 secs to execute versus less than 1 sec on 733. But why is it doing a full table scan when there is and index available.

I have analyzed the table and the index but it makes no difference.

The optimizer mode is set to CHOOSE which is the default.

The server has an absolute ton of memory 512mb which is double what we had under 733 partly to offset the performance issue.

The datbase was installed about 2 years ago and not really touched again until we just upgraded and during those two years the performance has been simply amazing without any problems but now it sucks big time in certain specific instances.

Does anyone have some suggestions on what migth need fixing..

Rob Received on Fri Feb 05 1999 - 02:30:20 CST