SQL 您如何解释查询的解释计划？

How do you interpret a query's explain plan?

提问by

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."

在尝试了解 SQL 语句的执行方式时，有时建议查看解释计划。在解释（理解）解释计划时应该经历什么过程？什么应该突出，“哦，这工作得很好？” 与“哦，不，那不对。”

采纳答案by David Aldridge

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.

每当我看到全表扫描不好而索引访问很好的评论时，我都会不寒而栗。全表扫描、索引范围扫描、快速全索引扫描、嵌套循环、合并连接、散列连接等只是分析人员必须理解的访问机制，并结合数据库结构的知识和查询的目的以得出任何有意义的结论。

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.

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

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

因此，对于如何阅读解释计划的机制，Oracle 文档是一个很好的指南：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.

也请仔细阅读性能调优指南。

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.

也有一个关于“基数反馈”的谷歌，一种解释计划可用于比较查询中各个阶段的基数估计与执行期间经历的实际基数的技术。我相信，沃尔夫冈·百年灵 (Wolfgang Breitling) 是该方法的作者。

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

所以，最重要的是：了解访问机制。了解数据库。理解查询的意图。避免经验法则。

回答by Tony Andrews

This subject is too big to answer in a question like this. You should take some time to read Oracle's Performance Tuning Guide

这个话题太大了，无法在这样的问题中回答。您应该花一些时间阅读Oracle 的性能调优指南

回答by Mark Nold

The two examples below show a FULL scan and a FAST scan using an INDEX.

下面的两个示例显示了使用 INDEX 的 FULL 扫描和 FAST 扫描。

It's best to concentrate on your Cost and Cardinality. Looking at the examples the use of the index reduces the Cost of running the query.

最好专注于您的成本和基数。查看示例，索引的使用降低了运行查询的成本。

It's a bit more complicated (and i don't have a 100% handle on it) but basically the Cost is a function of CPU and IO cost, and the Cardinality is the number of rows Oracle expects to parse. Reducing both of these is a good thing.

它有点复杂（我没有 100% 处理它）但基本上成本是 CPU 和 IO 成本的函数，基数是 Oracle 期望解析的行数。减少这两个是一件好事。

Don't forget that the Cost of a query can be influenced by your query and the Oracle optimiser model (eg: COST, CHOOSE etc) and how often you run your statistics.

不要忘记，查询的成本会受到您的查询和 Oracle 优化器模型（例如：COST、CHOOSE 等）以及您运行统计信息的频率的影响。

Example 1:

示例 1：

SCAN http://docs.google.com/a/shanghainetwork.org/File?id=dd8xj6nh_7fj3cr8dx_b

扫描 http://docs.google.com/a/shanghainetwork.org/File?id=dd8xj6nh_7fj3cr8dx_b

Example 2 using Indexes:

使用索引的示例 2：

INDEX http://docs.google.com/a/fukuoka-now.com/File?id=dd8xj6nh_9fhsqvxcp_b

索引 http://docs.google.com/a/fukuoka-now.com/File?id=dd8xj6nh_9fhsqvxcp_b

And as already suggested, watch out for TABLE SCAN. You can generally avoid these.

正如已经建议的那样，注意 TABLE SCAN。您通常可以避免这些。

回答by decibel

Looking for things like sequential scans can be somewhat useful, but the reality is in the numbers... except when the numbers are just estimates! What is usually farmore useful than looking at a query planis looking at the actual execution. In Postgres, this is the difference between EXPLAIN and EXPLAIN ANALYZE. EXPLAIN ANALYZE actually executes the query, and gets real timing information for every node. That lets you see what's actuallyhappening, instead of what the planner thinkswill happen. Many times you'll find that a sequential scan isn't an issue at all, instead it's something else in the query.

寻找诸如顺序扫描之类的东西可能会有些用处，但实际情况是数字……除非数字只是估计值！什么是通常远远高于在寻找一个查询更多有用的计划是看实际的执行。在 Postgres 中，这是 EXPLAIN 和 EXPLAIN ANALYZE 之间的区别。EXPLAIN ANALYZE 实际上执行查询，并获取每个节点的真实计时信息。这让您可以看到实际发生的事情，而不是计划者认为会发生的事情。很多时候，您会发现顺序扫描根本不是问题，而是查询中的其他问题。

The other key is identifying what the actual expensive step is. Many graphical tools will use different sized arrows to indicate how much different parts of the plan cost. In that case, just look for steps that have thin arrows coming in and a thick arrow leaving. If you're not using a GUI you'll need to eyeball the numbers and look for where they suddenly get much larger. With a little practice it becomes fairly easy to pick out the problem areas.

另一个关键是确定实际昂贵的步骤是什么。许多图形工具将使用不同大小的箭头来指示计划的不同部分的成本。在这种情况下，只需寻找有细箭头进入和粗箭头离开的步骤。如果您不使用 GUI，则需要观察数字并寻找它们突然变大的地方。稍加练习，就可以很容易地找出问题区域。

回答by EvilTeach

