Oracle 的解释计划有多准确?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/829805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:10:50  来源:igfitidea点击:

How accurate is Oracle's EXPLAIN PLAN?

sqloracleoracle10gsql-execution-plan

提问by Jason Baker

Are there any good ways to objectively measure a query's performance in Oracle 10g? There's one particular query that I've been tuningfor a few days. I've gotten a version that seems to be running faster (at least based on my initial tests), but the EXPLAIN cost is roughly the same.

在 Oracle 10g 中,有什么好的方法可以客观地衡量查询的性能吗?有一个特定的查询我已经调优了几天。我得到了一个似乎运行得更快的版本(至少基于我的初始测试),但 EXPLAIN 成本大致相同。

  1. How likely is it that the EXPLAIN cost is missing something?
  2. Are there any particular situations where the EXPLAIN cost is disproportionately different from the query's actual performance?
  3. I used the first_rows hint on this query. Does this have an impact?
  1. EXPLAIN 成本丢失某些东西的可能性有多大?
  2. 是否有任何特定情况下 EXPLAIN 成本与查询的实际性能不成比例地不同?
  3. 我在这个查询中使用了 first_rows 提示。这有影响吗?

回答by Quassnoi

How likely is it that the EXPLAIN cost is missing something?

EXPLAIN 成本丢失某些东西的可能性有多大?

Very unlikely. In fact, it would be a level 1bug :)

非常不可能。事实上,这将是一个关卡1错误:)

Actually, if your statistics have changed significantly from the time you ran the EXPLAIN, the actual query plan will differ. But as soom as the query is compliled, the plan will remain the same.

实际上,如果您的统计数据从您运行 时起发生了显着变化EXPLAIN,则实际的查询计划将有所不同。但只要查询被编译,计划将保持不变。

Note EXPLAIN PLANmay show you things that are likelyto happen but may never happen in an actual query.

NoteEXPLAIN PLAN可能会向您展示在实际查询中可能发生但可能永远不会发生的事情。

Like, if you run an EXPLAIN PLANon a hierarchical query:

例如,如果您EXPLAIN PLAN在分层查询上运行:

SELECT  *
FROM    table
START WITH
        id = :startid
CONNECT BY
        parent = PRIOR id

with indexes on both idand parent, you will see an extra FULL TABLE SCANwhich most probably will not happen in real life.

与这两个指标idparent,你会看到一个额外的FULL TABLE SCAN大多数可能不会发生在现实生活。

Use STORED OUTLINE's to store and reuse the plan no matter what.

STORED OUTLINE无论如何,使用's 来存储和重用计划。

Are there any particular situations where the EXPLAIN cost is disproportionately different from the query's actual performance?

是否有任何特定情况下 EXPLAIN 成本与查询的实际性能不成比例地不同?

Yes, it happens very very often on complicate queries.

是的,它经常发生在复杂的查询上。

CBO(cost based optimizer) uses calculated statistics to evaluate query time and choose optimal plan.

CBO(基于成本的优化器)使用计算统计来评估查询时间并选择最佳计划。

If you have lots of JOIN's, subqueries and these kinds on things in your query, its algorithm cannot predict exactly which plan will be faster, especially when you hit memory limits.

如果您JOIN的查询中有很多's、子查询和这些类型的内容,则其算法无法准确预测哪个计划会更快,尤其是当您遇到内存限制时。

Here's the particular situation you asked about: HASH JOIN, for instance, will need several passes over the probe tableif the hash table will not fit into pga_aggregate_table, but as of Oracle 10g, I don't remember this ever to be taken into account by CBO.

这是您询问的特定情况:HASH JOIN例如,probe table如果哈希表不适合,则需要多次传递pga_aggregate_table,但截至Oracle 10g,我不记得CBO.

That's why I hint everyquery I expect to run for more than 2seconds in a worst case.

这就是为什么我会在最坏的情况下提示我希望运行超过几秒钟的每个查询2

I used the first_rows hint on this query. Does this have an impact?

我在这个查询中使用了 first_rows 提示。这有影响吗?

This hint will make the optimizer to use a plan which has lower responsetime: it will return first rows as soon as possible, despite the overall query time being larger.

