SELECT f.CURRENCY_ID AS CURRENCY_ID,
SUM(f.BAL_MR * COALESCE(CAST(frp_0.param_value AS DECIMAL(21,7)), 1)) AS BAL_MR0
FROM FINTELLIX_BANKING_REGREP.RC_MCB_FCT_BLS0100 f
INNER JOIN FINTELLIX_BANKING_REGREP.FCT_REP_PARAMETER frp_0
ON f.period_id = frp_0.period_id
AND LTRIM(RTRIM(frp_0.param_mnemonic)) = 'ABS'
WHERE f.period_id = 20231129
AND ( (f.CURRENCY_ID NOT IN (41) ) )
AND (LTRIM(RTRIM(f.CHART_OF_ACCOUNT_BKEY_5)) IN (
(SELECT bkey FROM FINTELLIX_BANKING_REGREP.rl_engine_Filter WHERE id IN(34643,34644,34645)
)) )
AND (LTRIM(RTRIM(f.MATURITY_BAND_IND)) IN (
(SELECT bkey FROM FINTELLIX_BANKING_REGREP.rl_engine_Filter WHERE id IN(34646)
)) )
AND (LTRIM(RTRIM(f.GL_IND)) =
(SELECT bkey FROM FINTELLIX_BANKING_REGREP.rl_engine_Filter WHERE id IN(34647)
) )
GROUP BY f.CURRENCY_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 2.17 2.20 0 49 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 605.62 712.45 108788905 111927619 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 607.79 714.66 108788905 111927668 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 HASH GROUP BY (cr=0 pr=0 pw=0 time=130 us starts=1 cost=352374 size=350 card=1)
0 0 0 HASH JOIN SEMI (cr=0 pr=0 pw=0 time=91 us starts=1 cost=352371 size=350 card=1)
0 0 0 NESTED LOOPS SEMI (cr=0 pr=0 pw=0 time=89 us starts=1 cost=352371 size=350 card=1)
0 0 0 STATISTICS COLLECTOR (cr=0 pr=0 pw=0 time=83 us starts=1)
0 0 0 HASH JOIN OUTER (cr=0 pr=0 pw=0 time=70 us starts=1 cost=352368 size=342 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=70 us starts=1 cost=351301 size=200 card=1)
0 0 0 HASH JOIN OUTER (cr=0 pr=0 pw=0 time=70 us starts=1 cost=351296 size=198 card=1)
0 0 0 JOIN FILTER CREATE :BF0000 (cr=0 pr=0 pw=0 time=70 us starts=1 cost=239703 size=188 card=1)
0 0 0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=55 us starts=1 cost=239703 size=188 card=1)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=55 us starts=1 cost=239698 size=186 card=1)
0 0 0 JOIN FILTER CREATE :BF0001 (cr=0 pr=0 pw=0 time=55 us starts=1 cost=128106 size=176 card=1)
0 0 0 HASH JOIN (cr=0 pr=0 pw=0 time=51 us starts=1 cost=128106 size=176 card=1)
31702 31702 31702 NESTED LOOPS (cr=111661088 pr=108522458 pw=0 time=709716686 us starts=1 cost=128106 size=176 card=1)
31702 31702 31702 NESTED LOOPS (cr=111661088 pr=108522458 pw=0 time=709700109 us starts=1 cost=128106 size=176 card=1)
31702 31702 31702 STATISTICS COLLECTOR (cr=111661088 pr=108522458 pw=0 time=709676297 us starts=1)
31702 31702 31702 NESTED LOOPS OUTER (cr=111661088 pr=108522458 pw=0 time=711432099 us starts=1 cost=128105 size=121 card=1)
31703 31703 31703 NESTED LOOPS OUTER (cr=106150 pr=42626 pw=0 time=1381592 us starts=1 cost=16676 size=117 card=1)
31703 31703 31703 HASH JOIN (cr=42650 pr=42626 pw=0 time=434404 us starts=1 cost=16653 size=100 card=1)
1 1 1 PART JOIN FILTER CREATE :BF0002 (cr=6 pr=0 pw=0 time=57 us starts=1 cost=4 size=65 card=1)
1 1 1 NESTED LOOPS (cr=6 pr=0 pw=0 time=39 us starts=1 cost=4 size=65 card=1)
1 1 1 TABLE ACCESS BY INDEX ROWID RL_ENGINE_FILTER (cr=3 pr=0 pw=0 time=13 us starts=1 cost=2 size=8 card=1)
1 1 1 INDEX UNIQUE SCAN SYS_C0014871 (cr=2 pr=0 pw=0 time=7 us starts=1 cost=1 size=0 card=1)(object id 5391796)
1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED FCT_REP_PARAMETER (cr=3 pr=0 pw=0 time=23 us starts=1 cost=2 size=57 card=1)
10 10 10 INDEX RANGE SCAN PK_FCTREPPARAMETER (cr=2 pr=0 pw=0 time=17 us starts=1 cost=1 size=0 card=9)(object id 5216568)
32103 32103 32103 PARTITION LIST SINGLE PARTITION: KEY(AP) KEY(AP) (cr=42644 pr=42626 pw=0 time=170677 us starts=1 cost=16648 size=21945 card=627)
32103 32103 32103 TABLE ACCESS STORAGE FULL FCT_MCB_GL_LEDGER PARTITION: 1100 1100 (cr=42644 pr=42626 pw=0 time=147777 us starts=1 cost=16648 size=21945 card=627)
1 1 1 TABLE ACCESS BY INDEX ROWID RL_ENGINE_FILTER (cr=3 pr=0 pw=0 time=7 us starts=1 cost=2 size=8 card=1)
1 1 1 INDEX UNIQUE SCAN SYS_C0014871 (cr=2 pr=0 pw=0 time=5 us starts=1 cost=1 size=0 card=1)(object id 5391796)
31703 31703 31703 VIEW PUSHED PREDICATE RC_FCT_CURRENCY_RATE (cr=63500 pr=0 pw=0 time=670293 us starts=31703 cost=23 size=17 card=1)
31703 31703 31703 FILTER (cr=63500 pr=0 pw=0 time=633020 us starts=31703)
31703 31703 31703 NESTED LOOPS (cr=63500 pr=0 pw=0 time=563723 us starts=31703 cost=4 size=84 card=1)
31703 31703 31703 NESTED LOOPS (cr=31783 pr=0 pw=0 time=465581 us starts=31703 cost=2 size=52 card=1)
31703 31703 31703 TABLE ACCESS BY INDEX ROWID DIM_CURRENCY (cr=31707 pr=0 pw=0 time=316012 us starts=31703 cost=1 size=25 card=1)
31703 31703 31703 INDEX UNIQUE SCAN PK_DIMCURRENCY (cr=4 pr=0 pw=0 time=119393 us starts=31703 cost=0 size=0 card=1)(object id 5212970)
31703 31703 31703 INDEX UNIQUE SCAN PK_FCTCURRENCYRATE (cr=76 pr=0 pw=0 time=99870 us starts=31703 cost=1 size=27 card=1)(object id 5213240)
1 1 1 HASH UNIQUE (cr=62 pr=0 pw=0 time=426 us starts=1 cost=19 size=23 card=1)
1 1 1 TABLE ACCESS STORAGE FULL DIM_CURRENCY (cr=62 pr=0 pw=0 time=226 us starts=1 cost=18 size=23 card=1)
31703 31703 31703 TABLE ACCESS BY INDEX ROWID FCT_CURRENCY_RATE (cr=31717 pr=0 pw=0 time=144254 us starts=31703 cost=2 size=32 card=1)
31703 31703 31703 INDEX UNIQUE SCAN PK_FCTCURRENCYRATE (cr=14 pr=0 pw=0 time=89995 us starts=31703 cost=1 size=0 card=1)(object id 5213240)
125 125 125 VIEW PUSHED PREDICATE (cr=111554940 pr=108479832 pw=0 time=710891340 us starts=31703 cost=111429 size=4 card=1)
125 125 125 FILTER (cr=111554940 pr=108479832 pw=0 time=710861513 us starts=31703)
125 125 125 NESTED LOOPS (cr=111554940 pr=108479832 pw=0 time=710836778 us starts=31703 cost=111429 size=60 card=1)
253619 253619 253619 TABLE ACCESS STORAGE FULL DIM_CUSTOMER (cr=111060598 pr=108746279 pw=0 time=814732681 us starts=31703 cost=111414 size=210 card=5)
125 125 125 INDEX RANGE SCAN CUST_RELATION_PK (cr=760871 pr=0 pw=0 time=2238676 us starts=253619 cost=3 size=18 card=1)(object id 5392092)
0 0 0 INDEX UNIQUE SCAN PK_D_CHARTOFACCOUNTS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=0 size=0 card=1)(object id 5213624)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_CHART_OF_ACCOUNTS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=55 card=1)
0 0 0 TABLE ACCESS STORAGE FULL DIM_CHART_OF_ACCOUNTS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=55 card=1)
0 0 0 JOIN FILTER USE :BF0001 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=111573 size=72892760 card=7289276)
0 0 0 TABLE ACCESS STORAGE FULL DIM_CUSTOMER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=111573 size=72892760 card=7289276)
0 0 0 VIEW RC_DIM_MIS_CD1_D46 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=5 size=2 card=1)
0 0 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=5 size=272 card=2)
0 0 0 UNION ALL PUSHED PREDICATE (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=68 card=1)
0 0 0 INDEX UNIQUE SCAN PK_DIMMISCODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 5213049)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=68 card=1)
0 0 0 INDEX UNIQUE SCAN PK_DIMMISCODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 5213049)
0 0 0 JOIN FILTER USE :BF0000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=111573 size=72892760 card=7289276)
0 0 0 TABLE ACCESS STORAGE FULL DIM_CUSTOMER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=111573 size=72892760 card=7289276)
0 0 0 VIEW RC_DIM_MIS_CD1_D46 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=5 size=2 card=1)
0 0 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=5 size=272 card=2)
0 0 0 UNION ALL PUSHED PREDICATE (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=68 card=1)
0 0 0 INDEX UNIQUE SCAN PK_DIMMISCODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 5213049)
0 0 0 FILTER (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=68 card=1)
0 0 0 INDEX UNIQUE SCAN PK_DIMMISCODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 5213049)
0 0 0 VIEW RC_DIM_MIS_CD2_F43 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1067 size=568 card=4)
0 0 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1067 size=544 card=4)
0 0 0 UNION-ALL (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS STORAGE FULL DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1063 size=204 card=3)
0 0 0 TABLE ACCESS BY INDEX ROWID DIM_MIS_CODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=68 card=1)
0 0 0 INDEX UNIQUE SCAN PK_DIMMISCODE (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=1)(object id 5213049)
0 0 0 INLIST ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID RL_ENGINE_FILTER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=8 card=1)
0 0 0 INDEX UNIQUE SCAN SYS_C0014871 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=2)(object id 5391796)
0 0 0 INLIST ITERATOR (cr=0 pr=0 pw=0 time=0 us starts=0)
0 0 0 TABLE ACCESS BY INDEX ROWID RL_ENGINE_FILTER (cr=0 pr=0 pw=0 time=0 us starts=0 cost=3 size=8 card=1)
0 0 0 INDEX UNIQUE SCAN SYS_C0014871 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=2 size=0 card=2)(object id 5391796)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 341 0.00 0.00
Disk file operations I/O 354 0.00 0.00
SQL*Net message to client 1 0.00 0.00
reliable message 31703 0.01 23.34
IPC group service call 2 0.00 0.00
enq: KO - fast object checkpoint 63239 0.00 5.85
cell smart table scan 1610850 0.01 44.04
latch: ges resource hash list 34 0.00 0.00
latch free 6 0.00 0.00
KJC: Wait for msg sends to complete 11 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
SQL ID: 7cfz5wy9caaf4 Plan Hash: 4015672053
SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,
'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,
DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM
FROM
V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY
NAME_COL_PLUS_SHOW_PARAM,ROWNUM
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 0 6
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
6 6 6 SORT ORDER BY (cr=0 pr=0 pw=0 time=4840 us starts=1 cost=2 size=59 card=1)
6 6 6 COUNT (cr=0 pr=0 pw=0 time=4808 us starts=1)
6 6 6 HASH JOIN (cr=0 pr=0 pw=0 time=4805 us starts=1 cost=1 size=59 card=1)
85 85 85 FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=2454 us starts=1 cost=1 size=574 card=14)
5862 5862 5862 FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=1568 us starts=1 cost=1 size=97416 card=5412)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
PGA memory operation 18 0.00 0.00
SQL*Net message from client 2 0.72 0.72
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 2.17 2.20 0 49 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 3 605.62 712.46 108788905 111927619 0 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 607.80 714.67 108788905 111927668 0 6
Misses in library cache during parse: 2
Misses in library cache during execute: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 5 0.00 0.00
SQL*Net message from client 5 21.01 25.52
PGA memory operation 361 0.00 0.00
Disk file operations I/O 354 0.00 0.00
reliable message 31703 0.01 23.34
IPC group service call 2 0.00 0.00
enq: KO - fast object checkpoint 63239 0.00 5.85
cell smart table scan 1610850 0.01 44.04
latch: ges resource hash list 34 0.00 0.00
latch free 6 0.00 0.00
KJC: Wait for msg sends to complete 11 0.00 0.00
SQL*Net break/reset to client 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 21 0.00 0.00 0 0 0 0
Execute 23 0.00 0.00 0 0 0 0
Fetch 23 0.00 0.00 0 49 0 23
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 67 0.00 0.00 0 49 0 23
Misses in library cache during parse: 2
Misses in library cache during execute: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 2 0.00 0.00
2 user SQL statements in session.
5 internal SQL statements in session.
7 SQL statements in session.
********************************************************************************
Trace file: findev1_ora_319309_SLOWFINDEV.trc
Trace file compatibility: 12.2.0.0
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
5 internal SQL statements in trace file.
7 SQL statements in trace file.
4 unique SQL statements in trace file.
1708473 lines in trace file.
736 elapsed seconds in trace file.