Oracle SQL:其他限制导致性能问题 [英] Oracle SQL: additional restriction causes performance issues
问题描述
我在使用oracle SQL语句时遇到一个奇怪的性能问题.该语句或多或少是一个巨无霸的subselect/inner join语句，因此，我只能在此处发布其结构.看起来像这样:
I have a strange performance problem with a oracle SQL statement. The statement is a more or less giantic subselect / inner join statement, therefore I'll only be able to post the structure of it here. It looks like this:
SELECT "A".COL1, [...] FROM "A"
INNER JOIN ( .. massive amount of subselects and joins ... )
WHERE [...]
该语句的执行速度非常快(约30秒).为了进一步提高速度，我决定按时间限制选择:
The statement is pretty fast for what it is doing (~30 Seconds). To further increase the speed I decided to restrict the selection by time:
SELECT "A".COL1, [...] FROM "A"
INNER JOIN ( .. massive amount of subselects and joins ... )
WHERE "A".TIMESTAMP > ... AND [...]
这具有完全相反的效果.语句执行时间现在已超过600秒(!!).
This had the exact opposite effect. The statement execution time is now over 600 Seconds (!!).
现在说明计划的设置完全不同(正如我所说，仅由于一个单一的MORE限制-该限制具有完整的索引).在此之前，具有连接，索引限制和快速的完整扫描的正常"组合.之后，它被成千上万的嵌套圈完全弄乱了.
The Explain Plan is now set up completly different (as I said, just because of one single MORE restriction - the restriction has a complete index). Before it was a "normal" combination of has joins, index restrictions and fast full scans. Afterwards it is completly messed up with thousands of NESTED LOOPS.
我知道这很难从外部说出来，但是有什么一般性提示可以导致这些嵌套循环在开始时产生什么? 解释计划开始(!!)之前:常规"哈希的组合会加入限制，依此类推.深度始终< 10
I know this is hard to tell from the outside, but is there any general tip what can cause these nested loops at the beginning? EXPLAIN Plan Beginning (!!) Before: "Normal" Combination of Hash joins restrictions and so on. Depth always < 10
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 461 | 286K| 1672 (5)| 00:00:11 |
| 1 | SORT GROUP BY | | 461 | 286K| 1672 (5)| 00:00:11 |
|* 2 | HASH JOIN | | 461 | 286K| 1671 (5)| 00:00:11 |
| 3 | VIEW | index$_join$_016 | 2822 | 93126 | 21 (5)| 00:00:01 |
|* 4 | HASH JOIN | | | | | |
|* 5 | INDEX RANGE SCAN | HRP1000~0 | 2822 | 93126 | 5 (0)| 00:00:01 |
|* 6 | INDEX FAST FULL SCAN | HRP1000~1 | 2822 | 93126 | 19 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 459 | 270K| 1649 (5)| 00:00:11 |
|* 8 | HASH JOIN | | 459 | 259K| 1609 (5)| 00:00:10 |
|* 9 | TABLE ACCESS FULL | BBP_PDORG | 14463 | 607K| 39 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 1939 | 1013K| 1569 (5)| 00:00:10 |
|* 11 | HASH JOIN RIGHT OUTER | | 691 | 335K| 1548 (5)| 00:00:10 |
| 12 | VIEW | | 1572 | 47160 | 148 (5)| 00:00:01 |
| 13 | HASH GROUP BY | | 1572 | 411K| 147 (5)| 00:00:01 |
之后-大量的嵌套循环.深度> 20
After - Massive amount of Nested Loops. Depth > 20
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1392 | 329 (6)| 00:00:03
| 1 | SORT GROUP BY | | 1 | 1392 | 328 (5)| 00:00:03
| 2 | NESTED LOOPS | | 1 | 1392 | 327 (5)| 00:00:03
| 3 | NESTED LOOPS | | 1 | 1371 | 327 (5)| 00:00:03
| 4 | NESTED LOOPS | | 1 | 1333 | 327 (5)| 00:00:03
| 5 | NESTED LOOPS | | 1 | 1312 | 327 (5)| 00:00:03
| 6 | NESTED LOOPS | | 1 | 1274 | 326 (5)| 00:00:03
| 7 | NESTED LOOPS | | 1 | 1235 | 326 (5)| 00:00:03
| 8 | NESTED LOOPS | | 1 | 1196 | 326 (5)| 00:00:03
| 9 | NESTED LOOPS | | 1 | 1175 | 326 (5)| 00:00:03
| 10 | NESTED LOOPS | | 1 | 1137 | 325 (5)| 00:00:03
| 11 | NESTED LOOPS | | 1 | 1116 | 325 (5)| 00:00:03
| 12 | NESTED LOOPS | | 1 | 1078 | 325 (5)| 00:00:03
| 13 | NESTED LOOPS | | 1 | 1061 | 325 (5)| 00:00:03
| 14 | NESTED LOOPS | | 1 | 1010 | 324 (5)| 00:00:03
| 15 | NESTED LOOPS | | 1 | 988 | 324 (5)| 00:00:03
|* 16 | HASH JOIN | | 1 | 953 | 324 (5)| 00:00:03
| 17 | NESTED LOOPS | | | | |
| 18 | NESTED LOOPS | | 1 | 898 | 284 (6)| 00:00:02
| 19 | NESTED LOOPS | | 1 | 853 | 284 (6)| 00:00:02
|* 20 | HASH JOIN | | 1 | 823 | 284 (6)| 00:00:02
| 21 | NESTED LOOPS | | 1 | 780 | 236 (6)| 00:00:02
| 22 | NESTED LOOPS | | 1 | 741 | 236 (6)| 00:00:02
| 23 | NESTED LOOPS | | 1 | 701 | 235 (6)| 00:00:02
| 24 | NESTED LOOPS | | 1 | 639 | 235 (6)| 00:00:02
| 25 | NESTED LOOPS | | 1 | 609 | 235 (6)| 00:00:02
| 26 | NESTED LOOPS | | 1 | 576 | 235 (6)| 00:00:02
| 27 | NESTED LOOPS | | 1 | 533 | 234 (6)| 00:00:02
| 28 | NESTED LOOPS | | 1 | 495 | 234 (6)| 00:00:02
推荐答案
优化器可能认为A.TIMESTAMP > ...会减少命中次数，以至于使用嵌套循环处理少量行比使用嵌套循环便宜执行大型联接.
The optimizer probably thought that A.TIMESTAMP > ... would reduce the number of hits by so much that it would be cheaper to use nested loops for a small number of rows than to perform large joins.
根据提供的稀缺信息，很难确定确切原因以及是否有解决问题的简便方法.
The exact cause and whether there is an easy way to correct the problem is hard to determine based on the scarce information provided.
当您添加索引(或索引列中的条件)时，执行计划会发生巨大变化，您不会感到惊讶.
我为它选择更改>比较的计划感到有些惊讶.限制是否为固定值(即优化器是否知道)，并且接近表中的最大值(如表统计中所记录)?
You should not be surprised that the execution plan changes drastically when you add an index (or a condition on an indexed column).
I'm a bit surprised that it chose to change the plan for a > comparison. Is the limit a fixed value (i.e. is it known to the optimiser) and is it close to the highest value in the table (as recorded in the table statistics)?
有一个关于时间戳的警告，那就是最高值的统计信息很快就会过时. 假设您的统计信息是24小时制，并且您正在寻找最近24小时内的日期.优化程序将使用统计信息并预测查询将导致0次匹配.因此，它将从检查索引开始.
There is a caveat regarding timestamps and that is that the highest value statistic can get outdated pretty fast. Let's say your statistics are 24 h old and that you are looking for dates within the last 24 hours. The optimiser will use the stats and predict that the query will result in 0 hits. So it will start with checking the index.
实际上，您在过去24小时内输入了许多新记录.一整天的新记录价值……
In reality, you have entered lots of new records in the last 24 hours. A whole days worth of new records...
也可以直接设置优化程序的一种方法是提供截止日期作为参数(并在可能的情况下预先编译问题)，以使优化程序不会误以为它将获得0次匹配.
One way too set the optimizer straight is to provide the cut-off date as a parameter (and pre-compile the question if applicable) so that the optimiser isn't fooled into thinking it will get 0 hits.
这篇关于Oracle SQL:其他限制导致性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
