Using Oracle hint "FIRST_ROWS" to improve Oracle database performances
database, oracle, performance, query-hints
Solution
1) What are possible reasons for bad performance when userid = 2 (when hint is not used)?
Because Oracle thinks that one of the interim result sets using the plan from (userid=1) will be very large - probably incorrectly.
2) Why would execution plan be different for one vs another statement (when hint is not used)?
Histogram based indexes
3) Is there anything that I should be careful about when deciding to add this hint to my queries?
As long as the number of records being returned is small, this hint should be failry safe - unlike pushing the optimizer to use a specific index, this approach allows Oracle to pick a different plan if the indexes are changed.
Problem
I have a statement that runs on Oracle database server. The statement has about 5 joins and there is nothing unusual there. It looks pretty much like below: ``` SELECT field1, field2, field3, ... FROM table1, table2, table3, table4, table5 WHERE table1.id = table2.id AND table2.id = table3.id AND ... table5.userid = 1 ``` The problem (and what is interesting) is that statement for userid = 1 takes 1 second to return 590 records. Statement for userid = 2 takes around 30 seconds to return 70 records. I don't understand why is difference so big. It seems that different execution plan is chosen for statement with userid = 1 and different for userid = 2. After I implemented Oracle Hint FIRST_ROW, performance become significantly better. Both statements (for both ids 1 and 2) produce return in under 1 second. ``` SELECT /*+ FIRST_ROWS */ field1, field2, field3, ... FROM table1, table2, table3, table4, table5 WHERE table1.id = table2.id AND table2.id = table3.id AND ... table5.userid = 1 ``` Questions: - What are possible reasons for bad performance when userid = 2 (when hint is not used)? - Why would execution plan be different for one vs another statement (when hint is not used)? - Is there anything that I should be careful about when deciding to add this hint to my queries?