这个提示将使优化器使用响应时间较短的计划:它会尽快返回第一行,尽管整体查询时间更长。

Practically, it almost always means using NESTED LOOP's instead of HASH JOIN's.

实际上,它几乎总是意味着使用NESTED LOOP's 而不是HASH JOIN's。

NESTED LOOP's have poorer overall performance on large datasets, but they return the first rows faster (since no hash table needs to be built).

NESTED LOOP在大型数据集上的整体性能较差,但它们返回第一行的速度更快(因为不需要构建哈希表)。

As for the query from your original question, see my answer here.

至于来自您原始问题的查询,请在此处查看我的回答。

回答by spencer7593

Q:Are there any good ways to objectively measure a query's performance in Oracle 10g?

问:有什么好的方法可以客观地衡量 Oracle 10g 中的查询性能吗?

  • Oracle tracing is the best way to measure performance. Execute the query and let Oracle instrument the execution. In the SQLPlus environment, it's very easy to use AUTOTRACE.
  • Oracle 跟踪是衡量性能的最佳方式。执行查询并让 Oracle 检测执行。在 SQLPlus 环境中,使用 AUTOTRACE 非常容易。

http://asktom.oracle.com/tkyte/article1/autotrace.html(article moved)
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671636641855

http://asktom.oracle.com/tkyte/article1/autotrace.html(文章已移动)
http://tkyte.blogspot.com/2007/04/when-explanation-doesn-sound-quite.html
http:// asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5671636641855

And enabling Oracle trace in other environments isn't that difficult.

在其他环境中启用 Oracle 跟踪并没有那么困难。

Q:There's one particular query that I've been tuning for a few days. I've gotten a version that seems to be running faster (at least based on my initial tests), but the EXPLAIN cost is roughly the same.

问:有一个特定的查询我已经调优了几天。我得到了一个似乎运行得更快的版本(至少基于我的初始测试),但 EXPLAIN 成本大致相同。

  • The actual execution of the statement is what needs to be measured. EXPLAIN PLAN does a decent job of predicting the optimizer plan, but it doesn't actually measurethe performance.
  • 语句的实际执行是需要测量的。EXPLAIN PLAN 在预测优化器计划方面做得不错,但它实际上并没有衡量性能。

Q:> 1 . How likely is it that the EXPLAIN cost is missing something?

问:> 1 。EXPLAIN 成本丢失某些东西的可能性有多大?

  • Not very likely, but I have seen cases where EXPLAIN PLAN comes up with a different plan than the optimizer.
  • 不太可能,但我见过 EXPLAIN PLAN 提出与优化器不同的计划的情况。

Q:> 2 . Are there any particular situations where the EXPLAIN cost is disproportionately different from the query's actual performance?

问:> 2 。是否有任何特定情况下 EXPLAIN 成本与查询的实际性能不成比例地不同?

  • The short answer is that I've not observed any. But then again, there's not really a direct correlation between the EXPLAIN PLAN cost and the actual observed performance. It's possible for EXPLAIN PLAN to give a really high number for cost, but to have the actual query run in less than a second. EXPLAIN PLAN does not measure the actual performance of the query, for that you need Oracle trace.
  • 简短的回答是我没有观察到任何。但话说回来,EXPLAIN PLAN 成本与实际观察到的性能之间并没有真正的直接相关性。EXPLAIN PLAN 可能会给出非常高的成本数字,但实际查询运行时间不到一秒。EXPLAIN PLAN 不测量查询的实际性能,为此您需要 Oracle 跟踪。

Q:> 3 . I used the first_rows hint on this query. Does this have an impact?

问:> 3 。我在这个查询中使用了 first_rows 提示。这有影响吗?

  • Any hint (like /*+ FIRST_ROWS */) may influence which plan is selected by the optimizer.
  • 任何提示(如/*+ FIRST_ROWS */)都可能影响优化器选择的计划。


The "cost" returned by the EXPLAIN PLAN is relative. It's an indication of performance, but not an accurate gauge of it. You can't translate a cost number into a number of disk operations or a number of CPU seconds or number of wait events.

