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实例上以两个不同的模式运行。 insert语句看起来并不重要 - 我在这里寻找故障排除策略。
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％的相同结构。一些列的名称略有不同，除了它们是相同的。 insert语句几乎完全相同。一个解释计划的成本为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.
另一个insert语句在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万！），以及每次操作花费的时间（与其他操作相比）。
9 个解决方案
#2
2
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.
我见过系统，它们在批量插入之前删除索引并在最后重建 - 而且速度更快。
#3
1
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 not comparable. 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究竟如何执行该查询。它有很多技术性的gobbeldy-gook，但你真正关心的是知道它是从最缩进的部分开始工作，并且在每一步它根据少数规则之一进行合并。这将告诉您在两个实例中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 advisor to 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调优类，或者至少是非常好的。虽然它并不便宜。）
#4
1
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的。
#5
0
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是一个很好的起点。我还强烈推荐“优化Oracle性能”一书，该书讨论了跟踪执行和分析输出的类似工具。
#6
0
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.
我相信您应该能够通过首先了解所提出的问题以及阅读每个查询的解释计划来确定问题。很多时候我发现最大的区别是有些表和索引尚未分析。
#7
0
Another good reference that presents a general technique for query tuning is the book SQL Tuning by Dan Tow.
提供查询调优的一般技术的另一个很好的参考是Dan Tow的书籍SQL Tuning。
#8
0
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.
从这个分析中，我想出了一个我认为应该更适合的执行计划。这不是一个确切的执行计划，只是对我发现的一些重要更改，例如：它应该使用Index X或Hash Join而不是嵌套循环。
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使用该执行计划。通常通过使用Hints或创建附加索引，有时会更改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语句的执行计划。我会避免这种方法，因为它很容易被遗忘，并且在一年左右的时间里，一些可怜的开发人员会抓住她的头脑，为什么声明以一年前可能适用于数据的方式执行，而不是当前数据......
#9
-1
analyzing the oI also highly recommend the book Optimizing Oracle Performance, which discusses similar tools for tracing execution and utput.
分析oI还强烈推荐优化Oracle性能一书，该书讨论了跟踪执行和输出的类似工具。
#1
#2
2
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.
我见过系统，它们在批量插入之前删除索引并在最后重建 - 而且速度更快。
#3
1
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 not comparable. 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究竟如何执行该查询。它有很多技术性的gobbeldy-gook，但你真正关心的是知道它是从最缩进的部分开始工作，并且在每一步它根据少数规则之一进行合并。这将告诉您在两个实例中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 advisor to 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调优类，或者至少是非常好的。虽然它并不便宜。）
#4
1
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的。
#5
0
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是一个很好的起点。我还强烈推荐“优化Oracle性能”一书，该书讨论了跟踪执行和分析输出的类似工具。
#6
0
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.
我相信您应该能够通过首先了解所提出的问题以及阅读每个查询的解释计划来确定问题。很多时候我发现最大的区别是有些表和索引尚未分析。
#7
0
Another good reference that presents a general technique for query tuning is the book SQL Tuning by Dan Tow.
提供查询调优的一般技术的另一个很好的参考是Dan Tow的书籍SQL Tuning。
#8
0
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.
从这个分析中，我想出了一个我认为应该更适合的执行计划。这不是一个确切的执行计划，只是对我发现的一些重要更改，例如：它应该使用Index X或Hash Join而不是嵌套循环。
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使用该执行计划。通常通过使用Hints或创建附加索引，有时会更改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语句的执行计划。我会避免这种方法，因为它很容易被遗忘，并且在一年左右的时间里，一些可怜的开发人员会抓住她的头脑，为什么声明以一年前可能适用于数据的方式执行，而不是当前数据......
#9
-1
analyzing the oI also highly recommend the book Optimizing Oracle Performance, which discusses similar tools for tracing execution and utput.
分析oI还强烈推荐优化Oracle性能一书，该书讨论了跟踪执行和输出的类似工具。