Oracle Query с перекрестным соединением - проблема производительности
Я надеюсь, что кто-нибудь может помочь мне увидеть, могу ли я еще немного улучшить этот запрос:
Среда:
- База данных Oracle 12cR2
- Linux Red Hat 7
- ВМ с 8 ЦП и 32 ГБ ОЗУ
- Статистика актуальна, рассчитана с помощью АВТО ОБРАЗЕЦ и метода ДЛЯ ВСЕХ КОЛОНК РАЗМЕР АВТО
- Параметры оптимизатора являются параметрами по умолчанию для версии 12.2.
Исходный запрос принадлежит стороннему программному обеспечению. Он, как и ожидалось, создает HASH JOINS. Исходная статистика запроса и плана:
$ sqlplus /@USERWALLET @original.sql
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 29 13:19:47 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> var processingDate number;
SQL> exec :processingDate := 20210621;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> -- without index
SQL> select distinct 1 as col_0_0_ from ALFATS.FloatingRateRollover floatingra0_
2 cross join ALFATS.FloatingRateDetail floatingra1_
3 cross join ALFATS.FloatingRateInterestCalendar floatingra2_
4 cross join ALFATS.Schedule schedule3_
5 where floatingra0_.agreementNumber=floatingra1_.agreementNumber and
6 floatingra0_.scheduleNumber=floatingra1_.scheduleNumber and
7 floatingra0_.id=floatingra2_.floatingRateRolloverId and
8 floatingra0_.agreementNumber=schedule3_.agreementNumber and
9 floatingra0_.scheduleNumber=schedule3_.scheduleNumber and
10 floatingra0_.terminationNumber=schedule3_.terminationNumber and
11 floatingra0_.adjustmentProcessedIndicator='N' and
12 floatingra2_.processingDate<=:processingDate and
13 floatingra1_.variableRateType='U' and
14 floatingra0_.terminationNumber=0 and
15 schedule3_.scheduleStatus<>'PRE' and
16 not (exists (select 1 from ALFATS.TransactionFailure transactio4_
17 where floatingra0_.agreementNumber=transactio4_.agreementNumber and
18 floatingra0_.scheduleNumber=transactio4_.scheduleNumber and
19 floatingra0_.terminationNumber=transactio4_.terminationNumber))
20 ;
COL_0_0_
----------
1
1 row selected.
Elapsed: 00:00:23.70
Execution Plan
----------------------------------------------------------
Plan hash value: 3077170061
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 15M| | 236K (1)| 00:00:10 |
| 1 | SORT UNIQUE NOSORT | | 106K| 15M| | 236K (1)| 00:00:10 |
|* 2 | HASH JOIN RIGHT ANTI | | 106K| 15M| | 232K (1)| 00:00:10 |
|* 3 | INDEX FAST FULL SCAN | TRANSACTIONFAILURE_NK | 39 | 1170 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 106K| 12M| 10M| 232K (1)| 00:00:10 |
|* 5 | HASH JOIN RIGHT SEMI | | 110K| 9207K| 4608K| 230K (1)| 00:00:10 |
|* 6 | INDEX FAST FULL SCAN| FLOATINGRATEDETAIL_1 | 109K| 3319K| | 724 (1)| 00:00:01 |
|* 7 | HASH JOIN | | 284K| 14M| 6944K| 228K (1)| 00:00:09 |
|* 8 | TABLE ACCESS FULL | FLOATINGRATEINTERESTCALENDAR | 284K| 3608K| | 20338 (1)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | FLOATINGRATEROLLOVER | 29M| 1153M| | 133K (1)| 00:00:06 |
|* 10 | INDEX FAST FULL SCAN | SCHEDULE_L1 | 274K| 9M| | 852 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="TRANSACTIO4_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="TRANSACTIO4_"."SCHEDULENUMBER" AND
"FLOATINGRA0_"."TERMINATIONNUMBER"="TRANSACTIO4_"."TERMINATIONNUMBER")
3 - filter("TRANSACTIO4_"."TERMINATIONNUMBER"=0)
4 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="SCHEDULE3_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="SCHEDULE3_"."SCHEDULENUMBER" AND
"FLOATINGRA0_"."TERMINATIONNUMBER"="SCHEDULE3_"."TERMINATIONNUMBER")
5 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="FLOATINGRA1_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="FLOATINGRA1_"."SCHEDULENUMBER")
6 - filter("FLOATINGRA1_"."VARIABLERATETYPE"=U'U')
7 - access("FLOATINGRA0_"."ID"="FLOATINGRA2_"."FLOATINGRATEROLLOVERID")
8 - filter("FLOATINGRA2_"."PROCESSINGDATE"<=TO_NUMBER(:PROCESSINGDATE))
9 - filter("FLOATINGRA0_"."ADJUSTMENTPROCESSEDINDICATOR"=U'N' AND
"FLOATINGRA0_"."TERMINATIONNUMBER"=0)
10 - filter("SCHEDULE3_"."TERMINATIONNUMBER"=0 AND "SCHEDULE3_"."SCHEDULESTATUS"<>U'PRE')
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
569374 consistent gets
488274 physical reads
484 redo size
542 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Изучив план, я попытался избежать полного сканирования ТАБЛИЦЫ, создав следующие два индекса:
SQL> CREATE INDEX alfats.FloatingRateRollover_PRF1
ON alfats.FloatingRateRollover (adjustmentProcessedIndicator, agreementNumber, scheduleNumber, terminationNumber, id)
INVISIBLE
COMPUTE STATISTICS;
Index created.
Elapsed: 00:01:58.14
SQL> CREATE INDEX alfats.FloatingRateInterestCalendar_PRF2
ON alfats.FloatingRateInterestCalendar (floatingRateRolloverId, processingDate)
INVISIBLE
COMPUTE STATISTICS;
Index created.
Elapsed: 00:00:06.60
После этого запрос улучшается, к нему обращается
$ sqlplus /@USERWALLET @original_with_indexes.sql
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 29 13:24:27 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> var processingDate number;
SQL> exec :processingDate := 20210621;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
Elapsed: 00:00:00.00
SQL> select distinct 1 as col_0_0_ from ALFATS.FloatingRateRollover floatingra0_
2 cross join ALFATS.FloatingRateDetail floatingra1_
3 cross join ALFATS.FloatingRateInterestCalendar floatingra2_
4 cross join ALFATS.Schedule schedule3_
5 where floatingra0_.agreementNumber=floatingra1_.agreementNumber and
6 floatingra0_.scheduleNumber=floatingra1_.scheduleNumber and
7 floatingra0_.id=floatingra2_.floatingRateRolloverId and
8 floatingra0_.agreementNumber=schedule3_.agreementNumber and
9 floatingra0_.scheduleNumber=schedule3_.scheduleNumber and
10 floatingra0_.terminationNumber=schedule3_.terminationNumber and
11 floatingra0_.adjustmentProcessedIndicator='N' and
12 floatingra2_.processingDate<=:processingDate and
13 floatingra1_.variableRateType='U' and
14 floatingra0_.terminationNumber=0 and
15 schedule3_.scheduleStatus<>'PRE' and
16 not (exists (select 1 from ALFATS.TransactionFailure transactio4_
17 where floatingra0_.agreementNumber=transactio4_.agreementNumber and
18 floatingra0_.scheduleNumber=transactio4_.scheduleNumber and
19 floatingra0_.terminationNumber=transactio4_.terminationNumber))
20 ;
COL_0_0_
----------
1
1 row selected.
Elapsed: 00:00:10.95
Execution Plan
----------------------------------------------------------
Plan hash value: 1805021994
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 15M| | 149K (1)| 00:00:06 |
| 1 | SORT UNIQUE NOSORT | | 106K| 15M| | 149K (1)| 00:00:06 |
|* 2 | HASH JOIN RIGHT ANTI | | 106K| 15M| | 145K (1)| 00:00:06 |
|* 3 | INDEX FAST FULL SCAN | TRANSACTIONFAILURE_NK | 39 | 1170 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 106K| 12M| 10M| 145K (1)| 00:00:06 |
|* 5 | HASH JOIN RIGHT SEMI | | 110K| 9207K| 4608K| 143K (1)| 00:00:06 |
|* 6 | INDEX FAST FULL SCAN | FLOATINGRATEDETAIL_1 | 109K| 3319K| | 724 (1)| 00:00:01 |
|* 7 | HASH JOIN | | 284K| 14M| 6944K| 141K (1)| 00:00:06 |
|* 8 | INDEX FAST FULL SCAN| FLOATINGRATEINTERESTCALENDAR_PRF2 | 284K| 3608K| | 5050 (2)| 00:00:01 |
|* 9 | INDEX FAST FULL SCAN| FLOATINGRATEROLLOVER_PRF1 | 29M| 1153M| | 62326 (1)| 00:00:03 |
|* 10 | INDEX FAST FULL SCAN | SCHEDULE_L1 | 274K| 9M| | 852 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="TRANSACTIO4_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="TRANSACTIO4_"."SCHEDULENUMBER" AND
"FLOATINGRA0_"."TERMINATIONNUMBER"="TRANSACTIO4_"."TERMINATIONNUMBER")
3 - filter("TRANSACTIO4_"."TERMINATIONNUMBER"=0)
4 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="SCHEDULE3_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="SCHEDULE3_"."SCHEDULENUMBER" AND
"FLOATINGRA0_"."TERMINATIONNUMBER"="SCHEDULE3_"."TERMINATIONNUMBER")
5 - access("FLOATINGRA0_"."AGREEMENTNUMBER"="FLOATINGRA1_"."AGREEMENTNUMBER" AND
"FLOATINGRA0_"."SCHEDULENUMBER"="FLOATINGRA1_"."SCHEDULENUMBER")
6 - filter("FLOATINGRA1_"."VARIABLERATETYPE"=U'U')
7 - access("FLOATINGRA0_"."ID"="FLOATINGRA2_"."FLOATINGRATEROLLOVERID")
8 - filter("FLOATINGRA2_"."PROCESSINGDATE"<=TO_NUMBER(:PROCESSINGDATE))
9 - filter("FLOATINGRA0_"."ADJUSTMENTPROCESSEDINDICATOR"=U'N' AND "FLOATINGRA0_"."TERMINATIONNUMBER"=0)
10 - filter("SCHEDULE3_"."TERMINATIONNUMBER"=0 AND "SCHEDULE3_"."SCHEDULESTATUS"<>U'PRE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
257732 consistent gets
0 physical reads
6224 redo size
542 bytes sent via SQL*Net to client
607 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Моя последняя попытка была попытаться переписать запрос, преобразовав некоторые из этих
SQL> select distinct 1 as col_0_0_ from
2 ALFATS.FloatingRateRollover t0
3 inner join ALFATS.FloatingRateDetail t1
4 on ( t0.agreementNumber=t1.agreementNumber
5 and
6 t0.scheduleNumber=t1.scheduleNumber
7 and
8 t0.adjustmentProcessedIndicator='N'
9 and
10 t0.terminationNumber=0
11 and
12 t1.variableRateType='U'
13 )
14 inner join ALFATS.Schedule t3
15 on (
16 t0.agreementNumber=t3.agreementNumber
17 and
18 t0.scheduleNumber=t3.scheduleNumber
19 and
20 t0.terminationNumber=t3.terminationNumber
21 and
22 t3.scheduleStatus<>'PRE'
23 )
24 inner join ALFATS.FloatingRateInterestCalendar t2
25 on (
26 t0.id = t2.floatingRateRolloverId
27 and
28 t2.processingDate<=:processingDate
29 ) where
30 not (exists (select 1 from ALFATS.TransactionFailure t4
31 where t0.agreementNumber=t4.agreementNumber and
32 t0.scheduleNumber=t4.scheduleNumber and
33 t0.terminationNumber=t4.terminationNumber)
34 )
35 ;
1 row selected.
Elapsed: 00:00:07.93
Execution Plan
----------------------------------------------------------
Plan hash value: 1805021994
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106K| 15M| | 149K (1)| 00:00:06 |
| 1 | SORT UNIQUE NOSORT | | 106K| 15M| | 149K (1)| 00:00:06 |
|* 2 | HASH JOIN RIGHT ANTI | | 106K| 15M| | 145K (1)| 00:00:06 |
|* 3 | INDEX FAST FULL SCAN | TRANSACTIONFAILURE_NK | 39 | 1170 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN SEMI | | 106K| 12M| 10M| 145K (1)| 00:00:06 |
|* 5 | HASH JOIN RIGHT SEMI | | 110K| 9207K| 4608K| 143K (1)| 00:00:06 |
|* 6 | INDEX FAST FULL SCAN | FLOATINGRATEDETAIL_1 | 109K| 3319K| | 724 (1)| 00:00:01 |
|* 7 | HASH JOIN | | 284K| 14M| 6944K| 141K (1)| 00:00:06 |
|* 8 | INDEX FAST FULL SCAN| FLOATINGRATEINTERESTCALENDAR_PRF2 | 284K| 3608K| | 5050 (2)| 00:00:01 |
|* 9 | INDEX FAST FULL SCAN| FLOATINGRATEROLLOVER_PRF1 | 29M| 1153M| | 62326 (1)| 00:00:03 |
|* 10 | INDEX FAST FULL SCAN | SCHEDULE_L1 | 274K| 9M| | 852 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T0"."AGREEMENTNUMBER"="T4"."AGREEMENTNUMBER" AND "T0"."SCHEDULENUMBER"="T4"."SCHEDULENUMBER"
AND "T0"."TERMINATIONNUMBER"="T4"."TERMINATIONNUMBER")
3 - filter("T4"."TERMINATIONNUMBER"=0)
4 - access("T0"."AGREEMENTNUMBER"="T3"."AGREEMENTNUMBER" AND "T0"."SCHEDULENUMBER"="T3"."SCHEDULENUMBER"
AND "T0"."TERMINATIONNUMBER"="T3"."TERMINATIONNUMBER")
5 - access("T0"."AGREEMENTNUMBER"="T1"."AGREEMENTNUMBER" AND "T0"."SCHEDULENUMBER"="T1"."SCHEDULENUMBER")
6 - filter("T1"."VARIABLERATETYPE"=U'U')
7 - access("T0"."ID"="T2"."FLOATINGRATEROLLOVERID")
8 - filter("T2"."PROCESSINGDATE"<=TO_NUMBER(:PROCESSINGDATE))
9 - filter("T0"."ADJUSTMENTPROCESSEDINDICATOR"=U'N' AND "T0"."TERMINATIONNUMBER"=0)
10 - filter("T3"."TERMINATIONNUMBER"=0 AND "T3"."SCHEDULESTATUS"<>U'PRE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
257573 consistent gets
0 physical reads
1288 redo size
542 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Мой вопрос в том, есть ли более простой способ улучшить этот запрос с учетом объемов, или, возможно,
Заранее спасибо.