Using Oracle hint "FIRST_ROWS" to improve Oracle database performances

database performance oracle hint
bobetko picture bobetko · Jun 11, 2012 · Viewed 31.5k times · Source

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?

Thanks

Answer

symcbean picture symcbean · Jun 11, 2012

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.

Related questions

How do you interpret a query's explain plan?

When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out …

Read More
Why is Select 1 faster than Select count(*)?

In Oracle, when querying for row existence, why is Select 1 fast than Select count(*)?

Read More
How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More