sunilkrishna wrote:If you are on 10g and above, you can take advantage of sql profiles for the same using Sql Tuning Advisor.
I have a table with email id as one of the column. There is a query generated by packaged application to search email id's along with some other details.
This query has an in-definite elapsed time. After some research i found out that query is not using one of the index on email id column. hence i have hinted the index and the query elapsed time reduced to 1 minute.
But now the issue is i cannot implement hint since the query is generated dynamically from packaged application.
The stats are upto date and there exists a height based histogram on email id column. Even then the query is still not picking up the index.
How to help optimizer to choose the right index which was hinted?
PrafullaNath wrote:This is not good advice - the OP is not running Oracle 8i, and probably doesn't want to wreck the rest of his system just to improve the probability that one of his queries might go faster some of the time.
If your optimizer is not using the index then you can think of setting the parameter "optimizer_index_cost_adj"
The optimizer_index_cost_adj parameter was created to allow use to change the relative costs of full-scan versus index operations. This is the most important parameter of all, and the default setting of 100 . For some OLTP systems, re-setting this parameter to a smaller value (between 10- to 30) may result in huge performance gains! and it will force to use the index.