Oracle 19.27
RAC 2 nodes, exadata X-10
Hello,
greetings.
could you please advise how below query could be tuned?
SQL_ID 8c7n73u4rtbjk, child number 0
-------------------------------------
SELECT f.CURRENCY_ID AS CURRENCY_ID,SUM(f.NOSTRO_BALANCE) AS
NOSTRO_BALANCE0,SUM(f.SHORT_TERM_LIABILITIES_BAL) AS
SHORT_TERM_LIABILITIES_B1 FROM fintellix_banking_regrep.RC_FM0600_NOSTRO
_BALANCE f WHERE f.period_id = 20230301 GROUP BY f.CURRENCY_ID
Plan hash value: 544310323
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 19M(100)| | | |
| 1 | HASH GROUP BY | | 1 | 78 | 19M (1)| 00:12:30 | | |
|* 2 | VIEW | VW_FOJ_0 | 2 | 156 | 19M (1)| 00:12:30 | | |
|* 3 | HASH JOIN FULL OUTER | | 2 | 496 | 19M (1)| 00:12:30 | | |
| 4 | VIEW | | 1 | 124 | 19M (1)| 00:12:30 | | |
| 5 | HASH GROUP BY | | 1 | 2562 | 19M (1)| 00:12:30 | | |
|* 6 | HASH JOIN SEMI | | 1 | 2562 | 19M (1)| 00:12:30 | | |
|* 7 | HASH JOIN OUTER | | 1 | 560 | 19M (1)| 00:12:30 | | |
| 8 | NESTED LOOPS OUTER | | 1 | 418 | 19M (1)| 00:12:30 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 416 | 19M (1)| 00:12:30 | | |
| 10 | NESTED LOOPS OUTER | | 1 | 406 | 19M (1)| 00:12:30 | | |
| 11 | NESTED LOOPS | | 1 | 404 | 19M (1)| 00:12:30 | | |
|* 12 | HASH JOIN | | 1 | 394 | 19M (1)| 00:12:30 | | |
| 13 | NESTED LOOPS OUTER | | 1 | 339 | 19M (1)| 00:12:30 | | |
|* 14 | FILTER | | | | | | | |
| 15 | NESTED LOOPS OUTER | | 1 | 335 | 19M (1)| 00:12:30 | | |
|* 16 | HASH JOIN | | 1 | 318 | 19M (1)| 00:12:30 | | |
| 17 | JOIN FILTER CREATE | :BF0000 | 1 | 95 | 36 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS STORAGE FULL | DIM_CURRENCY | 1 | 95 | 36 (0)| 00:00:01 | | |
| 19 | JOIN FILTER USE | :BF0000 | 1530K| 325M| 19M (1)| 00:12:30 | | |
| 20 | PARTITION LIST ALL | | 1530K| 325M| 19M (1)| 00:12:30 | 1 | 1100 |
|* 21 | TABLE ACCESS STORAGE FULL | FCT_MCB_GL_LEDGER | 1530K| 325M| 19M (1)| 00:12:30 | 1 | 1100 |
| 22 | VIEW PUSHED PREDICATE | RC_FCT_CURRENCY_RATE | 1 | 17 | 41 (3)| 00:00:01 | | |
| 23 | NESTED LOOPS | | 1 | 154 | 4 (0)| 00:00:01 | | |
| 24 | NESTED LOOPS | | 1 | 122 | 2 (0)| 00:00:01 | | |
| 25 | TABLE ACCESS BY INDEX ROWID | DIM_CURRENCY | 1 | 95 | 1 (0)| 00:00:01 | | |
|* 26 | INDEX UNIQUE SCAN | PK_DIMCURRENCY | 1 | | 0 (0)| | | |
|* 27 | INDEX UNIQUE SCAN | PK_FCTCURRENCYRATE | 1 | 27 | 1 (0)| 00:00:01 | | |
| 28 | HASH UNIQUE | | 1 | 88 | 37 (3)| 00:00:01 | | |
|* 29 | TABLE ACCESS STORAGE FULL | DIM_CURRENCY | 1 | 88 | 36 (0)| 00:00:01 | | |
| 30 | TABLE ACCESS BY INDEX ROWID | FCT_CURRENCY_RATE | 1 | 32 | 2 (0)| 00:00:01 | | |
|* 31 | INDEX UNIQUE SCAN | PK_FCTCURRENCYRATE | 1 | | 1 (0)| 00:00:01 | | |
| 32 | VIEW PUSHED PREDICATE | | 1 | 4 | 136 (1)| 00:00:01 | | |
| 33 | NESTED LOOPS | | 1 | 60 | 136 (1)| 00:00:01 | | |
| 34 | NESTED LOOPS | | 1 | 60 | 136 (1)| 00:00:01 | | |
|* 35 | INDEX RANGE SCAN | CUST_RELATION_PK | 1 | 18 | 134 (1)| 00:00:01 | | |
|* 36 | INDEX UNIQUE SCAN | PK_D_CUSTOMER | 1 | | 1 (0)| 00:00:01 | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | DIM_CUSTOMER | 1 | 42 | 2 (0)| 00:00:01 | | |
| 38 | VIEW | index$_join$_043 | 1467 | 80685 | 21 (0)| 00:00:01 | | |
|* 39 | HASH JOIN | | | | | | | |
| 40 | INDEX STORAGE FAST FULL SCAN | PK_D_CHARTOFACCOUNTS | 1467 | 80685 | 8 (0)| 00:00:01 | | |
| 41 | INDEX STORAGE FAST FULL SCAN | UQ_D_CHARTOFACCOUNTS_1 | 1467 | 80685 | 19 (0)| 00:00:01 | | |
| 42 | TABLE ACCESS BY INDEX ROWID | DIM_CUSTOMER | 1 | 10 | 2 (0)| 00:00:01 | | |
|* 43 | INDEX UNIQUE SCAN | PK_D_CUSTOMER | 1 | | 1 (0)| 00:00:01 | | |
| 44 | VIEW | RC_DIM_MIS_CD1_D46 | 1 | 2 | 7 (15)| 00:00:01 | | |
| 45 | SORT UNIQUE | | 2 | 272 | 7 (15)| 00:00:01 | | |
| 46 | UNION ALL PUSHED PREDICATE | | | | | | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | DIM_MIS_CODE | 1 | 68 | 3 (0)| 00:00:01 | | |
|* 48 | INDEX UNIQUE SCAN | PK_DIMMISCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 49 | FILTER | | | | | | | |
| 50 | TABLE ACCESS BY INDEX ROWID | DIM_MIS_CODE | 1 | 68 | 3 (0)| 00:00:01 | | |
|* 51 | INDEX UNIQUE SCAN | PK_DIMMISCODE | 1 | | 2 (0)| 00:00:01 | | |
| 52 | TABLE ACCESS BY INDEX ROWID | DIM_CUSTOMER | 1 | 10 | 2 (0)| 00:00:01 | | |
|* 53 | INDEX UNIQUE SCAN | PK_D_CUSTOMER | 1 | | 1 (0)| 00:00:01 | | |
| 54 | VIEW | RC_DIM_MIS_CD1_D46 | 1 | 2 | 7 (15)| 00:00:01 | | |
| 55 | SORT UNIQUE | | 2 | 272 | 7 (15)| 00:00:01 | | |
| 56 | UNION ALL PUSHED PREDICATE | | | | | | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | DIM_MIS_CODE | 1 | 68 | 3 (0)| 00:00:01 | | |
|* 58 | INDEX UNIQUE SCAN | PK_DIMMISCODE | 1 | | 2 (0)| 00:00:01 | | |
|* 59 | FILTER | | | | | | | |
| 60 | TABLE ACCESS BY INDEX ROWID | DIM_MIS_CODE | 1 | 68 | 3 (0)| 00:00:01 | | |
|* 61 | INDEX UNIQUE SCAN | PK_DIMMISCODE | 1 | | 2 (0)| 00:00:01 | | |
| 62 | VIEW | RC_DIM_MIS_CD2_F43 | 4 | 568 | 9 (23)| 00:00:01 | | |
| 63 | SORT UNIQUE | | 4 | 544 | 9 (23)| 00:00:01 | | |
| 64 | UNION-ALL | | | | | | | |
| 65 | TABLE ACCESS BY INDEX ROWID BATCHED | DIM_MIS_CODE | 3 | 204 | 4 (0)| 00:00:01 | | |
|* 66 | INDEX RANGE SCAN | FK_DIMMISCODE_1 | 3 | | 3 (0)| 00:00:01 | | |
| 67 | TABLE ACCESS BY INDEX ROWID | DIM_MIS_CODE | 1 | 68 | 3 (0)| 00:00:01 | | |
|* 68 | INDEX UNIQUE SCAN | PK_DIMMISCODE | 1 | | 2 (0)| 00:00:01 | | |
| 69 | VIEW | VW_NSO_1 | 12 | 24024 | 72 (0)| 00:00:01 | | |
|* 70 | HASH JOIN | | 12 | 25128 | 72 (0)| 00:00:01 | | |
| 71 | JOIN FILTER CREATE | :BF0001 | 4 | 316 | 36 (0)| 00:00:01 | | |
|* 72 | TABLE ACCESS STORAGE FULL | TAG_MASTER | 4 | 316 | 36 (0)| 00:00:01 | | |
| 73 | JOIN FILTER USE | :BF0001 | 654 | 1286K| 36 (0)| 00:00:01 | | |
|* 74 | TABLE ACCESS STORAGE FULL | TAG_DETAIL | 654 | 1286K| 36 (0)| 00:00:01 | | |
| 75 | VIEW | | 1 | 124 | 549 (1)| 00:00:01 | | |
| 76 | HASH GROUP BY | | 1 | 148 | 549 (1)| 00:00:01 | | |
| 77 | NESTED LOOPS | | 1 | 148 | 549 (1)| 00:00:01 | | |
| 78 | NESTED LOOPS | | 1 | 148 | 549 (1)| 00:00:01 | | |
| 79 | VIEW | VW_GBC_30 | 1 | 53 | 548 (1)| 00:00:01 | | |
| 80 | HASH GROUP BY | | 1 | 464 | 548 (1)| 00:00:01 | | |
|* 81 | FILTER | | | | | | | |
|* 82 | HASH JOIN | | 1 | 464 | 517 (1)| 00:00:01 | | |
| 83 | NESTED LOOPS | | 1 | 309 | 515 (1)| 00:00:01 | | |
| 84 | NESTED LOOPS | | 1 | 309 | 515 (1)| 00:00:01 | | |
| 85 | NESTED LOOPS | | 1 | 214 | 514 (1)| 00:00:01 | | |
| 86 | MERGE JOIN CARTESIAN | | 1 | 127 | 455 (1)| 00:00:01 | | |
|* 87 | TABLE ACCESS STORAGE FULL | DIM_CURRENCY | 1 | 95 | 36 (0)| 00:00:01 | | |
| 88 | BUFFER SORT | | 141K| 4406K| 419 (1)| 00:00:01 | | |
| 89 | TABLE ACCESS STORAGE FULL | FCT_CURRENCY_RATE | 141K| 4406K| 419 (1)| 00:00:01 | | |
| 90 | PARTITION RANGE ITERATOR | | 31 | 2697 | 60 (0)| 00:00:01 | KEY | KEY |
|* 91 | TABLE ACCESS STORAGE FULL | FCT_FOREX_DEAL | 31 | 2697 | 60 (0)| 00:00:01 | KEY | KEY |
|* 92 | INDEX UNIQUE SCAN | PK_D_PRODUCTSTATUS | 1 | | 0 (0)| | | |
|* 93 | TABLE ACCESS BY INDEX ROWID | DIM_PRODUCT_STATUS | 1 | 95 | 1 (0)| 00:00:01 | | |
| 94 | VIEW | index$_join$_020 | 12 | 1860 | 2 (0)| 00:00:01 | | |
|* 95 | HASH JOIN | | | | | | | |
| 96 | INDEX STORAGE FAST FULL SCAN | PK_D_TREASURYDEALSUBTYPE | 12 | 1860 | 1 (0)| 00:00:01 | | |
| 97 | INDEX STORAGE FAST FULL SCAN | UQ_D_TREASURYDEALSUBTYPE_1 | 12 | 1860 | 1 (0)| 00:00:01 | | |
| 98 | NESTED LOOPS SEMI | | 6 | 12564 | 30 (0)| 00:00:01 | | |
|* 99 | TABLE ACCESS STORAGE FULL FIRST ROWS| TAG_DETAIL | 7 | 14105 | 23 (0)| 00:00:01 | | |
|*100 | TABLE ACCESS BY INDEX ROWID | TAG_MASTER | 4 | 316 | 1 (0)| 00:00:01 | | |
|*101 | INDEX UNIQUE SCAN | SYS_C0093627 | 1 | | 0 (0)| | | |
|*102 | INDEX UNIQUE SCAN | PK_DIMCURRENCY | 1 | | 0 (0)| | | |
|*103 | TABLE ACCESS BY INDEX ROWID | DIM_CURRENCY | 1 | 95 | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("A"."PERIOD_ID","B"."PERIOD_ID")=20230301)
3 - access("A"."CURRENCY_SEQ_NO"="B"."CURRENCY_SEQ_NO" AND TRIM("A"."CURRENCY_BKEY")=TRIM("B"."CURRENCY_BKEY") AND
"A"."PERIOD_ID"="B"."PERIOD_ID")
6 - access("DIMENSION_BKEY"=TRIM("CHART_OF_ACCOUNT_BKEY"))
7 - access(TRIM("MIS_CD2_F43_CODE")=TRIM("F"."MIS_CODE_2"))
12 - access("F"."CHART_OF_ACCOUNT_ID"="CHART_OF_ACCOUNT_ID")
14 - filter(NVL("F"."BAL",0)*"FCR"."MID_RATE"<>0)
16 - access("F"."CURRENCY_ID"="CURRENCY_ID")
18 - storage(("CURRENCY_BKEY"='AUD' OR "CURRENCY_BKEY"='CAD' OR "CURRENCY_BKEY"='CHF' OR "CURRENCY_BKEY"='CNY' OR
"CURRENCY_BKEY"='EUR' OR "CURRENCY_BKEY"='GBP' OR "CURRENCY_BKEY"='HKD' OR "CURRENCY_BKEY"='INR' OR "CURRENCY_BKEY"='JPY' OR
"CURRENCY_BKEY"='SGD' OR "CURRENCY_BKEY"='USD' OR "CURRENCY_BKEY"='ZAR'))
filter(("CURRENCY_BKEY"='AUD' OR "CURRENCY_BKEY"='CAD' OR "CURRENCY_BKEY"='CHF' OR "CURRENCY_BKEY"='CNY' OR
"CURRENCY_BKEY"='EUR' OR "CURRENCY_BKEY"='GBP' OR "CURRENCY_BKEY"='HKD' OR "CURRENCY_BKEY"='INR' OR "CURRENCY_BKEY"='JPY' OR
"CURRENCY_BKEY"='SGD' OR "CURRENCY_BKEY"='USD' OR "CURRENCY_BKEY"='ZAR'))
21 - storage(("F"."GL_IND"='L' AND SYS_OP_BLOOM_FILTER(:BF0000,"F"."CURRENCY_ID")))
filter(("F"."GL_IND"='L' AND SYS_OP_BLOOM_FILTER(:BF0000,"F"."CURRENCY_ID")))
26 - access("CURRENCY_ID"="F"."CURRENCY_ID")
27 - access("PERIOD_ID"="F"."PERIOD_ID" AND "UNIFIED_CURRENCY_CODE"=)
29 - storage(NVL("IS_RCY",'N')='Y')
filter(NVL("IS_RCY",'N')='Y')
31 - access("PERIOD_ID"="F"."PERIOD_ID" AND "UNIFIED_CURRENCY_CODE"="CURRENCY_UCID")
filter("PERIOD_ID"="PERIOD_ID")
35 - access("FMCR"."PERIOD_ID"="F"."PERIOD_ID" AND "FMCR"."OBJECT_CUSTOMER_ID"="F"."CUSTOMER_ID")
filter("FMCR"."OBJECT_CUSTOMER_ID"="F"."CUSTOMER_ID")
36 - access("FMCR"."SUBJECT_CUSTOMER_ID"="CUSTOMER_ID")
37 - filter("CUSTOMER_BKEY"='999999')
39 - access(ROWID=ROWID)
43 - access("F"."CUSTOMER_ID"="CUSTOMER_ID")
47 - filter("BASE_DIMENSION_ID"=100001)
48 - access("MIS_CODE_ID"="MIS_CODE_1_ID")
49 - filter("MIS_CODE_1_ID"=(-1))
51 - access("MIS_CODE_ID"=(-1))
53 - access("F"."COUNTER_PARTY_ID"="CUSTOMER_ID")
57 - filter("BASE_DIMENSION_ID"=100001)
58 - access("MIS_CODE_ID"="MIS_CODE_1_ID")
59 - filter("MIS_CODE_1_ID"=(-1))
61 - access("MIS_CODE_ID"=(-1))
66 - access("BASE_DIMENSION_ID"=100021)
68 - access("MIS_CODE_ID"=(-1))
70 - access("TAG_ID"="TAG_ID")
72 - storage("TAG_NAME"='TAG_LIQUID_BALANCES_NOSTRO_ADD')
filter("TAG_NAME"='TAG_LIQUID_BALANCES_NOSTRO_ADD')
74 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"TAG_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"TAG_ID"))
81 - filter( IS NOT NULL)
82 - access("TRSRY_DEAL_SUB_TYPE_ID"="DEAL_SUB_TYPE_ID")
87 - storage("CURRENCY_BKEY"='MUR')
filter("CURRENCY_BKEY"='MUR')
91 - storage(("PERIOD_ID"="PERIOD_ID" AND "PURCHASE_CURRENCY_ID"="CURRENCY_ID" AND
"PERIOD_ID"<TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("VALUE_DATE"),'YYYYMMDD')) AND "PURCHASE_CURRENCY_ID" IS NOT NULL))
filter(("PERIOD_ID"="PERIOD_ID" AND "PURCHASE_CURRENCY_ID"="CURRENCY_ID" AND
"PERIOD_ID"<TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("VALUE_DATE"),'YYYYMMDD')) AND "PURCHASE_CURRENCY_ID" IS NOT NULL))
92 - access("DEAL_STATUS_ID"="PRODUCT_STATUS_ID")
93 - filter(CASE WHEN "PRODUCT_STATUS_BKEY"='APPROVED' THEN 'Y' WHEN "PRODUCT_STATUS_BKEY"='REVERSED' THEN 'N' END ='Y')
95 - access(ROWID=ROWID)
99 - filter("DIMENSION_BKEY"=TRIM(:B1))
100 - filter("TAG_NAME"='TAG_LIQUID_BALANCES_NOSTRO_MINUS')
101 - access("TAG_ID"="TAG_ID")
102 - access("ITEM_2"="CURRENCY_ID")
103 - filter("CURRENCY_BKEY"="ITEM_1")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (3))
---------------------------------------------------------------------------
2 - SEL$2 / A@SEL$2
U - USE_HASH(A,B)
4 - SEL$9E145C0F / F@SEL$3
U - USE_HASH(F,DCA)
75 - SEL$5D61510E / F@SEL$44
U - USE_HASH(F,TR)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
SELECT f.CURRENCY_ID AS CURRENCY_ID,SUM(f.NOSTRO_BALANCE) AS NOSTRO_BALANCE0,SUM(f.SHORT_TERM_LIABILITIES_BAL) AS SHORT_TERM_LIABILITIES_B1 FROM fintellix_banking_regrep.RC_FM0600_NOSTRO_BALANCE f WHERE f.period_id = 20230301 GROUP BY f.CURRENCY_ID
Regards,
Roshan