了解Oracle SQL Developer中的执行解释计划的结果 [英] Understanding the results of Execute Explain Plan in Oracle SQL Developer

问题描述

我正在尝试优化查询，但对说明计划返回的某些信息不太了解.谁能告诉我OPTIONS和COST列的重要性?在选项"列中，我仅看到已满"一词.在COST列中，我可以推断出更低的成本意味着更快的查询.但是成本值究竟代表什么，可接受的阈值是多少?

I'm trying to optimize a query but don't quite understand some of the information returned from Explain Plan. Can anyone tell me the significance of the OPTIONS and COST columns? In the OPTIONS column, I only see the word FULL. In the COST column, I can deduce that a lower cost means a faster query. But what exactly does the cost value represent and what is an acceptable threshold?

推荐答案

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

The output of EXPLAIN PLAN is a debug output from Oracle's query optimiser. The COST is the final output of the Cost-based optimiser (CBO), the purpose of which is to select which of the many different possible plans should be used to run the query. The CBO calculates a relative Cost for each plan, then picks the plan with the lowest cost.

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

(Note: in some cases the CBO does not have enough time to evaluate every possible plan; in these cases it just picks the plan with the lowest cost found so far)

通常，导致慢速查询的最大原因之一是为查询提供服务的读取行数(更精确地说，是块)，因此成本将部分基于 优化器估算值需要读取的行数.

In general, one of the biggest contributors to a slow query is the number of rows read to service the query (blocks, to be more precise), so the cost will be based in part on the number of rows the optimiser estimates will need to be read.

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

For example, lets say you have the following query:

SELECT emp_id FROM employees WHERE months_of_service = 6;

(months_of_service列具有NOT NULL约束，并且具有普通索引.)

(The months_of_service column has a NOT NULL constraint on it and an ordinary index on it.)

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

There are two basic plans the optimiser might choose here:

• 计划1:从员工"表中读取所有行，对于每个行，检查谓词是否为真(months_of_service=6).
• 方案2:读取months_of_service=6的索引(这将导致一组ROWID)，然后根据返回的ROWID访问表.

• Plan 1: Read all the rows from the "employees" table, for each, check if the predicate is true (months_of_service=6).
• Plan 2: Read the index where months_of_service=6 (this results in a set of ROWIDs), then access the table based on the ROWIDs returned.

让我们想象一下员工"表有1,000,000(100万)行.让我们进一步想象一下，由于某种原因，months_of_service的值范围从1到12，并且分布相当均匀.

Let's imagine the "employees" table has 1,000,000 (1 million) rows. Let's further imagine that the values for months_of_service range from 1 to 12 and are fairly evenly distributed for some reason.

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

The cost of Plan 1, which involves a FULL SCAN, will be the cost of reading all the rows in the employees table, which is approximately equal to 1,000,000; but since Oracle will often be able to read the blocks using multi-block reads, the actual cost will be lower (depending on how your database is set up) - e.g. let's imagine the multi-block read count is 10 - the calculated cost of the full scan will be 1,000,000 / 10; Overal cost = 100,000.

计划2 的成本将涉及扫描索引的成本，以及通过ROWID访问表的成本，该计划涉及INDEX RANGE SCAN和按ROWID查找表.我将不讨论索引范围扫描的成本，但让我们想象一下索引范围扫描的成本是每行1个.我们希望在12个案例中找到1个匹配，因此索引扫描的费用为1,000,000/12 = 83,333；加上访问表的成本(假设每次访问读取1个块，在这里我们不能使用多块读取)= 83,333；总费用= 166,666.

The cost of Plan 2, which involves an INDEX RANGE SCAN and a table lookup by ROWID, will be the cost of scanning the index, plus the cost of accessing the table by ROWID. I won't go into how index range scans are costed but let's imagine the cost of the index range scan is 1 per row; we expect to find a match in 1 out of 12 cases, so the cost of the index scan is 1,000,000 / 12 = 83,333; plus the cost of accessing the table (assume 1 block read per access, we can't use multi-block reads here) = 83,333; Overall cost = 166,666.

如您所见，计划1(完全扫描)的成本比计划2(索引扫描+通过rowid进行访问)的成本低-这意味着CBO将选择完全扫描.

As you can see, the cost of Plan 1 (full scan) is LESS than the cost of Plan 2 (index scan + access by rowid) - which means the CBO would choose the FULL scan.

如果优化器在这里所​​做的假设是正确的，那么实际上计划1将比计划2更可取，效率也更高-这证明了全扫描始终是不良"的说法.

If the assumptions made here by the optimiser are true, then in fact Plan 1 will be preferable and much more efficient than Plan 2 - which disproves the myth that FULL scans are "always bad".

如果优化程序的目标是FIRST_ROWS(n)而不是ALL_ROWS，则结果将大不相同-在这种情况下，优化程序将青睐Plan 2，因为它通常会更快地返回前几行，但代价是效率较低整个查询.

The results would be quite different if the optimiser goal was FIRST_ROWS(n) instead of ALL_ROWS - in which case the optimiser would favour Plan 2 because it will often return the first few rows quicker, at the cost of being less efficient for the entire query.

这篇关于了解Oracle SQL Developer中的执行解释计划的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！