Добрый день, не мог бы кто подсказать, на что обратить внимание и как раскопать причину торможения запроса
Есть запрос чей то запрос вида
select ..
from tbl
join tbl1 on tbl1.id= tbl.doc_id
join tbl2 on tbl2.id= tbl.vad_id
left join V_DATA_AID on V_DATA_AID.id_aid= tbl.id_aid and V_DATA_AID.year - 2021
where ..
запрос работает в райноне 30 минут
вьюха V_DATA_AID внутри порядочно большой запрос.
убираю left join V_DATA_AID, запрос работает 2минуты.
Запросы выполнял соответственно на ненагруженной бд для чистоты эксперимента.
План долгого запроса 30мин
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 222 | | 48557 (3)| 00:06:38 | | |
| 1 | NESTED LOOPS OUTER | | 1 | 222 | | 48553 (3)| 00:06:38 | | |
| 2 | NESTED LOOPS OUTER | | 1 | 220 | | 47832 (3)| 00:06:32 | | |
| 3 | NESTED LOOPS | | 1 | 205 | | 47823 (3)| 00:06:32 | | |
|* 4 | HASH JOIN | | 1 | 153 | | 47821 (3)| 00:06:32 | | |
| 5 | VIEW | V_GIP_KM_UN | 2 | 46 | | 33 (10)| 00:00:01 | | |
| 6 | UNION-ALL | | | | | | | | |
|* 7 | FILTER | | | | | | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 119 | | 8 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 116 | | 8 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS OUTER | | 1 | 75 | | 5 (0)| 00:00:01 | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | V_GIP_KM_DATE | 1 | 46 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX RANGE SCAN | IDX$$_143B60001 | 1 | | | 4 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IDX$$_145060008 | 1 | 29 | | 0 (0)| 00:00:01 | | |
|* 14 | MAT_VIEW ACCESS BY INDEX ROWID | MV_REF_CLAS_KM | 1 | 41 | | 3 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IDX_MV_REF_CLAS_KM | 1 | | | 2 (0)| 00:00:01 | | |
|* 16 | INDEX UNIQUE SCAN | PK_D_EXPENSE_TYPES | 1 | 3 | | 0 (0)| 00:00:01 | | |
| 17 | SORT AGGREGATE | | 1 | 172 | | | | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | REF_GIP_KM_PARAMS | 1 | 172 | | 4 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | IDX_REF_GIP_KM_PARAMS | 3 | | | 1 (0)| 00:00:01 | | |
|* 20 | FILTER | | | | | | | | |
| 21 | NESTED LOOPS OUTER | | 1 | 147 | | 17 (18)| 00:00:01 | | |
| 22 | NESTED LOOPS OUTER | | 1 | 144 | | 17 (18)| 00:00:01 | | |
| 23 | NESTED LOOPS OUTER | | 1 | 141 | | 17 (18)| 00:00:01 | | |
| 24 | NESTED LOOPS OUTER | | 1 | 75 | | 5 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS BY GLOBAL INDEX ROWID| V_GIP_KM_DATE | 1 | 46 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 26 | INDEX RANGE SCAN | IDX$$_143B60001 | 1 | | | 4 (0)| 00:00:01 | | |
|* 27 | INDEX RANGE SCAN | IDX$$_145060008 | 1 | 29 | | 0 (0)| 00:00:01 | | |
|* 28 | VIEW PUSHED PREDICATE | | 1 | 66 | | 12 (25)| 00:00:01 | | |
|* 29 | WINDOW SORT PUSHED RANK | | 1 | 109 | | 12 (25)| 00:00:01 | | |
|* 30 | FILTER | | | | | | | | |
|* 31 | HASH JOIN | | 1 | 109 | | 11 (19)| 00:00:01 | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | REF_CLAS_KM | 1 | 54 | | 4 (0)| 00:00:01 | | |
|* 33 | INDEX RANGE SCAN | IDX_REF_CLAS_KM_CKM2 | 1 | | | 3 (0)| 00:00:01 | | |
|* 34 | VIEW | | 371 | 20405 | | 7 (29)| 00:00:01 | | |
|* 35 | WINDOW SORT PUSHED RANK | | 371 | 23373 | | 7 (29)| 00:00:01 | | |
|* 36 | FILTER | | | | | | | | |
|* 37 | FILTER | | | | | | | | |
|* 38 | HASH JOIN RIGHT OUTER | | 371 | 23373 | | 6 (17)| 00:00:01 | | |
|* 39 | TABLE ACCESS FULL | P_USED_PARAMS | 1 | 26 | | 2 (0)| 00:00:01 | | |
|* 40 | TABLE ACCESS FULL | D_CLAS_VERSION | 391 | 14467 | | 3 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | PK_D_EXPENSE_TYPES | 1 | 3 | | 0 (0)| 00:00:01 | | |
|* 42 | INDEX UNIQUE SCAN | PK_GIP_RAZDEL | 1 | 3 | | 0 (0)| 00:00:01 | | |
| 43 | SORT AGGREGATE | | 1 | 172 | | | | | |
|* 44 | TABLE ACCESS BY INDEX ROWID | REF_GIP_KM_PARAMS | 1 | 172 | | 4 (0)| 00:00:01 | | |
|* 45 | INDEX RANGE SCAN | IDX_REF_GIP_KM_PARAMS | 3 | | | 1 (0)| 00:00:01 | | |
|* 46 | VIEW | | 96342 | 11M| | 47787 (3)| 00:06:31 | | |
|* 47 | WINDOW SORT PUSHED RANK | | 96342 | 9220K| 11M| 47787 (3)| 00:06:31 | | |
| 48 | WINDOW SORT | | 96342 | 9220K| 11M| 47787 (3)| 00:06:31 | | |
|* 49 | HASH JOIN | | 96342 | 9220K| 4984K| 44181 (3)| 00:06:02 | | |
|* 50 | TABLE ACCESS BY INDEX ROWID | EST_V_CONTRACT | 78503 | 4063K| | 4391 (1)| 00:00:36 | | |
|* 51 | INDEX RANGE SCAN | EV_CONTRACT_VD_VT_IDX | 14590 | | | 1102 (2)| 00:00:10 | | |
| 52 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 53 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 54 | TABLE ACCESS FULL | EST_V_FCEPLAN | 39M| 1677M| | 10294 (8)| 00:01:25 | | |
|* 55 | TABLE ACCESS BY INDEX ROWID | EST_V_CONTRACT | 1 | 52 | | 2 (0)| 00:00:01 | | |
|* 56 | INDEX UNIQUE SCAN | EV_CONTRACT_PK | 1 | | | 1 (0)| 00:00:01 | | |
| 57 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 58 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 59 | VIEW PUSHED PREDICATE | V_REF_DATA_AID_NK | 1 | 15 | | 9 (0)| 00:00:01 | | |
| 60 | TABLE ACCESS BY INDEX ROWID | REF_DATA_AID_NK | 3 | 114 | | 6 (0)| 00:00:01 | | |
|* 61 | INDEX RANGE SCAN | IDX_REF_DATA_AID_NK3 | 3 | | | 4 (0)| 00:00:01 | | |
| 62 | SORT AGGREGATE | | 1 | 8 | | | | | |
|* 63 | INDEX RANGE SCAN | IDX_DATA_AID_3 | 1 | 8 | | 3 (0)| 00:00:01 | | |
| 64 | VIEW PUSHED PREDICATE | V_DATA_AID | 1 | 2 | | 721 (6)| 00:00:06 | | |
|* 65 | HASH JOIN OUTER | | 1 | 57 | | 465 (8)| 00:00:04 | | |
| 66 | NESTED LOOPS OUTER | | 1 | 47 | | 118 (0)| 00:00:01 | | |
| 67 | NESTED LOOPS OUTER | | 1 | 45 | | 118 (0)| 00:00:01 | | |
|* 68 | TABLE ACCESS BY INDEX ROWID | DATA_AID | 1 | 40 | | 3 (0)| 00:00:01 | | |
|* 69 | INDEX UNIQUE SCAN | PK_DATA_AID | 1 | | | 2 (0)| 00:00:01 | | |
| 70 | SORT AGGREGATE | | 1 | 8 | | | | | |
| 71 | NESTED LOOPS | | 40184 | 313K| | 256 (1)| 00:00:03 | | |
| 72 | NESTED LOOPS | | 1 | | | 4 (0)| 00:00:01 | | |
| 73 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 74 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
|* 75 | INDEX RANGE SCAN | IDX_DATA_AID_3 | 40184 | 313K| | 252 (1)| 00:00:03 | | |
|* 76 | VIEW | | 1 | 5 | | 115 (0)| 00:00:01 | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | DATA_REF_AID_CLAS | 2190 | 39420 | | 52 (0)| 00:00:01 | | |
|* 78 | INDEX RANGE SCAN | IDX_DATA_REF_AID_CLAS | 2190 | | | 17 (0)| 00:00:01 | | |
| 79 | SORT AGGREGATE | | 1 | 8 | | | | | |
| 80 | NESTED LOOPS | | 10979 | 87832 | | 63 (0)| 00:00:01 | | |
| 81 | NESTED LOOPS | | 1 | | | 4 (0)| 00:00:01 | | |
| 82 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 83 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 84 | FIRST ROW | | 10979 | 87832 | | 59 (0)| 00:00:01 | | |
|* 85 | INDEX RANGE SCAN (MIN/MAX) | IDX_DATA_REF_AID_CLASSIF_VERS | 10979 | 87832 | | 59 (0)| 00:00:01 | | |
|* 86 | INDEX RANGE SCAN | IDX_D_OBJ_TYPE_OBJ_TYPE | 1 | 2 | | 0 (0)| 00:00:01 | | |
| 87 | VIEW | | 42 | 420 | | 346 (11)| 00:00:03 | | |
|* 88 | FILTER | | | | | | | | |
| 89 | SORT GROUP BY | | 42 | 4368 | | 346 (11)| 00:00:03 | | |
| 90 | VIEW | VW_DAG_0 | 8266 | 839K| | 346 (11)| 00:00:03 | | |
| 91 | SORT GROUP BY | | 8266 | 258K| | 346 (11)| 00:00:03 | | |
| 92 | PARTITION LIST SINGLE | | 8266 | 258K| | 345 (10)| 00:00:03 | KEY | KEY |
|* 93 | TABLE ACCESS FULL | V_GIP_KM_DATE | 8266 | 258K| | 345 (10)| 00:00:03 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------------------------------
План запроса 2 минуты
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 | | 47827 (3)| 00:06:32 | | |
| 1 | NESTED LOOPS | | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 185 | | 47823 (3)| 00:06:32 | | |
|* 3 | HASH JOIN | | 1 | 153 | | 47821 (3)| 00:06:32 | | |
| 4 | VIEW | V_GIP_KM_UN | 2 | 46 | | 33 (10)| 00:00:01 | | |
| 5 | UNION-ALL | | | | | | | | |
|* 6 | FILTER | | | | | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 119 | | 8 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS OUTER | | 1 | 116 | | 8 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 75 | | 5 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | V_GIP_KM_DATE | 1 | 46 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX RANGE SCAN | IDX$$_143B60001 | 1 | | | 4 (0)| 00:00:01 | | |
|* 12 | INDEX RANGE SCAN | IDX$$_145060008 | 1 | 29 | | 0 (0)| 00:00:01 | | |
|* 13 | MAT_VIEW ACCESS BY INDEX ROWID | MV_REF_CLAS_KM | 1 | 41 | | 3 (0)| 00:00:01 | | |
|* 14 | INDEX RANGE SCAN | IDX_MV_REF_CLAS_KM | 1 | | | 2 (0)| 00:00:01 | | |
|* 15 | INDEX UNIQUE SCAN | PK_D_EXPENSE_TYPES | 1 | 3 | | 0 (0)| 00:00:01 | | |
| 16 | SORT AGGREGATE | | 1 | 172 | | | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | REF_GIP_KM_PARAMS | 1 | 172 | | 4 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX_REF_GIP_KM_PARAMS | 3 | | | 1 (0)| 00:00:01 | | |
|* 19 | FILTER | | | | | | | | |
| 20 | NESTED LOOPS OUTER | | 1 | 147 | | 17 (18)| 00:00:01 | | |
| 21 | NESTED LOOPS OUTER | | 1 | 144 | | 17 (18)| 00:00:01 | | |
| 22 | NESTED LOOPS OUTER | | 1 | 141 | | 17 (18)| 00:00:01 | | |
| 23 | NESTED LOOPS OUTER | | 1 | 75 | | 5 (0)| 00:00:01 | | |
|* 24 | TABLE ACCESS BY GLOBAL INDEX ROWID| V_GIP_KM_DATE | 1 | 46 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 25 | INDEX RANGE SCAN | IDX$$_143B60001 | 1 | | | 4 (0)| 00:00:01 | | |
|* 26 | INDEX RANGE SCAN | IDX$$_145060008 | 1 | 29 | | 0 (0)| 00:00:01 | | |
|* 27 | VIEW PUSHED PREDICATE | | 1 | 66 | | 12 (25)| 00:00:01 | | |
|* 28 | WINDOW SORT PUSHED RANK | | 1 | 109 | | 12 (25)| 00:00:01 | | |
|* 29 | FILTER | | | | | | | | |
|* 30 | HASH JOIN | | 1 | 109 | | 11 (19)| 00:00:01 | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | REF_CLAS_KM | 1 | 54 | | 4 (0)| 00:00:01 | | |
|* 32 | INDEX RANGE SCAN | IDX_REF_CLAS_KM_CKM2 | 1 | | | 3 (0)| 00:00:01 | | |
|* 33 | VIEW | | 371 | 20405 | | 7 (29)| 00:00:01 | | |
|* 34 | WINDOW SORT PUSHED RANK | | 371 | 23373 | | 7 (29)| 00:00:01 | | |
|* 35 | FILTER | | | | | | | | |
|* 36 | FILTER | | | | | | | | |
|* 37 | HASH JOIN RIGHT OUTER | | 371 | 23373 | | 6 (17)| 00:00:01 | | |
|* 38 | TABLE ACCESS FULL | P_USED_PARAMS | 1 | 26 | | 2 (0)| 00:00:01 | | |
|* 39 | TABLE ACCESS FULL | D_CLAS_VERSION | 391 | 14467 | | 3 (0)| 00:00:01 | | |
|* 40 | INDEX UNIQUE SCAN | PK_D_EXPENSE_TYPES | 1 | 3 | | 0 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | PK_GIP_RAZDEL | 1 | 3 | | 0 (0)| 00:00:01 | | |
| 42 | SORT AGGREGATE | | 1 | 172 | | | | | |
|* 43 | TABLE ACCESS BY INDEX ROWID | REF_GIP_KM_PARAMS | 1 | 172 | | 4 (0)| 00:00:01 | | |
|* 44 | INDEX RANGE SCAN | IDX_REF_GIP_KM_PARAMS | 3 | | | 1 (0)| 00:00:01 | | |
|* 45 | VIEW | | 96342 | 11M| | 47787 (3)| 00:06:31 | | |
|* 46 | WINDOW SORT PUSHED RANK | | 96342 | 9220K| 11M| 47787 (3)| 00:06:31 | | |
| 47 | WINDOW SORT | | 96342 | 9220K| 11M| 47787 (3)| 00:06:31 | | |
|* 48 | HASH JOIN | | 96342 | 9220K| 4984K| 44181 (3)| 00:06:02 | | |
|* 49 | TABLE ACCESS BY INDEX ROWID | EST_V_CONTRACT | 78503 | 4063K| | 4391 (1)| 00:00:36 | | |
|* 50 | INDEX RANGE SCAN | EV_CONTRACT_VD_VT_IDX | 14590 | | | 1102 (2)| 00:00:10 | | |
| 51 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 52 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 53 | TABLE ACCESS FULL | EST_V_FCEPLAN | 39M| 1677M| | 10294 (8)| 00:01:25 | | |
|* 54 | INDEX UNIQUE SCAN | EV_CONTRACT_PK | 1 | | | 1 (0)| 00:00:01 | | |
|* 55 | TABLE ACCESS BY INDEX ROWID | EST_V_CONTRACT | 1 | 32 | | 2 (0)| 00:00:01 | | |
| 56 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
| 57 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------------
Не выполняя трассировку, что можно вынести из этих планов? Понятно, что стоимостные показатели какие то сказочные для запроса, который 30 минут выполняется. Сама вьюха возвращает 100тыс записей, что тоже не много и возвращает она их быстро.
