Using Oracle hint "FIRST_ROWS" to improve Oracle database performances

Question

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:

1. What are possible reasons for bad performance when userid = 2 (when hint is not used)?
2. Why would execution plan be different for one vs another statement (when hint is not used)?
3. Is there anything that I should be careful about when deciding to add this hint to my queries?

Cate

Are your table statistics up-to-date and accurate? – Vivienviviene

be careful about caching when running performance comparisons. – Enzootic

Tebbe - Statistics are up to date. Are they accurate, I don't know. – Cate

Answer 1

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.

Jaquelynjaquenetta

Thanks for your quick response. The most puzzling thing is that we didn't have this issue for 6 years until 1 month ago (when we upgraded to new version of Oracle). Since then performance started getting worse and worse (for some users). Are there any actions that could be done on the Oracle server to optimize performance? Thanks. – Cate

Do you keep up to date your statistics? It would be good to indeed analyze whether you need histograms or not – Mindszenty

Are you using bound variables and switched to a DBMS with bind-peeking enabled? – Jaquelynjaquenetta

This might be related to METHOD_OPT changing its default behavior: richardfoote.wordpress.com/2008/01/04/… – Whereat

Well, I am not dba - he said that statiscs are ok. Thank you jonearles. That might be it... will look into that – Cate