如何解决Oracle SQL语句的性能问题 [英] How do I troubleshoot performance problems with an Oracle SQL statement

问题描述

我有两个insert语句，几乎完全相同，它们在同一个Oracle实例上的两个不同模式中运行。插入语句看起来像什么都没关系 - 我在这里寻找故障排除策略。

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.

这两个模式都有99％的结构相同。几个列具有稍微不同的名称，除了它们是相同的。 insert语句几乎完全相同。一个解释计划的成本为6，解释计划的成本为7.两组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.

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

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

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

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

正在插入的记录数正确。这是执行时间的巨大差距，让我困惑。考虑到解释计划中没有什么突出的，你将如何确定什么导致运行时的这种差异？

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?

（我在Windows上使用Oracle 10.2.0.4 ）。

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

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

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:-

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)

请注意操作为MERGE JOIN CARTESIAN和BUFFER SORT的行。让我来看看这些的事情是生成的行数（超过200万！）和每个操作花费的时间（与其他操作相比）。

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).

推荐答案

使用 SQL跟踪工具和TKPROF 。

这篇关于如何解决Oracle SQL语句的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！