oracle - 了解 Oracle SQL Developer 中执行解释计划的结果

Question

我正在尝试优化查询，但不太理解解释计划返回的一些信息。谁能告诉我 OPTIONS 和 COST 列的重要性？在“选项”列中，我只看到“完整”一词。在 COST 列中，我可以推断出较低的成本意味着更快的查询。但成本值到底代表什么以及可接受的阈值是多少？

Answer 1

EXPLAIN PLAN 的输出是 Oracle 查询优化器的调试输出。 COST 是基于成本的优化器 (CBO) 的最终输出，其目的是选择应使用许多不同的可能计划中的哪一个来运行查询。 CBO 计算每个计划的相对成本，然后选择成本最低的计划。

(注意:在某些情况下，CBO 没有足够的时间来评估每个可能的计划；在这些情况下，它只会选择迄今为止发现的成本最低的计划)

一般来说，导致查询缓慢的最大因素之一是为查询服务而读取的行数(更准确地说是 block )，因此成本将部分基于优化器估计需要读取的行数。

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

SELECT emp_id FROM employees WHERE months_of_service = 6;

(months_of_service 列有一个 NOT NULL 约束和一个普通索引。)

优化器可以在此处选择两个基本计划:

• 方案 1:读取“employees”表中的所有行，检查每行的谓词是否为 true (months_of_service=6)。
• 方案 2:读取 months_of_service=6 的索引(这会生成一组 ROWID)，然后根据返回的 ROWID 访问表。

假设“employees”表有 1,000,000(100 万)行。让我们进一步想象，months_of_service 的值范围从 1 到 12，并且由于某种原因分布相当均匀。

计划 1 涉及全扫描，其成本将是读取员工表中所有行的成本，大约等于 1,000,000；但由于 Oracle 通常能够使用多 block 读取来读取 block ，因此实际成本会更低(取决于数据库的设置方式) - 例如假设多 block 读取计数为 10 - 计算出的全扫描成本将为 1,000,000/10；总成本 = 100,000。

计划 2 涉及索引范围扫描和按 ROWID 进行表查找，其成本将是扫描索引的成本加上按 ROWID 访问表的成本。我不会讨论索引范围扫描的成本是如何计算的，但让我们假设索引范围扫描的成本是每行 1；我们期望在 12 个案例中找到 1 个匹配项，因此索引扫描的成本为 1,000,000/12 = 83,333；加上访问表的成本(假设每次访问读取 1 个 block ，我们这里不能使用多 block 读取)= 83,333；总成本 = 166,666。

如您所见，计划 1(完整扫描)的成本低于计划 2(索引扫描 + rowid 访问)的成本 - 这意味着 CBO 将选择完整扫描。

如果优化器在此所做的假设是正确的，那么实际上计划 1 会比计划 2 更可取且更高效 - 这反驳了完整扫描“总是不好”的神话。

如果优化器的目标是 FIRST_ROWS(n) 而不是 ALL_ROWS，结果会大不相同 - 在这种情况下，优化器会倾向于计划 2，因为它通常会更快地返回前几行，但代价是效率较低整个查询。