如何解决 Oracle SQL 语句的性能问题

How do I troubleshoot performance problems with an Oracle SQL statement

提问by Mike McAllister

I have two insert statements, almost exactly the same, which run in two different schemas on the same Oracle instance. What the insert statement looks like doesn't matter - I'm looking for a troubleshooting strategy here.

我有两个几乎完全相同的插入语句，它们在同一个 Oracle 实例上的两个不同模式中运行。插入语句的外观无关紧要 - 我在这里寻找故障排除策略。

Both schemas have 99% the same structure. A few columns have slightly different names, other than that they're the same. The insert statements are almost exactly the same. The explain plan on one gives a cost of 6, the explain plan on the other gives a cost of 7. The tables involved in both sets of insert statements have exactly the same indexes. Statistics have been gathered for both schemas.

两种模式都有 99% 的相同结构。一些列的名称略有不同，但它们是相同的。插入语句几乎完全相同。一个的解释计划的成本为 6，另一个的解释计划的成本为 7。两组插入语句中涉及的表具有完全相同的索引。已经为两种模式收集了统计信息。

One insert statement inserts 12,000 records in 5 seconds.

一个 insert 语句在 5 秒内插入 12,000 条记录。

The other insert statement inserts 25,000 records in 4 minutes 19 seconds.

另一个插入语句在 4 分 19 秒内插入 25,000 条记录。

The number of records being insert is correct. It's the vast disparity in execution times that confuses me. Given that nothing stands out in the explain plan, how would you go about determining what's causing this disparity in runtimes?

插入的记录数是正确的。让我困惑的是执行时间的巨大差异。鉴于解释计划中没有任何突出之处，您将如何确定导致运行时差异的原因？

(I am using Oracle 10.2.0.4 on a Windows box).

（我在 Windows 机器上使用 Oracle 10.2.0.4）。

Edit:The problem ended up being an inefficient query plan, involving a cartesian merge which didn't need to be done. Judicious use of index hints and a hash join hint solved the problem. It now takes 10 seconds. Sql Trace / TKProf gave me the direction, as I it showed me how many seconds each step in the plan took, and how many rows were being generated. Thus TKPROF showed me:-

编辑：问题最终是一个低效的查询计划，涉及不需要完成的笛卡尔合并。明智地使用索引提示和散列连接提示解决了这个问题。现在需要 10 秒。Sql Trace / TKProf 为我指明了方向，因为它向我展示了计划中的每一步花费了多少秒，以及生成了多少行。因此 TKPROF 向我展示了：-

Rows     Row Source Operation
-------  ---------------------------------------------------
  23690  NESTED LOOPS OUTER (cr=3310466 pr=17 pw=0 time=174881374 us)
  23690   NESTED LOOPS  (cr=3310464 pr=17 pw=0 time=174478629 us)
2160900    MERGE JOIN CARTESIAN (cr=102 pr=0 pw=0 time=6491451 us)
   1470     TABLE ACCESS BY INDEX ROWID TBL1 (cr=57 pr=0 pw=0 time=23978 us)
   8820      INDEX RANGE SCAN XIF5TBL1 (cr=16 pr=0 pw=0 time=8859 us)(object id 272041)
2160900     BUFFER SORT (cr=45 pr=0 pw=0 time=4334777 us)
   1470      TABLE ACCESS BY INDEX ROWID TBL1 (cr=45 pr=0 pw=0 time=2956 us)
   8820       INDEX RANGE SCAN XIF5TBL1 (cr=10 pr=0 pw=0 time=8830 us)(object id 272041)
  23690    MAT_VIEW ACCESS BY INDEX ROWID TBL2 (cr=3310362 pr=17 pw=0 time=235116546 us)
  96565     INDEX RANGE SCAN XPK_TBL2 (cr=3219374 pr=3 pw=0 time=217869652 us)(object id 272084)
      0   TABLE ACCESS BY INDEX ROWID TBL3 (cr=2 pr=0 pw=0 time=293390 us)
      0    INDEX RANGE SCAN XIF1TBL3 (cr=2 pr=0 pw=0 time=180345 us)(object id 271983)

Notice the rows where the operations are MERGE JOIN CARTESIAN and BUFFER SORT. Things that keyed me into looking at this were the number of rows generated (over 2 million!), and the amount of time spent on each operation (compare to other operations).

请注意操作为 MERGE JOIN CARTESIAN 和 BUFFER SORT 的行。促使我关注这一点的是生成的行数（超过 200 万！），以及每个操作花费的时间（与其他操作相比）。

