Oracle 查询优化器的诊断输出
在很多情况下，我们对 Oracle 基于成本的优化器做出的有关查询执行计划的决策不满意。 然后，我们使用提示、不太直接的查询转换、索引重组和实例参数来尝试引导它做我们认为更有意义的事情。 这是在黑暗中进行的尝试，开发、登台和生产服务器之间的结果可能有很大差异（无论我们如何努力同步统计数据等）。
有没有办法从 Oracle 服务器获取诊断输出，以说明 CBO 已经考虑了哪些替代方案，以及为什么它们被丢弃？
我正在考虑像 EXPLAIN PLAN 这样的功能，只不过它枚举了所有可能的（或至少许多）执行计划及其相关成本。
There are many instances where we are not happy with the decisions that Oracle's cost-based-optimizer makes regarding the query execution plan. Using hints, less-than-straightforward query transformations, index reorganization and instance parameters we then try to coax it into doing what we think makes more sense. It is very much taking stabs in the dark, and the results can vary widely between the development, staging and production servers (no matter how hard we try to synchronize statistics and such).
Is there a way to get diagnostic output from the Oracle server to illustrate what alternatives the CBO has looked at, and why they have been discarded?
I am thinking of a feature like EXPLAIN PLAN, except that it enumerates all possible (or at least many) execution plans and their associated costs.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以通过收集 10053 个跟踪事件转储更好地了解 Oracle 优化器如何做出决策。
我发现优化器很少做出“错误”的决定（当然，不包括跨数据库链接的分布式查询），但通常会被不能准确代表数据的性质和分布的统计信息所误导。 检查 http://optimizermagic.blogspot.com 和 http://www.jlcomp.demon.co.uk 查看优化器内部结构。
您还可以使用 Oracle SQL 分析器实用程序（额外费用）让优化器考虑替代路径，如果找到更好的路径，它至少会生成旧的和新的 EXPLAIN PLANS 供您比较。 有时，您可以根据这些信息对次优计划的原因进行逆向工程（通常情况下，您会发现这是一个统计问题）
You can better understand how the Oracle optimizer is making its decisions by collecting 10053 trace event dumps.
I have found that the optimizer rarely makes "wrong" decisions (well, excluding distributed queries across db links), but is usually being misled by statistics that don't accurately represent the nature and distribution of your data. Check resources like http://optimizermagic.blogspot.com and http://www.jlcomp.demon.co.uk for a look into optimizer internals.
You can also use the Oracle SQL Analyzer utility (extra cost) to have the optimizer consider alternative paths and if a better one is found it will at least produce the old and new EXPLAIN PLANS for your comparison. Sometimes you can reverse-engineer the cause for the sub-optimal plans from this information (and more often or not you'll find it is a statistics problem)