Re: Oracle wont use an index and performance sucks

The quick answer is that if set the optimizer mode to FIRST_ROWS the index will almost certainly be used. This may, however, have an unsuitable effect on the rest of your processing.

Another option to consider (if STATUS = 10 is an exceptional status) is to analyze generating histograms on STATUS and CUSTOMERPNO. One reason for the change is that you may have allocated the 'ton' of memory to db_block_buffers, thus increasing the size of the cache for small tables, and the _small_table_threshold so that your 8Mb table is now considered by Oracle to be a SMALL table.

Without lots of stats, (e.g. the clustering factor on the index, the space wastage in the table, the number of different values for STATUS) and your full init.ora parameter set, it is not possible to tell you exactly why the CBO has decided that a full tablescan would produce fewer I/O requests than an indexed path.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Rob Edgar wrote in message <79ea6j$fll13_at_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:54:25 CST