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 [...]
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 [...]
This had the exact opposite effect. The statement execution time is now over 600 Seconds (!!).
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.
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 |
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
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)?
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.
In reality, you have entered lots of new records in the last 24 hours. A whole days worth of new records...
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.