在Oracle SQL Developer中理解执行Explain计划的结果

Question

我试图优化一个查询，但不太理解Explain Plan返回的一些信息。有人能告诉我OPTIONS和COST列的意义吗？在OPTIONS列中，我只看到一个单词FULL。在COST列中，我可以推断出较低的成本意味着更快的查询。但是成本值具体代表什么以及可接受的阈值是多少呢？

- Kevin Babcock

Answer 1

EXPLAIN PLAN的输出是来自Oracle查询优化器的调试输出。 COST是基于代价的优化器（CBO）的最终输出，其目的是选择运行查询时应使用哪些可能的计划中的一种。 CBO为每个计划计算相对成本，然后选择成本最低的计划。

（注意：在某些情况下，CBO没有足够的时间来评估每个可能的计划；在这些情况下，它只选择到目前为止找到的成本最低的计划）

一般来说，慢查询的最大贡献者之一是读取用于服务查询的行数（更确切地说是块数），因此成本将部分基于优化器估计需要读取的行数。

例如，假设您有以下查询：

SELECT emp_id FROM employees WHERE months_of_service = 6;

（months_of_service列有一个NOT NULL约束和一个普通索引。）

这里有两个基本的执行计划：

1. 扫描"employees"表中的所有行，对于每一行，检查谓词是否为真（months_of_service=6）。 2. 读取索引，其中months_of_service=6（这将得到一组ROWIDs），然后根据返回的ROWIDs访问表。

假设"employees"表有1,000,000（100万）行。进一步假设months_of_service的值从1到12，并因某种原因分布相当均匀。

"Plan 1"的代价是进行全表扫描的代价，大约等于1,000,000；但由于Oracle通常可以使用多块读取来读取块，实际成本将会更低（取决于数据库的设置）。例如，假设多块读取计数为10，则全表扫描的计算成本将为1,000,000 / 10 = 100,000。总成本= 100,000。

"Plan 2"的代价是索引范围扫描和按ROWID访问表的代价。我不会详细介绍索引范围扫描的代价，但假设索引范围扫描的代价是每行1个；我们希望在12个案例中有1个匹配项，因此索引扫描的代价为1,000,000 / 12 = 83,333；加上访问表的代价（假设每次访问需要读取1个块，这里不能使用多块读取）= 83,333；总成本=166,666。

如您所见，"Plan 1"（全表扫描）的代价比"Plan 2"（索引扫描+按ROWID访问）低-这意味着CBO将选择全表扫描。

如果优化器的假设是正确的，那么实际上"Plan 1"将比"Plan 2"更可取且更高效-这证明了全表扫描并不总是“不好”的神话。

如果优化器目标是FIRST_ROWS(n)而不是ALL_ROWS，结果会非常不同-在这种情况下，优化器会倾向于选择"Plan 2"，因为它通常可以更快地返回前几行，但对于整个查询来说效率较低。

- Jeffrey Kemp

Answer 2

CBO会构建一个决策树，估计每个查询可用的可能执行路径的成本。成本由实例上设置的CPU_cost或I/O_cost参数设置。CBO会尽力基于表和索引的现有统计信息估算成本，以最佳方式完成查询。你不应该仅根据成本来调整查询。成本可以帮助你理解优化器为什么这样做。如果没有成本，你可能无法弄清楚优化器选择了哪个计划。较低的成本并不意味着更快的查询。有些情况下是正确的，但也有些情况下是错误的。成本基于表格统计信息，如果它们是错误的，则成本将是错误的。

在调整查询时，你应该查看每个步骤的基数和行数。它们是否合理？优化器所假设的基数是否正确？返回的行数是否合理？如果存在错误的信息，则很可能优化器没有获得所需的正确信息以做出正确的决策。这可能是由于表和索引以及CPU统计信息过时或缺失造成的。在调整查询时更新统计信息可以获得最佳优化效果。了解架构对调整非常有帮助。知道优化器何时做出了非常糟糕的决定，并通过小提示指向正确的路径，可以节省大量时间。

- MichaelN

Answer 3

以下是关于在Oracle中使用EXPLAIN PLAN的参考文献: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm），此处提供了有关列的特定信息：http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i18300 如果查询使用“FULL”，则表示正在执行全表扫描以查找数据。在某些情况下，这是可以接受的，否则就表明索引/查询编写不佳。

通常，对于解释计划，您需要确保查询正在利用键，从而Oracle可以使用最少的行访问来查找您要查找的数据。最终，您有时只能在表结构方面取得一定进展。如果成本仍然过高，则可能需要考虑调整模式布局以实现更好的性能。

- drowe

Answer 4

在最近的Oracle版本中，COST代表优化器预计查询所需的时间量，以单个块读取所需的时间单位表示。

因此，如果单个块读取需要2毫秒，并且成本表示为“250”，则可以预期查询需要500毫秒才能完成。

优化器根据估计的单个块和多个块读取次数以及计划的CPU消耗来计算成本。后者在通过在其他操作之前执行某些操作以尝试避免高CPU成本操作方面非常有用。

这引出了一个问题，即优化器如何知道操作需要多长时间。最近的Oracle版本允许收集“系统统计信息”，这绝对不能与表或索引上的统计信息混淆。系统统计是硬件性能的测量结果，最重要的是：

1. 单个块读取需要多长时间 2. 多块读取需要多长时间 3. 多块读取的大小（通常由于表段小于最大值等原因而不同）。 4. CPU性能

这些数字可以根据系统的操作环境而有很大差异，并且可以为“日间OLTP”操作和“夜间批处理报告”操作以及“月末报告”存储不同的统计数据。

给定这些统计数据，可以在不同的操作环境中评估给定的查询执行计划的成本，这可能在某些时候促进使用全表扫描，而在其他时候则促进使用索引扫描。

成本并不完美，但是优化器随着每个版本的发布而变得更加擅长自我监控，并且可以反馈实际成本以比较估计成本，以便为未来做出更好的决策。这也使其更难以预测。

请注意，成本不一定是挂钟时间，因为并行查询操作跨多个线程消耗总时间。

在早期版本的Oracle中，CPU操作的成本被忽略，单个块和多块读取的相对成本根据init参数有效地固定。

- David Aldridge

Answer 5

FULL 可能是指全表扫描，这意味着没有使用索引。除非查询需要使用表中的所有行，否则通常表示出现了问题。

Cost 是一个数字，表示不同负载（处理器、内存、磁盘、IO）的总和，高数值通常是不好的。当移动到计划的根时，这些数字会相加，应该检查每个分支以定位瓶颈。

您还可以查询 v$sql 和 v$session 以获取有关 SQL 语句的统计信息，这将提供各种资源、时间和执行的详细指标。

- stili