Although you did not show it, you mentioned that you have twelve columns used in your where clause. It is possible that the where conditions could be written better. We cannot tell without seeing them.
I have tried ORDERED hint also giving the requisite table as the last one in FROM clause but I don't find this table (which I have specified last in the FROM clause) getting read last ?
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.
If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. You might want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information lets you choose an inner and outer table better than the optimizer could.
> So, Ideally speaking, the statistics have remarkably improved.
Are you saying the query is now faster? In which case is the problem solved?
> Any solutions/pointers to my query would be highly appreciated!!!!
As would any answers to the questions you have been asked. Like the one about what percentage of rows from TAB1 you will be accessing, or the selectivity of the joins, or what happened when you tried the suggestions posted by Gabe or Barbara.
Also bearing in mind that none of us has ever heard of fnd_stats.gather_table_stats.
You've had two pages of general advice and quite a few specific suggestions and tests, many of which you haven't tried yet, and we still don't know your query, the Explain Plan output, the tkprof output, the table and index definitions, or very much about your data.
If you are going to experiment with optimizer hints one at a time, bear in mind that this board only seems to handle about 120 posts per thread.
If there is some specific thing you are stuck on, please post details.