采纳答案by Eddie Awad

Use the SQL Trace facility and TKPROF.

使用SQL 跟踪工具和 TKPROF。

回答by Lou Franco

The main culprits in insert slow downs are indexes, constraints, and oninsert triggers. Do a test without as many of these as you can remove and see if it's fast. Then introduce them back in and see which one is causing the problem.

插入速度变慢的罪魁祸首是索引、约束和 oninsert 触发器。在没有尽可能多的情况下进行测试，看看它是否很快。然后将他们介绍回来，看看是哪一个导致了问题。

I have seen systems where they drop indexes before bulk inserts and rebuild at the end -- and it's faster.

我见过在批量插入之前删除索引并在最后重建的系统——而且速度更快。

回答by user11318

The first thing to realize is that, as the documentation says, the cost you see displayed is relative to one of the query plans. The costs for 2 different explains are notcomparable. Secondly the costs are based on an internal estimate. As hard as Oracle tries, those estimates are not accurate. Particularly not when the optimizer misbehaves. Your situation suggests that there are two query plans which, according to Oracle, are very close in performance. But which, in fact, perform very differently.

首先要意识到，正如文档所说，您看到的成本与查询计划之一有关。2 种不同解释的成本不可比。其次，成本基于内部估计。尽管 Oracle 努力尝试，但这些估计并不准确。尤其是当优化器行为不端时。您的情况表明，根据 Oracle 的说法，有两个查询计划在性能上非常接近。但实际上，它们的表现非常不同。

The actual information that you want to look at is the actual explain plan itself. That tells you exactly how Oracle executes that query. It has a lot of technical gobbeldy-gook, but what you really care about is knowing that it works from the most indented part out, and at each step it merges according to one of a small number of rules. That will tell you what Oracle is doing differently in your two instances.

您要查看的实际信息是实际的解释计划本身。这会准确地告诉您 Oracle 如何执行该查询。它有很多技术难题，但你真正关心的是知道它从最缩进的部分开始工作，并且在每一步都根据少数规则之一进行合并。这将告诉您 Oracle 在您的两个实例中的不同之处。

What next? Well there are a variety of strategies to tune bad statements. The first option that I would suggest, if you're in Oracle 10g, is to try their SQL tuning advisorto see if a more detailed analysis will tell Oracle the error of its ways. It can then store that plan, and you will use the more efficient plan.

接下来是什么？好吧，有多种策略可以调整坏语句。如果您使用的是 Oracle 10g，我建议的第一个选项是尝试他们的SQL 调优顾问，看看更详细的分析是否会告诉 Oracle 其方式的错误。然后它可以存储该计划，您将使用更有效的计划。

