My single query with 3 inputs runs faster in TOAD but since the same input is used multiple times in query, Java is treating it as 8 inputs. This is leading to 2 different explain plans. The query runs in 1 sec in TOAD while from Java it takes about 3 min.
Oracle version - Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
from Java
SQL_ID cqjgrqd3up0bc, child number 0
-------------------------------------
WITH BASE AS --TestVinod
(SELECT DISTINCT 0
AS MARK, PF.PF_ID,
PF.PF_NAME, PF.CP_ID,
SC.SC_ID, TO_DATE ( :1 , 'dd.mm.yyyy') AS
CALC_DATE, PF.PF_ISS_DATE AS
PF_ISS_DATE, PF.CREATED_DATE,
PF.CREATED_BY FROM V_INT_PORTFOLIO_WKF PF,
V_INT_SHARECLASS SC WHERE SC.CP_ID = PF.CP_ID
AND SC.PF_ID = PF.PF_ID AND
SC.REF_DATE = (SELECT MAX (REF_DATE)
FROM INT_SHARECLASS
WHERE SC_ID = SC.SC_ID AND
PF_ID = PF.PF_ID AND REF_DATE <=
Plan hash value: 2128809778
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | NESTED LOOPS | | 1 | | | |
| 3 | VIEW | | 1 | | | |
| 4 | HASH UNIQUE | | 1 | 1584K| 1584K| |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 1579K| 1579K| |
|* 7 | FILTER | | | | | |
|* 8 | FILTER | | | | | |
|* 9 | HASH JOIN OUTER | | 9 | 1640K| 1640K| |
|* 10 | HASH JOIN | | 8 | 19M| 6260K| |
|* 11 | INDEX STORAGE FAST FULL SCAN | INT_SHARECLASS_UK02 | 191K| 1028K| 1028K| |
|* 12 | HASH JOIN | | 107K| 11M| 4693K| |
|* 13 | HASH JOIN | | 48901 | 2559K| 2559K| 3226K (0)|
| 14 | JOIN FILTER CREATE | :BF0000 | 10 | | | |
|* 15 | MAT_VIEW ACCESS STORAGE FULL | M_ADS_MADT_CP | 10 | | | |
| 16 | JOIN FILTER USE | :BF0000 | 146K| | | |
|* 17 | TABLE ACCESS STORAGE FULL | INT_PORTFOLIO | 146K| | | |
| 18 | INDEX STORAGE FAST FULL SCAN | INT_SHARECLASS_UK02 | 191K| 1028K| 1028K| |
| 19 | INDEX FULL SCAN | ADS_MADT_CP_UK01 | 38 | 1028K| 1028K| |
| 20 | NESTED LOOPS | | 1 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | SFDR_CTR_PORTFOLIO | 1 | | | |
|* 22 | INDEX UNIQUE SCAN | IND_SFDR_CTR_PORTFOLIO_UK | 1 | 1028K| 1028K| |
|* 23 | TABLE ACCESS BY INDEX ROWID BATCHED| SFDR_CTR_SHARECLASS | 1 | | | |
|* 24 | INDEX RANGE SCAN | SFDR_CALC_SHARECLASS_FK01 | 1 | 1028K| 1028K| |
| 25 | SORT AGGREGATE | | 1 | | | |
|* 26 | INDEX STORAGE FAST FULL SCAN | INT_PORTFOLIO_UK01 | 1 | 1028K| 1028K| |
|* 27 | INDEX UNIQUE SCAN | INT_REP_CONFIG_UK01 | 1 | 1028K| 1028K| |
| 28 | SORT GROUP BY NOSORT | | 1 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | INT_REP_CONFIG | 1 | | | |
|* 30 | INDEX RANGE SCAN | IND_INT_REP_CONFIG_02 | 1 | 1028K| 1028K| |
|* 31 | TABLE ACCESS BY INDEX ROWID | INT_REP_CONFIG | 1 | | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("REF_DATE"=MAX("REF_DATE"))
7 - filter(( IS NULL AND "REF_DATE"=))
8 - filter((:5='ALL' OR "MAP_CP"."M_ID"=:6))
9 - access("MAP_CP"."CP_GUID"="CP"."CP_GUID")
10 - access("SC_ID"="SC_ID" AND "PF_ID"="PF_ID" AND "CP_ID"="CP_ID")
11 - storage("REF_DATE"<=TO_DATE(:2,'dd.mm.yyyy'))
filter("REF_DATE"<=TO_DATE(:2,'dd.mm.yyyy'))
12 - access("CP_ID"="CP_ID" AND "PF_ID"="PF_ID")
13 - access("CP_ID"="CP"."CP_ID")
15 - storage(("CP"."LOGINNAME"=:4 AND "CP"."MO_ID"='SFDR'))
filter(("CP"."LOGINNAME"=:4 AND "CP"."MO_ID"='SFDR'))
17 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"CP_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"CP_ID"))
22 - access("P_CTR"."CP_ID"=TO_NUMBER(:B1) AND "P_CTR"."PF_ID"=:B2 AND
"P_CTR"."REPORT_DATE"=TO_DATE(:7,'dd.mm.yyyy') AND "P_CTR"."WORKFLOW_UNIT"='SFDR')
23 - filter("S_CTR"."SC_ID"=:B1)
24 - access("S_CTR"."SFDR_CALC_PF_GUID"="P_CTR"."SFDR_CALC_PF_GUID")
26 - filter(("PF_ID"=:B1 AND "CP_ID"=:B2 AND "REF_DATE"<=TO_DATE(:3,'dd.mm.yyyy')))
27 - access("IRC"."REF_DATE"= AND "IRC"."CP_ID"= AND "IRC"."PF_ID"= AND "IRC"."SC_ID"=)
29 - filter("X"."SC_ID"=:B1)
30 - access("X"."PF_ID"=:B1 AND "X"."CP_ID"=:B2 AND "X"."REF_DATE"<=TO_DATE(:8,'dd.mm.yyyy'))
31 - filter(CASE "REPORT_SFDR" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_ART_6" WHEN 'Y' THEN 1 ELSE 0
END +CASE "REPORT_SFDR_ART_8" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_ART_9" WHEN 'Y' THEN 1 ELSE 0
END +CASE "WEB_DISCLOSURE" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_PREC" WHEN 'Y' THEN 1 ELSE 0 END
+CASE "REPORT_SFDR_PERIOD" WHEN 'Y' THEN 1 ELSE 0 END +CASE "RUN_EET" WHEN 'Y' THEN 1 ELSE 0 END +CASE
"REPORT_SFDR_PERIOD" WHEN 'Y' THEN 1 ELSE 0 END +CASE "RUN_EET" WHEN 'Y' THEN 1 ELSE 0 END +CASE
"PORTFOLIO_IN_ENTITY_PAI_REPORT" WHEN 'Y' THEN 1 ELSE 0 END >0)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID cqjgrqd3up0bc, child number 1
WITH BASE AS --TestVinod
(SELECT DISTINCT 0
AS MARK, PF.PF_ID,
PF.PF_NAME, PF.CP_ID,
SC.SC_ID, TO_DATE ( :1 , 'dd.mm.yyyy') AS
CALC_DATE, PF.PF_ISS_DATE AS
PF_ISS_DATE, PF.CREATED_DATE,
PF.CREATED_BY FROM V_INT_PORTFOLIO_WKF PF,
V_INT_SHARECLASS SC WHERE SC.CP_ID = PF.CP_ID
AND SC.PF_ID = PF.PF_ID AND
SC.REF_DATE = (SELECT MAX (REF_DATE)
FROM INT_SHARECLASS
WHERE SC_ID = SC.SC_ID AND
PF_ID = PF.PF_ID AND REF_DATE <=
From TOAD
SQL_ID 54sqtkvvt1fmc, child number 0
-------------------------------------
WITH BASE AS --TestVinod
(SELECT DISTINCT 0
AS MARK, PF.PF_ID,
PF.PF_NAME, PF.CP_ID,
SC.SC_ID, TO_DATE ( :REF_DATE, 'dd.mm.yyyy')
AS CALC_DATE, PF.PF_ISS_DATE
AS PF_ISS_DATE, PF.CREATED_DATE,
PF.CREATED_BY FROM V_INT_PORTFOLIO_WKF PF,
V_INT_SHARECLASS SC WHERE SC.CP_ID = PF.CP_ID
AND SC.PF_ID = PF.PF_ID AND
SC.REF_DATE = (SELECT MAX (REF_DATE)
FROM INT_SHARECLASS
WHERE SC_ID = SC.SC_ID AND
PF_ID = PF.PF_ID AND REF_DATE <=
Plan hash value: 3041033520
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 1 | | | |
| 2 | NESTED LOOPS | | 1 | | | |
| 3 | VIEW | | 1 | | | |
| 4 | HASH UNIQUE | | 1 | 1772K| 1772K| 4879K (0)|
|* 5 | FILTER | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | | | |
| 7 | NESTED LOOPS | | 1 | | | |
|* 8 | HASH JOIN | | 215 | 3560K| 2713K| 4828K (0)|
| 9 | JOIN FILTER CREATE | :BF0000 | 472 | | | |
|* 10 | HASH JOIN | | 472 | 2991K| 2991K| 3241K (0)|
| 11 | JOIN FILTER CREATE | :BF0001 | 2 | | | |
|* 12 | MAT_VIEW ACCESS STORAGE FULL | M_ADS_MADT_CP | 2 | | | |
|* 13 | HASH JOIN | | 7313 | 3621K| 3621K| 4787K (0)|
| 14 | JOIN FILTER CREATE | :BF0002 | 7313 | | | |
| 15 | VIEW | VW_SQ_2 | 7313 | | | |
| 16 | HASH GROUP BY | | 7313 | 3518K| 3127K| 5310K (0)|
|* 17 | INDEX RANGE SCAN | INT_PORTFOLIO_UK01 | 7313 | 1028K| 1028K| |
| 18 | JOIN FILTER USE | :BF0001 | 146K| | | |
| 19 | JOIN FILTER USE | :BF0002 | 146K| | | |
|* 20 | TABLE ACCESS STORAGE FULL | INT_PORTFOLIO | 146K| | | |
| 21 | VIEW | VW_SQ_1 | 9598 | | | |
| 22 | HASH GROUP BY | | 9598 | 4048K| 2816K| 5818K (0)|
| 23 | JOIN FILTER USE | :BF0000 | 9598 | | | |
|* 24 | INDEX STORAGE FAST FULL SCAN | INT_SHARECLASS_UK02 | 9598 | 1028K| 1028K| |
|* 25 | INDEX UNIQUE SCAN | INT_SHARECLASS_UK02 | 1 | 1028K| 1028K| |
| 26 | NESTED LOOPS | | 1 | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | SFDR_CTR_PORTFOLIO | 1 | | | |
|* 28 | INDEX UNIQUE SCAN | IND_SFDR_CTR_PORTFOLIO_UK | 1 | 1028K| 1028K| |
|* 29 | TABLE ACCESS BY INDEX ROWID BATCHED| SFDR_CTR_SHARECLASS | 1 | | | |
|* 30 | INDEX RANGE SCAN | SFDR_CALC_SHARECLASS_FK01 | 1 | 1028K| 1028K| |
|* 31 | INDEX RANGE SCAN | ADS_MADT_CP_UK01 | 1 | 1028K| 1028K| |
|* 32 | INDEX UNIQUE SCAN | INT_REP_CONFIG_UK01 | 1 | 1028K| 1028K| |
| 33 | SORT GROUP BY NOSORT | | 1 | | | |
|* 34 | INDEX RANGE SCAN | INT_REP_CONFIG_PK01 | 1 | 1028K| 1028K| |
|* 35 | TABLE ACCESS BY INDEX ROWID | INT_REP_CONFIG | 1 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter((:M_ID='ALL' OR "MAP_CP"."M_ID"=:M_ID))
8 - access("ITEM_2"="PF_ID")
10 - access("CP_ID"="CP"."CP_ID")
12 - storage(("CP"."LOGINNAME"=:USERNAME AND "CP"."MO_ID"='SFDR'))
filter(("CP"."LOGINNAME"=:USERNAME AND "CP"."MO_ID"='SFDR'))
13 - access("REF_DATE"="MAX(REF_DATE)" AND "ITEM_4"="PF_ID" AND "ITEM_5"="CP_ID")
17 - access("REF_DATE"<=TO_DATE(:REF_DATE,'dd.mm.yyyy'))
20 - storage(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"PF_ID"),SYS_OP_BLOOM_FILTER(:BF0001,"CP_I
D")))
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0002,"PF_ID"),SYS_OP_BLOOM_FILTER(:BF0001,"CP_ID
")))
24 - storage("REF_DATE"<=TO_DATE(:REF_DATE,'dd.mm.yyyy'))
filter("REF_DATE"<=TO_DATE(:REF_DATE,'dd.mm.yyyy'))
25 - access("ITEM_3"="CP_ID" AND "PF_ID"="PF_ID" AND "ITEM_1"="SC_ID" AND "REF_DATE"="MAX(REF_DATE)")
filter(("CP_ID"="CP_ID" AND IS NULL))
28 - access("P_CTR"."CP_ID"=TO_NUMBER(:B1) AND "P_CTR"."PF_ID"=:B2 AND
"P_CTR"."REPORT_DATE"=TO_DATE(:REF_DATE,'dd.mm.yyyy') AND "P_CTR"."WORKFLOW_UNIT"='SFDR')
29 - filter("S_CTR"."SC_ID"=:B1)
30 - access("S_CTR"."SFDR_CALC_PF_GUID"="P_CTR"."SFDR_CALC_PF_GUID")
31 - access("MAP_CP"."CP_GUID"="CP"."CP_GUID")
32 - access("IRC"."REF_DATE"= AND "IRC"."CP_ID"= AND "IRC"."PF_ID"= AND "IRC"."SC_ID"=)
34 - access("X"."CP_ID"=:B1 AND "X"."PF_ID"=:B2 AND "X"."SC_ID"=:B3 AND
"X"."REF_DATE"<=TO_DATE(:REF_DATE,'dd.mm.yyyy'))
filter(("X"."SC_ID"=:B1 AND "X"."PF_ID"=:B2))
35 - filter(CASE "REPORT_SFDR" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_ART_6" WHEN 'Y' THEN 1 ELSE 0
END +CASE "REPORT_SFDR_ART_8" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_ART_9" WHEN 'Y' THEN 1 ELSE 0 END
+CASE "WEB_DISCLOSURE" WHEN 'Y' THEN 1 ELSE 0 END +CASE "REPORT_SFDR_PREC" WHEN 'Y' THEN 1 ELSE 0 END +CASE
"REPORT_SFDR_PERIOD" WHEN 'Y' THEN 1 ELSE 0 END +CASE "RUN_EET" WHEN 'Y' THEN 1 ELSE 0 END +CASE
"REPORT_SFDR_PERIOD" WHEN 'Y' THEN 1 ELSE 0 END +CASE "RUN_EET" WHEN 'Y' THEN 1 ELSE 0 END +CASE
"PORTFOLIO_IN_ENTITY_PAI_REPORT" WHEN 'Y' THEN 1 ELSE 0 END >0)