I received an email earlier on this year asking me my opinion of the first_rows option for the optimizer mode. My correspondent was looking at a database with the following settings:
He felt that first_rows was a very old optimizer instruction that might cause suboptimal execution plans in its attempt to avoid blocking operations. As for the cost ratio, no-one seemed to be able to explain why it was there.
As for the _sort_elimination_cost_ratio – it’s (probably) there to work around the problems caused by first_rows optimisation when you have an ORDER BY clause that could be met by walking an index as well as a WHERE clause that could be met from another index. Under first_rows the optimizer is highly likely to choose the index for the order by to avoid sorting; but the _sort_elimination_cost_ratio says: “if the cost of using the index for the ORDER BY is more than N times the cost of using the other index for the WHERE clause then use an index on the WHERE clause and sort the result.”
- set first_rows because “it’s an OLTP system” – a myth that even the manuals once promoted
- found lots of queries taking silly index-driven execution plans because they’d use (say) a primary key index to access and discard vast amounts of data in the required order, instead of picking up a small amount of data using a better choice of index and then sorting it.