如何解释查询的解释计划？ [英] How do you interpret a query's explain plan?

问题描述

尝试理解SQL语句如何执行时，有时建议查看解释计划。在解释计划的解释（有意义）过程中应该经历什么？什么应该突出，哦，这是工作辉煌？

When attempting to understand how a SQL statement is executing, it is sometimes recommended to look at the explain plan. What is the process one should go through in interpreting (making sense) of an explain plan? What should stand out as, "Oh, this is working splendidly?" versus "Oh no, that's not right."

推荐答案

我发现每当我看到完整的tablecans是坏的和索引访问好。全表扫描，索引范围扫描，快速全索引扫描，嵌套循环，合并连接，散列连接等是简单的访问机制，必须由分析人员理解，并结合数据库结构的知识和查询的目的

I shudder whenever I see comments that full tablescans are bad and index access is good. Full table scans, index range scans, fast full index scans, nested loops, merge join, hash joins etc. are simply access mechanisms that must be understood by the analyst and combined with a knowledge of the database structure and the purpose of a query in order to reach any meaningful conclusion.

全扫描只是读取数据段（表或表（或表）的大部分块的最有效的方式子）分区），并且尽管它通常可以指示性能问题，但是仅在它是否是用于实现查询的目标的有效机制的上下文中。作为一个数据仓库和BI人，我的性能的第一个警告标志是基于索引的访问方法和嵌套循环。

A full scan is simply the most efficient way of reading a large proportion of the blocks of a data segment (a table or a table (sub)partition), and, while it often can indicate a performance problem, that is only in the context of whether it is an efficient mechanism for achieving the goals of the query. Speaking as a data warehouse and BI guy, my number one warning flag for performance is an index based access method and a nested loop.

因此，对于如何的机制阅读解释计划Oracle文档是一个很好的指南： http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

So, for the mechanism of how to read an explain plan the Oracle documentation is a good guide: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/ex_plan.htm#PFGRF009

有好的通过性能调整指南也可以阅读。

Have a good read through the Performance Tuning Guide also.

还有一个google的基数反馈，一种解释计划可以用来比较基数的估计具有在执行期间经历的实际基数的查询中的各个阶段。 Wolfgang Breitling是该方法的作者，我相信。

Also have a google for "cardinality feedback", a technique in which an explain plan can be used to compare the estimations of cardinality at various stages in a query with the actual cardinalities experienced during the execution. Wolfgang Breitling is the author of the method, I believe.

所以，底线：了解访问机制。了解数据库。了解查询的意图。避免经验法则。

So, bottom line: understand the access mechanisms. Understand the database. Understand the intention of the query. Avoid rules of thumb.

这篇关于如何解释查询的解释计划？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！