database 使用 Oracle 提示“FIRST_ROWS”提高 Oracle 数据库性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10981505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Oracle hint "FIRST_ROWS" to improve Oracle database performances
提问by bobetko
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:
我有一个在 Oracle 数据库服务器上运行的语句。该语句有大约 5 个连接，没有什么异常。它看起来很像下面：
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.
问题（有趣的是）是 userid = 1 的语句需要 1 秒才能返回 590 条记录。userid = 2 的语句大约需要 30 秒才能返回 70 条记录。
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.
似乎为 userid = 1 的语句选择了不同的执行计划，为 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.
在我实施了 Oracle Hint FIRST_ROW 之后，性能变得明显更好。两个语句（对于 id 1 和 2）都在 1 秒内产生回报。
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?
问题：
1) 当 userid = 2 时（不使用提示时）性能不佳的可能原因是什么？
2) 为什么一个语句和另一个语句的执行计划会不同（当不使用提示时）？
3) 在决定将此提示添加到我的查询时，有什么需要注意的吗？
Thanks
谢谢
回答by symcbean
1) What are possible reasons for bad performance when userid = 2 (when hint is not used)?
1) 当 userid = 2（未使用提示时）时，性能不佳的可能原因是什么？
Because Oracle thinks that one of the interim result sets using the plan from (userid=1) will be very large - probably incorrectly.
因为 Oracle 认为使用来自 (userid=1) 的计划的临时结果集之一将非常大 - 可能是错误的。
2) Why would execution plan be different for one vs another statement (when hint is not used)?
2) 为什么一个语句和另一个语句的执行计划会不同（当不使用提示时）？
Histogram based indexes
基于直方图的索引
3) Is there anything that I should be careful about when deciding to add this hint to my queries?
3) 在决定将此提示添加到我的查询时，有什么需要注意的吗？
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.
只要返回的记录数量很少，这个提示就应该是故障安全的——与推动优化器使用特定索引不同，这种方法允许 Oracle 在索引更改时选择不同的计划。