EXPLAIN PLAN 返回的“成本”是相对的。这是性能的一个指标,但不是一个准确的衡量标准。您无法将成本数字转换为磁盘操作数或 CPU 秒数或等待事件数。

Normally, we find that a statement with an EXPLAIN PLAN cost shown as 1 is going to run "very quickly", and a statement with an EXPLAIN PLAN cost on the order of five or six digits is going to take more time to run. But not always.

通常,我们发现 EXPLAIN PLAN 成本显示为 1 的语句将“非常快地”运行,而 EXPLAIN PLAN 成本约为五或六位数的语句将需要更多时间来运行。但不总是。

What the optimizer is doing is comparing a lot of possible execution plans (full table scan, using an index, nested loop join, etc.) The optimizer is assigning a number to each plan, then selecting the plan with the lowest number.

优化器所做的是比较许多可能的执行计划(全表扫描、使用索引、嵌套循环连接等)。优化器为每个计划分配一个编号,然后选择编号最小的计划。

I have seen cases where the optimizer plan shown by EXPLAIN PLAN does NOTmatch the actual plan used when the statement is executed. I saw that a decade ago with Oracle8, particularly when the statement involved bind variables, rather than literals.

我见过 EXPLAIN PLAN 显示的优化器计划与执行语句时使用的实际计划匹配的情况。十年前我在 Oracle8 中看到了这一点,特别是当语句涉及绑定变量而不是文字时。

To get an actual cost for statement execution, turn on tracing for your statement. The easiest way to do this is with SQLPlus AUTOTRACE.

要获得语句执行的实际成本,请为您的语句打开跟踪。最简单的方法是使用 SQLPlus AUTOTRACE。

[http://asktom.oracle.com/tkyte/article1/autotrace.html][4]

Outside the SQLPlus environment, you can turn on Oracle tracing:

在 SQLPlus 环境之外,您可以打开 Oracle 跟踪:

    alter session set timed_statistics = true;
    alter session set tracefile_identifier = here_is_my_session;
    alter session set events '10046 trace name context forever, level 12'
    --alter session set events '10053 trace name context forever, level 1'
    select /*-- your_statement_here --*/ ...
    alter session set events '10046 trace name context off'
    --alter session set events '10053 trace name context off'

This puts a trace file into the user_dump_dest directory on the server. The tracefile produced will have the statement plan AND all of the wait events. (The assigned tracefile identifier is included in the filename, and makes it easier to find your file in the udump directory)

这会将跟踪文件放入服务器上的 user_dump_dest 目录中。生成的跟踪文件将包含语句计划和所有等待事件。(分配的跟踪文件标识符包含在文件名中,可以更轻松地在 udump 目录中找到您的文件)

    select value from v$parameter where name like 'user_dump_dest'

If you don't have access to the tracefile, you're going to need to get help from the dba to get you access. (The dba can create a simple shell script that developers can run against a .trc file to run tkprof, and change the permissions on the trace file and on the tkprof output. You can also use the newer trcanlzr. There are Oracle metalink notes on both.

如果您无权访问跟踪文件,则需要从 dba 获得帮助才能访问。(dba 可以创建一个简单的 shell 脚本,开发人员可以针对 .trc 文件运行该脚本以运行 tkprof,并更改跟踪文件和 tkprof 输出的权限。您还可以使用较新的 trcanlzr。有 Oracle metalink 说明两个都。

回答by haggai_e

AFAIK, EXPLAIN is using some database statistics to calculate the cost, so it can definitely differ from the actual performance.

AFAIK,EXPLAIN 正在使用一些数据库统计数据来计算成本,因此它肯定会与实际性能有所不同。

回答by northpole

In my experience EXPLAIN has been accurate and beneficial. If it wasn't it might not be the useful tool it is. When was the last time you analyzed the tables? I have seen where the Explain plan was nearly the same before and after an analyze, but the analyze made a huge performance gain.

根据我的经验,EXPLAIN 是准确且有益的。如果不是,它可能不是有用的工具。你最后一次分析表格是什么时候?我已经看到在分析之前和之后解释计划几乎相同的地方,但分析取得了巨大的性能提升。