Re: Need design suggestion , whether index organized table is good choice here
Date: Fri, 4 Feb 2022 01:20:22 +0530
Message-ID: <CAEjw_fhhVn4QgDNAvPEn=07cZy1NcbHVsDZKSSn5E=1TRjqK4w_at_mail.gmail.com>
Thank You So much for all the suggestions.
The query which we were trying to tune was something as below. And we are soon planning to move into 19C. So on one of the Dev databases which is already on 19c , I was trying to test two things as suggested by the experts here 1)If the table ref_tab can go for bloom pruning 2) What is the effect of the covering index on the two columns of the table REF_TAB.
Though the volume was not anywhere matching production, I do see the difference in behaviour, i.e. on 19c the view is merged with the outer joined ref_tab table, which is not happening on 11.2.0.4. Also when I forced the FTS plan, the bloom fiter pruning is only happening on 19C with that merged view plan but not with the 11.2.0.4 OFE. So this is pointing to the fact that the transformation is available in 19C but not in 11.2.0.4. I have added below the first two sql monitors from dev , one with the covered index(as sayan suggested) and other with the FTS on the REF_TAB table doing bloom pruning as Lothar was pointing.
Also we have asked the database+etl design team to look into making the design better so as to minimize the scan of the table REF_TAB. But as a quick fix in prod, we have been asked to test the data fetch query with reduced matching volume from TRAN_TAB. And we are seeing with ~4-5million rows(rather ~115million rows which we were getting before) the "Hash join outer" is happening fully in memory and the query is finishing in <5minutes. I have provided the last two sql monitors below from production with this volume (~4-5million rows) fetched from the query, and it shows zero temp spill. Also the query with an index access from TAB_TAB2 seems a better path as compared to FTS on all tables.
So based on these results, we are hopeful for better performance for this query in 19C , however at the moment, we are trying to restrict the data fetch to ~4-5million with multiple runs. And are also planning to test the covering index on production.
SELECT TADD.TX_ID, TADD.SBID, TADD.PART_DATE1, TADD.DID, TADD.ANBR,
REF_TAB.masked_col1
FROM (SELECT TRAN_TAB.TX_ID,......
FROM TRAN_TAB, TRAN_TAB2
WHERE TRAN_TAB.PART_DATE1 = TRAN_TAB2.PART_DATE1(+)
AND TRAN_TAB.TX_ID = TRAN_TAB2.TX_ID(+)
AND TRAN_TAB2.P_CD(+) = 'XX'
AND TRAN_TAB.SBID IN
(SELECT SFID FROM STAGE_TAB WHERE ASP_NM = :B2
AND PART_DATE = to_date(:B1,'DD-MON-YYYY') )
AND TRAN_TAB.PART_DATE1 = to_date(:B1,'DD-MON-YYYY')) TADD,
REF_TAB
WHERE TADD.ANBR = REF_TAB.COL1(+)
- From Dev with covering index for COL1 and masked_col1
Global Information
Status : DONE (ALL ROWS) Instance ID : 2 SQL ID : 4cvk1waaff1vw SQL Execution ID : 33554433 Execution Started : 02/03/2022 13:22:14 First Refresh Time : 02/03/2022 13:22:14 Last Refresh Time : 02/03/2022 13:22:20 Duration : 6s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 21
Global Stats
| Elapsed | Cpu | IO | Application | Other | Fetch | Buffer |
Read | Read | Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes | Elig Bytes | Returned Bytes |
| 4.53 | 4.31 | 0.21 | 0.00 | 0.01 | 21 | 331K |
2342 | 2GB | 2GB | 2GB |
SQL Plan Monitoring Details (Plan Hash Value=1150610528)
| Id | Operation | Name | Rows
| Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
| 0 | SELECT STATEMENT | |
| | 3 | +4 | 1 | 99953 | | | . |
| |
| 1 | HASH JOIN OUTER | |
99822 | 140K | 3 | +4 | 1 | 99953 | | |
13MB | | |
| 2 | HASH JOIN OUTER | |
99822 | 117K | 3 | +2 | 1 | 99953 | | |
12MB | | |
| 3 | JOIN FILTER CREATE | :BF0000 |
99822 | 67865 | 3 | +2 | 1 | 99953 | | |
. | | |
| 4 | HASH JOIN | |
99822 | 67865 | 3 | +2 | 1 | 99953 | | |
12MB | | |
| 5 | JOIN FILTER CREATE | :BF0001 |
99822 | 444 | 1 | +2 | 1 | 99953 | | |
. | | |
| 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB |
99822 | 444 | 1 | +2 | 1 | 99953 | | |
. | | |
| 7 | JOIN FILTER USE | :BF0001 |
9M | 48130 | 3 | +2 | 1 | 514K | | | .
| | |
| 8 | TABLE ACCESS STORAGE FULL | TRAN_TAB |
9M | 48130 | 4 | +1 | 1 | 514K | 1383 | 1GB | 7MB
| | |
| 9 | JOIN FILTER USE | :BF0000 |
9M | 33017 | 1 | +4 | 1 | 538K | | | .
| | |
| 10 | TABLE ACCESS STORAGE FULL | TRAN_TAB2 |
9M | 33017 | 2 | +3 | 1 | 538K | 959 | 939MB | 7MB
| | |
| 11 | INDEX STORAGE FAST FULL SCAN | REF_TAB_IX2 |
10M | 10192 | 3 | +4 | 1 | 10M | | | .
| | |
==================================================================================================================================================================================
Predicate Information (identified by operation id):
1 -
access("REF_TAB"."COL1"(+)=TRIM(NVL("TRAN_TAB2"."LITEM4","TRAN_TAB"."ANBR")))
2 - access("TRAN_TAB"."PART_DATE1"="TRAN_TAB2"."PART_DATE1"(+) AND
"TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID"(+))
4 - access("TRAN_TAB"."SBID"="SFID")
6 - storage("PART_DATE"=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXX')
filter("PART_DATE"=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXX')
8 - storage("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0001,"TRAN_TAB"."SBID"))
filter("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0001,"TRAN_TAB"."SBID"))
10 - storage("TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "TRAN_TAB2"."P_CD"(+)='XX' AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB2"."PART_DATE1"(+),"TRAN_TAB2"."TX_ID"(+) ))
filter("TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRAN_TAB2"."P_CD"(+)='XX' AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB2"."PART_DATE1"(+),"TRAN_TAB2"."TX_ID"(+)
))
Statistics
12 recursive calls
0 db block gets
330551 consistent gets
292788 physical reads
0 redo size
3555387 bytes sent via SQL*Net to client
709 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99953 rows processed
**************** From Dev with FTS on REF_TAB ***************
Global Information
Status : DONE (ALL ROWS) Instance ID : 2 SQL ID : 2v9a7s4hnp7y0 SQL Execution ID : 33554432 Execution Started : 02/03/2022 13:26:57 First Refresh Time : 02/03/2022 13:26:57 Last Refresh Time : 02/03/2022 13:27:02 Duration : 5s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 21
Global Stats
| Elapsed | Cpu | IO | Application | Fetch | Buffer | Read | Read
| Offload | Offload |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs |
Bytes | Elig Bytes | Returned Bytes |
| 4.42 | 4.17 | 0.26 | 0.00 | 21 | 351K | 2819 |
3GB | 3GB | 3GB |
SQL Plan Monitoring Details (Plan Hash Value=4056119139)
| Id | Operation | Name | Rows
| Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
| 0 | SELECT STATEMENT | |
| | 4 | +2 | 1 | 99953 | | | . |
| |
| 1 | HASH JOIN OUTER | |
99822 | 145K | 4 | +2 | 1 | 99953 | | |
12MB | 25.00 | Cpu (1) |
| 2 | JOIN FILTER CREATE | :BF0000 |
99822 | 117K | 2 | +2 | 1 | 99953 | | |
. | | |
| 3 | HASH JOIN OUTER | |
99822 | 117K | 2 | +2 | 1 | 99953 | | |
12MB | | |
| 4 | JOIN FILTER CREATE | :BF0001 |
99822 | 67865 | 1 | +2 | 1 | 99953 | | |
. | | |
| 5 | HASH JOIN | |
99822 | 67865 | 1 | +2 | 1 | 99953 | | |
12MB | | |
| 6 | JOIN FILTER CREATE | :BF0002 |
99822 | 444 | 1 | +2 | 1 | 99953 | | |
. | | |
| 7 | TABLE ACCESS STORAGE FULL | STAGE_TAB |
99822 | 444 | 1 | +2 | 1 | 99953 | | |
. | | |
| 8 | JOIN FILTER USE | :BF0002 |
9M | 48130 | 1 | +2 | 1 | 514K | | | .
| | |
| 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB |
9M | 48130 | 2 | +1 | 1 | 514K | 1383 | 1GB | 7MB
| 25.00 | Cpu (1) |
| 10 | JOIN FILTER USE | :BF0001 |
9M | 33017 | 2 | +2 | 1 | 538K | | | .
| | |
| 11 | TABLE ACCESS STORAGE FULL | TRAN_TAB2 |
9M | 33017 | 2 | +2 | 1 | 538K | 959 | 939MB | 7MB
| 50.00 | Cpu (2) |
| 12 | JOIN FILTER USE | :BF0000 |
10M | 15376 | 3 | +3 | 1 | 10M | | | .
| | |
| 13 | TABLE ACCESS STORAGE FULL | REF_TAB |
10M | 15376 | 3 | +3 | 1 | 10M | 477 | 440MB | 7MB
| | |
===================================================================================================================================================================================
Predicate Information (identified by operation id):
1 -
access("REF_TAB"."COL1"(+)=TRIM(NVL("TRAN_TAB2"."LITEM4","TRAN_TAB"."ANBR")))
3 - access("TRAN_TAB"."PART_DATE1"="TRAN_TAB2"."PART_DATE1"(+) AND
"TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID"(+))
5 - access("TRAN_TAB"."SBID"="SFID")
7 - storage("PART_DATE"=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXX')
filter("PART_DATE"=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXX')
9 - storage("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0002,"TRAN_TAB"."SBID")) filter("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0002,"TRAN_TAB"."SBID")) 11 - storage("TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRAN_TAB2"."P_CD"(+)='XX' AND
SYS_OP_BLOOM_FILTER(:BF0001,"TRAN_TAB2"."PART_DATE1"(+),"TRAN_TAB2"."TX_ID"(+)) )
filter("TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "TRAN_TAB2"."P_CD"(+)='XX' AND
SYS_OP_BLOOM_FILTER(:BF0001,"TRAN_TAB2"."PART_DATE1"(+),"TRAN_TAB2"."TX_ID"(+))
)
13 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"REF_TAB"."COL1"(+)))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"REF_TAB"."COL1"(+)))
Statistics
12 recursive calls
0 db block gets
350747 consistent gets
349058 physical reads
0 redo size
3555387 bytes sent via SQL*Net to client
709 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
99953 rows processed
**************** From production with Lesser volume and index access on
TRAN_TAB2***************
Global Information
Status : DONE (ALL ROWS) Instance ID : 4 SQL ID : 2v5sxyj9zqcb3 SQL Execution ID : 67108864 Execution Started : 02/03/2022 04:48:58 First Refresh Time : 02/03/2022 04:49:02 Last Refresh Time : 02/03/2022 04:53:03 Duration : 245s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 801
Global Stats
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer |
Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes | Offload |
| 209 | 192 | 16 | 0.02 | 0.00 | 801 | 37M |
387K | 251GB | 61.39% |
SQL Plan Monitoring Details (Plan Hash Value=2612108243)
| Id | Operation | Name |
Rows | Cost | Time | Start | Execs | Rows | Read | Read |
Cell | Mem | Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | =========================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | |
| | 242 | +4 | 1 | 4M | | | | | 1.01 | Cpu (1) |
| | | |
| | | | | | | | | | | SQL*Net more data to client (1) |
| 1 | HASH JOIN OUTER | |
749K | 13M | 242 | +4 | 1 | 4M | | | | 386M | 64.82 | Cpu (129) |
| 2 | VIEW | |
749K | 4M | 22 | +4 | 1 | 4M | | | | | | |
| 3 | NESTED LOOPS OUTER | |
749K | 4M | 22 | +4 | 1 | 4M | | | | | | |
| 4 | HASH JOIN | |
749K | 2M | 22 | +4 | 1 | 4M | | | | 1M | 0.50 | Cpu (1) |
| 5 | JOIN FILTER CREATE | :BF0000 |
406 | 66074 | 1 | +4 | 1 | 174 | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB |
406 | 66074 | 1 | +4 | 1 | 174 | | | | 7M | | |
| 7 | JOIN FILTER USE | :BF0000 |
1G | 2M | 22 | +4 | 1 | 5M | | | | | | |
| 8 | PARTITION RANGE SINGLE | |
1G | 2M | 22 | +4 | 1 | 5M | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB |
1G | 2M | 22 | +4 | 1 | 5M | 79079 | 77GB | 99.63% | 7M | 0.50 | Cpu (1) |
| 10 | PARTITION RANGE SINGLE | |
1 | 4 | 22 | +4 | 4M | 343K | | | | | 1.01 | Cpu (2) |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID | TRAN_TAB2 |
1 | 4 | 24 | +2 | 4M | 343K | 18552 | 145MB | | | 6.53 | Cpu (2) |
| | | |
| | | | | | | | | | | cell single block physical read (11) |
| 12 | INDEX RANGE SCAN | TRAN_TAB2_IX1 |
1 | 3 | 23 | +3 | 4M | 343K | 7624 | 60MB | | | 3.02 | Cpu (1) |
| | | |
| | | | | | | | | | | cell single block physical read (5) |
| 13 | TABLE ACCESS STORAGE FULL | REF_TAB |
2G | 4M | 221 | +25 | 1 | 2G | 265K | 163GB | 40.83% | 7M | 22.61 | Cpu (40) |
| | | |
| | | | | | | | | | | cell smart table scan (5) | =========================================================================================================================================================================================================================
Predicate Information (identified by operation id):
1 - access("TADD"."ANBR"="REF_TAB"."COL1"(+))
4 - access("TRAN_TAB"."SBID"="SFID")
6 - storage("PART_DATE"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXXXXXXX' AND ("SFID" LIKE '%XX' OR
"SFID" LIKE '%XX'))
filter("PART_DATE"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXXXXXXX' AND
("SFID" LIKE '%XX' OR "SFID" LIKE '%XX'))
9 - storage("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")) filter("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")) 11 - filter("TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
12 - access("TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID"(+) AND "TRAN_TAB2"."P_CD"(+)='XX')
Statistics
5 recursive calls
0 db block gets
36819673 consistent gets
32963062 physical reads
1684876 redo size
314090562 bytes sent via SQL*Net to client
9261 bytes received via SQL*Net from client
801 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3996985 rows processed
- From production with Lesser volume and all FTS***************
Global Information
Status : DONE (ALL ROWS) Instance ID : 4 SQL ID : cqhxktctua455 SQL Execution ID : 67108864 Execution Started : 02/03/2022 04:14:49 First Refresh Time : 02/03/2022 04:14:53 Last Refresh Time : 02/03/2022 04:19:26 Duration : 277s Module/Action : SQL*Plus/- Program : sqlplus.exe Fetch Calls : 801
Global Stats
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer |
Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes | Offload |
| 242 | 204 | 37 | 0.04 | 0.02 | 801 | 52M |
496K | 378GB | 66.67% |
SQL Plan Monitoring Details (Plan Hash Value=1546773105)
| Id | Operation | Name | Rows
| Cost | Time | Start | Execs | Rows | Read | Read | Cell
| Mem | Activity | Activity Detail |
| | | |
(Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | ==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | |
| | 254 | +24 | 1 | 4M | | |
| | 0.87 | SQL*Net more data to client (2) |
| 1 | HASH JOIN OUTER | |
652K | 14M | 254 | +24 | 1 | 4M | | | | 385M | 54.55 | Cpu (126) |
| 2 | VIEW | |
652K | 5M | 25 | +24 | 1 | 4M | | | | | 0.43 | Cpu (1) |
| 3 | HASH JOIN OUTER | |
652K | 5M | 45 | +4 | 1 | 4M | | | | 383M | 3.46 | Cpu (8) |
| 4 | HASH JOIN | |
652K | 2M | 21 | +4 | 1 | 4M | | | | 1M | | |
| 5 | JOIN FILTER CREATE | :BF0000 |
353 | 66073 | 1 | +4 | 1 | 174 | | | | | | |
| 6 | TABLE ACCESS STORAGE FULL | STAGE_TAB |
353 | 66073 | 4 | +1 | 1 | 174 | | | | 7M | 0.43 | cell smart table scan (1) |
| 7 | JOIN FILTER USE | :BF0000 |
1G | 2M | 21 | +4 | 1 | 5M | | | | | 0.43 | Cpu (1) |
| 8 | PARTITION RANGE SINGLE | |
1G | 2M | 21 | +4 | 1 | 5M | | | | | | |
| 9 | TABLE ACCESS STORAGE FULL | TRAN_TAB |
1G | 2M | 23 | +2 | 1 | 5M | 76610 | 69GB | 87.67% | 7M | 7.79 | Cpu (9) |
| | | |
| | | | | | | |
| | | cell smart table scan (9) |
| 10 | PARTITION RANGE SINGLE | |
35M | 3M | 25 | +24 | 1 | 36M | | | | | | |
| 11 | TABLE ACCESS STORAGE FULL | TRAN_TAB2 |
35M | 3M | 27 | +22 | 1 | 36M | 140K | 127GB | 88.88% | 7M | 7.36 | Cpu (4) |
| | | |
| | | | | | | |
| | | cell smart table scan (13) |
| 12 | TABLE ACCESS STORAGE FULL | REF_TAB |
2G | 4M | 230 | +48 | 1 | 2G | 259K | 163GB | 37.11% | 7M | 24.68 | gc cr grant 2-way (1) |
| | | |
| | | | | | | |
| | | Cpu (51) |
| | | |
| | | | | | | |
| | | cell smart table scan (5) |
==============================================================================================================================================================================================================
Predicate Information (identified by operation id):
1 - access("TADD"."ANBR"="REF_TAB"."COL1"(+))
3 - access("TRAN_TAB"."PART_DATE1"="TRAN_TAB2"."PART_DATE1"(+) AND
"TRAN_TAB"."TX_ID"="TRAN_TAB2"."TX_ID"(+))
4 - access("TRAN_TAB"."SBID"="SFID")
6 - storage("PART_DATE"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXXXXXXX'
AND ("SFID" LIKE '%XX' OR "SFID" LIKE '%XX'))
filter("PART_DATE"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ASP_NM"='XXXXXXXXXXXXXXXX'
AND ("SFID" LIKE '%XX' OR "SFID" LIKE '%XX'))
9 - storage("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-30 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")) filter("TRAN_TAB"."PART_DATE1"=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
SYS_OP_BLOOM_FILTER(:BF0000,"TRAN_TAB"."SBID")) 11 - storage("TRAN_TAB2"."P_CD"(+)='XX' AND "TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("TRAN_TAB2"."P_CD"(+)='XX' AND "TRAN_TAB2"."PART_DATE1"(+)=TO_DATE(' 2022-01-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
5 recursive calls
0 db block gets
51556755 consistent gets
49594231 physical reads
7956 redo size
314116122 bytes sent via SQL*Net to client
9261 bytes received via SQL*Net from client
801 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3996985 rows processed
On Tue, Feb 1, 2022 at 2:56 PM Andy Sayer <andysayer_at_gmail.com> wrote:
> I don’t think it was tongue in cheek. > > This doesn’t look very ETL, it looks TEL as you are doing the transform in > the source environment. The whole point of an ETL is to reduce the workload > on your source environment, this isn’t doing that. > > A good solution for the overall problem could look more like: > * Incremental changes from source database (only read what’s changed and > what will make a difference to what you have on your target system). > * Staging area that calculates what needs changing based on the source > changes > * Load these changes to the target system. > > The staging area could be on your target system. > > There are many products you can purchase to make this go smoothly but it > will depend on your budget and amount of effort you’re willing to make. > This is where an outside consultant can really help you decide (yes, you’ll > end up paying a lot but if they have the expertise and can properly > determine what your needs are then you are going to be in a much better > place). > > Thanks, > Andy > > On Mon, 31 Jan 2022 at 20:27, Mladen Gogala <gogala.mladen_at_gmail.com> > wrote: > >> On 1/31/22 05:39, Jonathan Lewis wrote: >> >> 1) Upgrade to 19c >> 2) Hire someone to sign an NDA and get them to do a proper database >> design after a full briefing on requirements. >> >> You are NOT asking for a "design suggestion", you're asking for yet >> another hack/stop-gap to keep your system limping along until it gets so >> unwieldy that it will be virtually impossible to do anything to fix it. >> >> >> Regards >> Jonathan Lewis >> >> Two points: >> >> 1. It is usually I who writes tongue in cheek posts like this one. >> 2. What you are describing is the usual point of application >> redesign. When the design gets too messed up to do anything that makes >> sense, usually the hardware is thrown at the problem until the redesign is >> done. >> >> Regards >> >> >> -- >> Mladen Gogala >> Database Consultant >> Tel: (347) 321-1217https://dbwhisperer.wordpress.com >> >> -- http://www.freelists.org/webpage/oracle-l > >
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 03 2022 - 20:50:22 CET