Oracle执行计划成本与速度 [英] Oracle execution plan cost vs speed

问题描述

在Oracle中构建和调整查询时，速度通常是开发人员最关心的问题.但是，在调整特定查询时，我最近尝试了FIRST_ROWS和NO_CPU_COSTING提示，并且生成的执行计划的执行时间比以前的计划快80％，但成本却高出300％.执行计划中的I/O很少，而且所有额外的开销似乎都来自两个视图之间的嵌套循环外部联接.

When building and tuning a query in Oracle, speed is generally the main concern for the developer. However, in tuning a particular query, I recently tried the FIRST_ROWS and NO_CPU_COSTING hints and an execution plan was generated that is 80% faster than the previous plan in execution time, but at a 300% higher cost. There is very little I/O in the execution plan, and it appears that all the additional cost comes from a nested loop outer join between two views.

此查询是分页的，因此我只需要前几百行.缺乏有效的I/O，使我认为此查询将不依赖于缓存，乍一看，这似乎是可行的方法.但是，由于我从未见过查询同时提高速度和的费用，因此，我不确定使用此查询的弊端是什么.有吗?

This query is paginated, so I will only ever need the first few hundred rows. The lack of significant I/O leads me to think that this query will not be cache-dependent, and at first glance it seems like the way to go. However, since I've never seen a query increase in speed and cost so much at the same time, I'm not sure what the drawbacks to using this query might be. Are there any?

推荐答案

对于带等联接的查询，这是非常典型的查询；当需要完整数据集时，对等联接进行了优化以使用哈希联接；而仅当前几行是必需的，或者在按完整日期集排序的顺序上使用排序，这样索引可以更有效地用于子集.

This is pretty typical of a query with an equijoin that is optimised to use a hash join when the full data set is required, and a nested loop when only the first few rows are needed, or where a sort is used for an order by on the full date set where an index can be more efficiently used for a subset.

当然，如果优化器不知道您将只使用行的子集，那么它不会给出您将实际执行的查询的成本，因为它包括所有嵌套循环操作的成本永远不会执行.

Of course if the optimiser is not aware that you are only going to use a subset of the rows then it is not giving the cost for the query that you will actually execute, as it includes the cost for all the nested loop operations that are never going to execute.

但是，估算成本没有什么不对的地方，仅此而已.如果您希望自己理解一个更有意义的数字，请使用rownum限制.

However, there is nothing incorrect about the estimated cost, it just is what it is. If you want a more meaningful figure for your own understanding then use a rownum limit.

顺便说一句，不赞成使用FIRST_ROWS，而推荐使用first_rows(1)，first_rows(10)，first_rows(100)或first_rows(1000).

By the way, FIRST_ROWS is deprecated in favour of first_rows(1), first_rows(10), first_rows(100) or first_rows(1000).

这篇关于Oracle执行计划成本与速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！