在评估SQL查询性能时，要比较哪些指标？

Question

最近，我观看了一个关于oracle SQL性能优化的在线课程。在视频中，讲师经常在比较两个查询的性能时比较自动跟踪的成本值。

但我也从其他论坛和网站上读到，它指出，成本是特定于该查询的相对值，不应用于评估性能的绝对度量。他们建议把注意力集中在一致的获取、物理阅读等方面。

因此，我的解释是，将完全不同的查询的成本值进行比较是没有意义的，因为成本值是相对的。但是，当比较相同的两个查询时，一个被稍微修改为“更好的性能”的查询，比较成本值是可以的。我的解释准确吗？

什么时候可以将成本值与其他指标进行比较呢？

在评估/比较查询性能时，我们应该考虑哪些其他指标？

Answer 1

通常，我会非常谨慎地比较两个查询之间的cost，除非您有一个非常具体的理由相信这是有意义的。

通常，人们不会查看优化器生成(几乎)最优计划的99.9%的查询。人们会关注那些优化器产生了明显次优计划的查询。优化器将产生一个次优计划，原因有两个--要么它不能将一个查询转换成它可以优化的形式(在这种情况下，一个人可能需要重写查询)，要么它用来进行估计的统计数据是不正确的，所以它认为最优的计划不是。(当然，查询速度缓慢还有其他原因--也许优化器生成了最优计划，但最优计划是进行表扫描，例如缺少索引。)

如果我看到的查询比较慢，而且查询似乎写得相当好，并且有一组合理的索引可用，那么统计数据是最有可能出现问题的原因。但是，由于cost完全基于统计数据，这意味着优化器的cost估计是不正确的。如果它们是不正确的，则cost大致相同地可能是错误的高或不正确的低。如果我查看一个查询计划，我知道这个查询需要聚合数十万行才能生成一个报告，并且我看到优化器已经给它分配了一个个位数的cost，我知道在这条线的某个地方，它估计一个步骤返回的行太少。为了优化这个查询，我需要cost来提高，以便优化器的估计能够准确地反映实际情况。如果我查看一个查询的查询计划，我知道应该只需要扫描几个行，并且在成千上万个行中看到一个cost，我知道优化器估计某些步骤会返回太多行。为了优化该查询，我将需要降低cost，以便优化器的估计能够反映实际情况。

如果使用gather_plan_statistics hint，您将在查询计划中看到估计行数和实际行数。如果优化器的估计接近实际情况，则该计划可能相当好，而cost可能相当准确。如果优化器的估计不正确，那么该计划很可能是糟糕的，而cost很可能是错误的。尝试使用cost度量来调优查询，而不首先确认cost是否合理地接近现实，这很少是非常有效率的。

就我个人而言，我会忽略cost，而只关注那些随着时间的推移可能是稳定的、实际上与性能相关的指标。我的偏见是集中在逻辑读取上，因为大多数系统都是I/O绑定的，但是您也可以使用CPU时间或经过的时间(不过，经过的时间往往不是特别稳定，因为这取决于运行查询时缓存中发生了什么)。如果您正在查看一个计划，请关注估计的和实际的行数，而不是在cost上。

Answer 2

到目前为止，查询的实际运行时间是优化查询的最重要指标。我们可以忽略成本和其他指标99.9%的时间。

如果查询相对较小且速度较快，并且我们可以轻松地重新运行它，并使用GATHER_PLAN_STATISTICS提示查找实际运行时间：

-- Add a hint to the query and re-run it.
select /*+ gather_plan_statistics */ count(*) from all_objects;

-- Find the SQL_ID of your query.
select sql_id, sql_fulltext from gv$sql where lower(sql_text) like '%gather_plan_statistics%';

-- Plus in the SQL_ID to find an execution plan with actual numbers.
select * from table(dbms_xplan.display_cursor(sql_id => 'bbqup7krbyf61', format => 'ALLSTATS LAST'));

如果查询非常慢，而且我们无法轻松地重新运行它，则生成一个SQL报告。这些数据通常在上次执行后几个小时内可用。

-- Generate a SQL Monitor report.
select dbms_sqltune.report_sql_monitor(sql_id => 'bbqup7krbyf61') from dual;

有整本书都是关于解释结果的。最基本的方法是首先检查执行计划，并将重点放在具有最大“A时间”的操作上。如果您想了解查询或优化器在哪里出错，请将“E-行”与“A-行”进行比较，因为估计的基数驱动了大多数优化器的决策。

示例输出：

SQL_ID  bbqup7krbyf61, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from all_objects
 
Plan hash value: 3058112905
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                    |      1 |        |      1 |00:00:03.58 |     121K|    622 |       |       |          |
|   1 |  SORT AGGREGATE                           |                    |      1 |      1 |      1 |00:00:03.58 |     121K|    622 |       |       |          |
|*  2 |   FILTER                                  |                    |      1 |        |  79451 |00:00:02.10 |     121K|    622 |       |       |          |
|*  3 |    HASH JOIN                              |                    |      1 |  85666 |  85668 |00:00:00.12 |    1479 |      2 |  2402K|  2402K| 1639K (0)|
|   4 |     INDEX FULL SCAN                       | I_USER2            |      1 |    148 |    148 |00:00:00.01 |       1 |      0 |       |       |          |
...

Answer 3

就像工程中的大多数东西一样，它真的可以归结为为什么/你在比较和评估什么。

成本是Oracle的一个通用的基于时间的估计，它被用作其内部优化器中的排序标准。This answer explains that selection process pretty well。

通常，作为度量的代价是比较两个不同查询的预期计算时间的好方法，因为它度量以块读取#表示的查询的估计时间开销。因此，如果您要比较同一查询的性能，即对时间进行优化，那么成本是一个很好的使用标准。

然而，如果您的查询或系统是瓶颈或对时间以外的东西(例如内存效率)的约束，那么成本将是一个糟糕的指标来优化。在这些情况下，您应该选择一个与最终目标相关的指标。