Really for issues like these, the best thing to do is ASKTOM. In particular his answer to that question contains links to the online Oracle doc, where a lot of the those sorts of rules are explained.

真的对于这些问题，最好的办法是ASKTOM。特别是他对该问题的回答包含指向在线 Oracle 文档的链接，其中解释了许多此类规则。

One thing to keep in mind, is that explain plans are really best guesses.

要记住的一件事是，解释计划确实是最好的猜测。

It would be a good idea to learn to use sqlplus, and experiment with the AUTOTRACE command. With some hard numbers, you can generally make better decisions.

学习使用 sqlplus 并试验 AUTOTRACE 命令将是一个好主意。有了一些硬性数字，您通常可以做出更好的决定。

But you should ASKTOM. He knows all about it :)

但是你应该问一下。他知道这一切:)

回答by Tom Leys

The output of the explain tells you how long each step has taken. The first thing is to find the steps that have taken a long time and understand what they mean. Things like a sequential scan tell you that you need better indexes - it is mostly a matter of research into your particular database and experience.

解释的输出告诉您每个步骤花费了多长时间。第一件事是找到花费了很长时间的步骤并理解它们的含义。诸如顺序扫描之类的事情告诉您需要更好的索引 - 这主要是对您的特定数据库和经验的研究。

回答by convex hull

One "Oh no, that's not right" is often in the form of a table scan. Table scans don't utilize any special indexes and can contribute to purging of every useful in memory caches. In postgreSQL, for example, you will find it looks like this.

一个“哦，不，那不对”通常是表扫描的形式。表扫描不使用任何特殊索引，并且有助于清除内存缓存中的所有有用信息。例如，在 postgreSQL 中，您会发现它看起来像这样。

Seq Scan on my_table  (cost=0.00..15558.92 rows=620092 width=78)

Sometimes table scans are ideal over, say, using an index to query the rows. However, this is one of those red-flag patterns that you seem to be looking for.

有时表扫描比使用索引查询行更理想。但是，这是您似乎正在寻找的危险信号之一。

回答by Jonathan Rupp

Basically, you take a look at each operation and see if the operations "make sense" given your knowledge of how it should be able to work.

基本上，您会查看每个操作，并根据您对它应该如何工作的了解，看看这些操作是否“有意义”。

For example, if you're joining two tables, A and B on their respective columns C and D (A.C=B.D), and your plan shows a clustered index scan (SQL Server term -- not sure of the oracle term) on table A, then a nested loop join to a series of clustered index seeks on table B, you might think there was a problem. In that scenario, you might expect the engine to do a pair of index scans (over the indexes on the joined columns) followed by a merge join. Further investigation might reveal bad statistics making the optimizer choose that join pattern, or an index that doesn't actually exist.

例如，如果您在各自的列 C 和 D (AC=BD) 上连接两个表 A 和 B，并且您的计划显示对表进行聚集索引扫描（SQL Server 术语——不确定 oracle 术语） A，然后嵌套循环连接到表 B 上的一系列聚集索引查找，您可能认为有问题。在这种情况下，您可能希望引擎执行一对索引扫描（对连接列上的索引），然后是合并连接。进一步调查可能会发现错误的统计信息，使优化器选择该连接模式，或实际上不存在的索引。

回答by dpollock

I mainly look for index or table scans. This usually tells me I'm missing an index on an important column that's in the where statement or join statement.

我主要寻找索引或表扫描。这通常告诉我我在 where 语句或 join 语句中的重要列上缺少索引。

From http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx:

从http://www.sql-server-performance.com/tips/query_execution_plan_analysis_p1.aspx：

If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.

* Index or table scans: May indicate a need for better or  additional indexes.
* Bookmark Lookups: Consider changing the current clustered index,
  consider using a covering index, limit
  the number of columns in the SELECT
  statement.
* Filter: Remove any functions in the WHERE clause, don't include wiews
  in your Transact-SQL code, may need
  additional indexes.
* Sort: Does the data really need to be sorted? Can an index be used to
  avoid sorting? Can sorting be done at
  the client more efficiently? 

It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be.

如果您在执行计划中看到以下任何内容，您应该考虑它们的警告信号并调查它们是否存在潜在的性能问题。从性能的角度来看，它们中的每一个都不理想。

* Index or table scans: May indicate a need for better or  additional indexes.
* Bookmark Lookups: Consider changing the current clustered index,
  consider using a covering index, limit
  the number of columns in the SELECT
  statement.
* Filter: Remove any functions in the WHERE clause, don't include wiews
  in your Transact-SQL code, may need
  additional indexes.
* Sort: Does the data really need to be sorted? Can an index be used to
  avoid sorting? Can sorting be done at
  the client more efficiently? 

并非总是可以避免这些，但是您可以避免的越多，查询性能就会越快。

回答by Steven A. Lowe

look at the percentage of time spent in each subsection of the plan, and consider what the engine is doing. for example, if it is scanning a table, consider putting an index on the field(s) that is is scanning for

查看计划的每个子部分所花费的时间百分比，并考虑引擎正在做什么。例如，如果它正在扫描一个表，考虑在正在扫描的字段上放置一个索引