哪一个计划更好?

邮件列表中有人问：如果有两个执行计划,如何判定哪一个更好?COST 能作为判断的依据么?

有人赞同 Thomas Kyte 的观点，根据响应时间来判断．
有人引用了Hotsos Symposium 2004　的 How To Forecast Tuning Results:

As cost is always a result of cardinality we should focus only on the
estimated cardinality. A developer, when writing the text of a SQL
statement, knows the purpose of the statement and also knows what he
wants to get as the result. Also the cardinality of the result set is
known – or at least he/she has an idea about that. Thus comparing the
estimated cardinality with the expected cardinality would be good
practice for timely elimination of performance problems. When those
two cardinalities differ by orders of magnitude performance problems
are almost inevitable. If the CBO were to correctly estimate the
cardinality of final or intermediate result sets, the cost would be very different and some other plan with a lower cost would be selected.
Most likely this plan would be the optimal one.”

Connor McDonald认为：这是个充满哲学意味的争论．如果优化器是完美的，那么最低开销的就应该是最快的查询．但优化器总是不那么完美，只是试图告诉我你哪一个执行计划可能是最优化的而已．Cary Millsap
指出：CBO 在一个查询执行之前，只能用一个模型来评估开销(can only estimate a cost using a model.),而该模型不够完美是问题所在．所以在执行该计划之后CBO才可以知道真正的estimated cost .10g 的优化器做了改进，可能有能力评估响应时间．Jonathan Lewis自信的认为：

the cost of a query IS and always
has been the optimizer’s estimate of the actual
run time of a query

不过争论来争论去，还有应了那句老话，＂实践是检验真理的唯一标准＂，实际的执行一下，哪一个快就哪一个好吧! CBO 远远不够完美，期待完美的那一天…

延伸阅读：

• Consider Cost or Time – trying to compare the COST of two queries
• SQL Tuning Interview with Dan Tow

Google+