Sorry, I have to go back to the base line.
The reason is:
- surely we will remove the FIRST_ROW hint from the query, it seems, we are not able to put the "n" value to the "FIRST_ROW(n)" hint :-(
- apart from the BENUTZER_ID, there are additional filters as well, e.g. MANDANT_ID
So the query looks like this way:
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT
t0.*, t1.*
FROM PROTOKOLL t0, PROTOKOLL t1
WHERE ((((t0.BENUTZER_ID = 'Buenn') AND (t0.MANDANT_ID = '01002110')) AND (t0.TYP = 'E')) AND ((t1.UUID = t0.ANDERES_PROTOKOLL_UUID) AND (t1.TYP = 'A')))
ORDER BY t0.ZEITPUNKT DESC) a WHERE ROWNUM <= 5000) WHERE rnum > 0;
We might have the following combinations:
With BENUTZER_ID, with MANDANT_ID
Without BENUTZER_ID, with MANDANT_ID
Of course, we have different values of the filters....
WHERE (t0.BENUTZER_ID = 'Buenn') AND (t0.MANDANT_ID = '01002110')-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:08:04.44 | 176K| 134K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:08:04.44 | 176K| 134K| | | |
|* 2 | VIEW | | 1 | 705 | 5000 |00:08:04.45 | 176K| 134K| | | |
|* 3 | COUNT STOPKEY | | 1 | | 5000 |00:08:04.44 | 176K| 134K| | | |
| 4 | VIEW | | 1 | 705 | 5000 |00:08:04.44 | 176K| 134K| | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 705 | 5000 |00:08:04.44 | 176K| 134K| 372K| 372K| 330K (0)|
| 6 | NESTED LOOPS | | 1 | | 22998 |00:05:42.78 | 176K| 134K| | | |
| 7 | NESTED LOOPS | | 1 | 705 | 22998 |00:04:38.01 | 153K| 121K| | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 1 | 705 | 22998 |00:02:24.42 | 107K| 106K| | | |
| 9 | BITMAP CONVERSION TO ROWIDS | | 1 | | 22998 |00:02:20.11 | 84637 | 84405 | | | |
| 10 | BITMAP AND | | 1 | | 3 |00:04:06.30 | 84637 | 84405 | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 4 |00:00:05.04 | 772 | 768 | | | |
|* 12 | INDEX RANGE SCAN | PROTOKOLL_BENUTZER_ID_IX1 | 1 | 89529 | 45996 |00:00:01.61 | 772 | 768 | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 18 |00:01:16.65 | 7072 | 7059 | | | |
|* 14 | INDEX RANGE SCAN | PROTOKOLL_MANDANT_ID_IX1 | 1 | 89529 | 228K|00:00:14.97 | 7072 | 7059 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 212 |00:00:56.81 | 76793 | 76578 | | | |
|* 16 | INDEX RANGE SCAN | PROTOKOLL_TYP_IX1 | 1 | 89529 | 3571K|00:05:01.24 | 76793 | 76578 | | | |
|* 17 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 22998 | 1 | 22998 |00:02:04.76 | 46016 | 15627 | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 22998 | 1 | 22998 |00:01:10.18 | 22998 | 12679 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
5 - filter(ROWNUM<=5000)
8 - filter("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL)
12 - access("P"."BENUTZER_ID"='Buenn')
14 - access("P"."MANDANT"='01002110')
16 - access("P"."TYP"='E')
17 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
18 - filter("P"."TYP"='A')
WHERE (t0.MANDANT_ID = '01002110')--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:07:01.77 | 211K| 110K|
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:07:01.77 | 211K| 110K|
|* 2 | VIEW | | 1 | 5000 | 5000 |00:03:50.29 | 211K| 110K|
|* 3 | COUNT STOPKEY | | 1 | | 5000 |00:03:50.28 | 211K| 110K|
| 4 | VIEW | | 1 | 5000 | 5000 |00:03:50.28 | 211K| 110K|
| 5 | NESTED LOOPS | | 1 | | 5000 |00:03:50.27 | 211K| 110K|
| 6 | NESTED LOOPS | | 1 | 5000 | 5000 |00:03:42.04 | 206K| 108K|
|* 7 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 1 | 89529 | 5000 |00:03:16.51 | 196K| 106K|
| 8 | INDEX FULL SCAN DESCENDING| PROTOKOLL_ZEITPUNKT_IX1 | 1 | 635K| 302K|00:00:17.83 | 2261 | 2156 |
|* 9 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 5000 | 1 | 5000 |00:00:20.63 | 10006 | 2655 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 5000 | 1 | 5000 |00:00:06.47 | 5000 | 1691 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
7 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."MANDANT"='01002110' AND "P"."TYP"='E'))
9 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
10 - filter("P"."TYP"='A')
Comments:
- you see the long A-Time values in both cases
- I see, that without the 5000 limit, the query with the first condition would return 23K rows, it's OK
- but with the second condition should return even more, because only the MANDAT_ID filter is applied, but I can see only 302K in the A-Rows and after that the 5000 limit only
- and a comoletly different exec. plan, even without the usage any index which contains MANDANT_ID field
Let me change the filter parameters now!
WHERE (t0.BENUTZER_ID = 'wiese') AND (t0.MANDANT_ID = '01055110')-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.84 | 10590 | 152 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.84 | 10590 | 152 | | | |
|* 2 | VIEW | | 1 | 1 | 2205 |00:00:00.85 | 10590 | 152 | | | |
|* 3 | COUNT STOPKEY | | 1 | | 2205 |00:00:00.84 | 10590 | 152 | | | |
| 4 | VIEW | | 1 | 1 | 2205 |00:00:00.84 | 10590 | 152 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 1 | 2205 |00:00:00.84 | 10590 | 152 | 142K| 142K| 126K (0)|
| 6 | NESTED LOOPS | | 1 | | 2205 |00:00:00.13 | 10590 | 152 | | | |
| 7 | NESTED LOOPS | | 1 | 1 | 2205 |00:00:00.12 | 8385 | 151 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 1 | 1 | 2205 |00:00:00.09 | 3973 | 94 | | | |
| 9 | BITMAP CONVERSION TO ROWIDS | | 1 | | 4410 |00:00:00.06 | 323 | 7 | | | |
| 10 | BITMAP AND | | 1 | | 1 |00:00:00.06 | 323 | 7 | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.04 | 180 | 5 | | | |
|* 12 | INDEX RANGE SCAN | PROTOKOLL_BENUTZER_ID_IX1 | 1 | 3555 | 4422 |00:00:00.01 | 180 | 5 | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | 1 | | 1 |00:00:00.02 | 143 | 2 | | | |
|* 14 | INDEX RANGE SCAN | PROTOKOLL_MANDANT_ID_IX1 | 1 | 3555 | 4720 |00:00:00.01 | 143 | 2 | | | |
|* 15 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 2205 | 1 | 2205 |00:00:00.48 | 4412 | 57 | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 2205 | 1 | 2205 |00:00:00.01 | 2205 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
5 - filter(ROWNUM<=5000)
8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
12 - access("P"."BENUTZER_ID"='wiese')
14 - access("P"."MANDANT"='01055110')
15 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
16 - filter("P"."TYP"='A')
WHERE (t0.MANDANT_ID = '01055110')-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.30 | 11146 | 348 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.30 | 11146 | 348 | | | |
|* 2 | VIEW | | 1 | 1376 | 2360 |00:00:01.30 | 11146 | 348 | | | |
|* 3 | COUNT STOPKEY | | 1 | | 2360 |00:00:01.30 | 11146 | 348 | | | |
| 4 | VIEW | | 1 | 1376 | 2360 |00:00:01.30 | 11146 | 348 | | | |
|* 5 | SORT ORDER BY STOPKEY | | 1 | 1376 | 2360 |00:00:01.29 | 11146 | 348 | 160K| 160K| 142K (0)|
| 6 | NESTED LOOPS | | 1 | | 2360 |00:00:00.04 | 11146 | 348 | | | |
| 7 | NESTED LOOPS | | 1 | 1376 | 2360 |00:00:00.03 | 8786 | 346 | | | |
|* 8 | TABLE ACCESS BY INDEX ROWID| PROTOKOLL | 1 | 1377 | 2360 |00:00:00.02 | 4064 | 256 | | | |
|* 9 | INDEX RANGE SCAN | PROTOKOLL_MANDANT_ID_IX1 | 1 | 5507 | 4720 |00:00:00.01 | 143 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | PROTOKOLL_PK | 2360 | 1 | 2360 |00:00:00.66 | 4722 | 90 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | PROTOKOLL | 2360 | 1 | 2360 |00:00:00.01 | 2360 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RNUM">0)
3 - filter(ROWNUM<=5000)
5 - filter(ROWNUM<=5000)
8 - filter(("P"."ANDERES_PROTOKOLL_UUID" IS NOT NULL AND "P"."TYP"='E'))
9 - access("P"."MANDANT"='01055110')
10 - access("P"."UUID"="P"."ANDERES_PROTOKOLL_UUID")
11 - filter("P"."TYP"='A')
Comments:
- A-Time values are fine :-)
- I see, that the queries return 2200-2300 rows only
- the first exec plan are pretty same
- the second are not, in the second case I see the exec. plan which I would have expect: usage of PROTOKOLL_MANDANT_ID_IX1
But the main problem is the significant difference on the A-Time values depending on the values of the applied filter....
Q: What can you suggest to speed up the queries with the first filters?