If you can't do that, or if that doesn't work, then you need to get into things like providing query hints, manual stored query outlines, and the like. That is a complex topic. This is where it helps to have a real DBA. If you don't, then you'll want to start reading the documentation, but be aware that there is a lot to learn. (Oracle also has a SQL tuning class that is, or at least used to be, very good. It isn't cheap though.)

如果您不能这样做，或者如果那不起作用，那么您需要进入诸如提供查询提示、手动存储查询大纲等内容。这是一个复杂的话题。这是拥有一个真正的 DBA 很有帮助的地方。如果您不这样做，那么您将需要开始阅读文档，但请注意，有很多东西需要学习。（Oracle 也有一个 SQL 调优类，它非常好，或者至少曾经非常好。不过它并不便宜。）

回答by AJ.

I've put up my general list of things to check to improve performance as an answer to another question:

作为另一个问题的答案，我列出了要检查以提高性能的一般事项清单：

Favourite performance tuning tricks

最喜欢的性能调优技巧

... It might be helpful as a checklist, even though it's not Oracle-specific.

... 作为检查清单可能会有所帮助，即使它不是特定于 Oracle 的。

回答by AJ.

SQL Trace and tkprof are only good if you have access to theses tools. Most of the large companies that I do work for do not allow developers to access anything under the Oracle unix IDs.

SQL Trace 和 tkprof 只有在您可以访问这些工具时才有用。我工作的大多数大公司都不允许开发人员访问 Oracle unix ID 下的任何内容。

I believe you should be able to determine the problem by first understanding the question that is being asked and by reading the explain plans for each of the queries. Many times I find that the big difference is that there are some tables and indexes that have not been analyzed.

我相信您应该能够通过首先理解所提出的问题并阅读每个查询的解释计划来确定问题。很多时候我发现很大的不同是有一些表和索引没有被分析。

回答by thoroughly

Another good reference that presents a general technique for query tuning is the book SQL Tuningby Dan Tow.

另一个介绍查询调优通用技术的很好的参考资料是Dan Tow所著的SQL Tuning一书。

回答by Dave Costa

I agree with a previous poster that SQL Trace and tkprof are a good place to start. I also highly recommend the book Optimizing Oracle Performance, which discusses similar tools for tracing execution and analyzing the output.

我同意以前的海报，SQL Trace 和 tkprof 是一个很好的起点。我还强烈推荐Optimizing Oracle Performance一书，其中讨论了用于跟踪执行和分析输出的类似工具。

回答by Jens Schauder

When the performance of a sql statement isn't as expected / desired, one of the first things I do is to check the execution plan.

当 sql 语句的性能不符合预期/期望时，我做的第一件事就是检查执行计划。

The trick is to check for things that aren't as expected. For example you might find table scans where you think an index scan should be faster or vice versa.

诀窍是检查不符合预期的事情。例如，您可能会发现表扫描在您认为索引扫描应该更快的地方，反之亦然。

A point where the oracle optimizer sometimes takes a wrong turn are the estimates how many rows a step will return. If the execution plan expects 2 rows, but you know it will more like 2000 rows, the execution plan is bound to be less than optimal.

oracle 优化器有时会出错的一点是估计一个步骤将返回多少行。如果执行计划需要 2 行，但您知道它更像是 2000 行，则执行计划肯定不是最佳的。

With two statements to compare you can obviously compare the two execution plans to see where they differ.

使用两个语句进行比较，您显然可以比较两个执行计划以查看它们的不同之处。

From this analysis, I come up with an execution plan that I think should be suited better. This is not an exact execution plan, but just some crucial changes, to the one I found, like: It should use Index X or a Hash Join instead of a nested loop.

从这个分析中，我提出了一个我认为应该更适合的执行计划。这不是一个精确的执行计划，而只是对我发现的那个计划的一些关键更改，例如：它应该使用索引 X 或哈希连接而不是嵌套循环。

Next thing is to figure out a way to make Oracle use that execution plan. Often by using Hints, or creating additonal indexes, sometimes changing the SQL statement. Then of course test that the changed statement

下一步是想办法让 Oracle 使用该执行计划。通常通过使用提示或创建附加索引，有时会更改 SQL 语句。然后当然测试更改的语句

a) still does what it is supposed to do

a) 仍然做它应该做的

b) is actually faster

b) 实际上更快

With b it is very important to make sure you are testing the correct use case. A typical pit fall is the difference between returning the first row, versus returning the last row. Most tools show you the first results as soon as they are available, with no direct indication, that there is more work to be done. But if your actual program has to process all rows before it continues to the next processing step, it is almost irrelevant when the first row appears, it is only relevant when the last row is available.

使用 b 确保您正在测试正确的用例非常重要。典型的跌倒是返回第一行与返回最后一行之间的差异。大多数工具在它们可用时立即向您显示第一个结果，没有直接迹象表明还有更多工作要做。但是如果您的实际程序在继续下一个处理步骤之前必须处理所有行，那么第一行出现时几乎无关紧要，只有当最后一行可用时才相关。

If you find a better execution plan, the final step is to make you database actually use it in the actual program. If you added an index, this will often work out of the box. Hints are an option, but can be problematic if a library creates your sql statement, those ofte don't support hints. As a last resort you can save and fix execution plans for specific sql statements. I'd avoid this approach, because its easy to become forgotten and in a year or so some poor developer will scratch her head why the statement performs in a way that might have been apropriate with the data one year ago, but not with the current data ...

如果你找到更好的执行计划，最后一步就是让你的数据库在实际程序中实际使用它。如果您添加了索引，这通常是开箱即用的。提示是一种选择，但如果库创建了您的 sql 语句，则可能会出现问题，这些语句通常不支持提示。作为最后的手段，您可以保存和修复特定 sql 语句的执行计划。我会避免这种方法，因为它很容易被遗忘，并且在一年左右的时间里，一些可怜的开发人员会挠头，为什么该语句的执行方式可能适合一年前的数据，但不适用于当前的数据数据 ...

回答by Jens Schauder

analyzing the oI also highly recommend the book Optimizing Oracle Performance, which discusses similar tools for tracing execution and utput.

分析 oI 还强烈推荐 Optimizing Oracle Performance 一书，该书讨论了用于跟踪执行和输出的类似工具。