in einem Trace-File eines PL finde ich ein SQL das sehr langsam ist. Fast die gesamte Laufzeit wird für das Fetchen eines SQL verbraucht. Dieses SQL liefert immer nur einen Satz zurück. Der SQL-Plan sieht gut aus. Bei meinen Test läuft es Tagelang in der erwarteten Zeit. Nach ein paar durchläufen, ohne das sich ein Schema zeigt, sehe ich einen Einbruch der sich von 500.000 in 12h auf 2.000 Sätze in 10h äußert.
Wer weis rat ?
den Plan in einen Editor kopieren, so ist er auch brauchbar formatiert.
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 125 | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | YT_TRANSACTION | 1 | 37 | 2 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 125 | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 88 | 0 (0)| | KEY | KEY |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| YT_CONTRACT | 1 | 88 | 0 (0)| | KEY | KEY |
|* 7 | INDEX RANGE SCAN | YIU_CONTRACT_01 | 1 | | 0 (0)| | KEY | KEY |
| 8 | PARTITION RANGE SINGLE | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION LIST SINGLE | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | YI_TRANSACTIO_03 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 17 0.00 0.00 0 0 0 0
Fetch 17 57.94 433.91 216904 4508723 0 17
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34 57.94 433.91 216904 4508723 0 17
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 112 (recursive depth: 1)
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 509 0.28 22.26 3 44 126 0
Execute 2663 2.57 91.28 22 173 98 1
Fetch 4004 58.80 458.18 218992 4523159 0 18627
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7176 61.65 571.74 219017 4523376 224 18628
Wer weis rat ?
den Plan in einen Editor kopieren, so ist er auch brauchbar formatiert.
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 125 | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID | YT_TRANSACTION | 1 | 37 | 2 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 125 | 2 (0)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 88 | 0 (0)| | KEY | KEY |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID| YT_CONTRACT | 1 | 88 | 0 (0)| | KEY | KEY |
|* 7 | INDEX RANGE SCAN | YIU_CONTRACT_01 | 1 | | 0 (0)| | KEY | KEY |
| 8 | PARTITION RANGE SINGLE | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
| 9 | PARTITION LIST SINGLE | | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
|* 10 | INDEX RANGE SCAN | YI_TRANSACTIO_03 | 1 | | 2 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 17 0.00 0.00 0 0 0 0
Fetch 17 57.94 433.91 216904 4508723 0 17
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34 57.94 433.91 216904 4508723 0 17
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 112 (recursive depth: 1)
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 509 0.28 22.26 3 44 126 0
Execute 2663 2.57 91.28 22 173 98 1
Fetch 4004 58.80 458.18 218992 4523159 0 18627
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7176 61.65 571.74 219017 4523376 224 18628
Comment