Try clearing the statistics, then run the query. I've found that the stats generated by oracle actually cause the sql generated by Remedy to not use the indexes as expected. This is more the result of poor indexing than anything though.
One thing I've done on our Oracle servers is to set the CURSOR_SHARING to SIMILAR. This has less to do with indexes than it does with the hard parses that Oracle is forced to do for just about every sql statement. Setting this will cause Oracle to replace all literals in the where clause with a bind variable (e.g., :B1, :B2). This allows oracle to reuse the previous execution plan for statements that were structured the same. Has a large impact on memory utilization (down to a fraction) on the oracle server, and a slight positive impact on performance due to the reduced parsing. In SQL*Plus, run the query after running 'SET AUTOTRACE ON'. Grab the exec plan and stats. Remove the stats on the table(s) and index(es) in question, start a new SQL*Plus session, then run the query after running 'SET AUTOTRACE ON'. Grab the exec plan and stats. Compare the two. If the above does not help, consider changing the arrangement of your indexes (compound versus single column indexes, uniqueness, etc.) Axton Grams On 12/12/06, Daniel Bloom <[EMAIL PROTECTED]> wrote:
We are having an issue of Oracle ignoring it's indices. could someone running this environment please post what cursor and optimization settings they use, and/or something else that could cause this? thanks ... Daniel p.s. AIX for the Oracle database, MS Server2000 for the ARSystem server _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
