Please help me in tuning the below query
I have also attached the Explain paln and Statistics
SELECT CA.CA_ID_CONTR_ATTEST CA_CONTR_ATTEST FROM CDL_MA_ASSEGN_CONTR AC, CDL_MA_CONTROLLI CO, CDL_TR_CONTR_ATTEST CA
WHERE CA.CA_ID_CONTR_ASSEGN = AC.AC_ID_ASSEGN_CONTR AND AC.AC_ID_CONTROLLO = CO.CO_ID_CONTROLLO
AND AC.AC_CDR IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST(1514 876 ) ) )
AND AC.AC_FINE_VALID IS NULL
AND CO.CO_TYPE IN (SELECT COLUMN_VALUE FROM TABLE (cdl_pa_internal_interface.CDL_FN_GET_IN_LIST( '10012 ,10010,10469,10011,10013') ) )
AND ( CA.CA_ULTIMA_DATA_ATTEST = Trunc(TO_DATE('28/02/2007','DD/MM/YYYY')) )
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1131 Card=1 Bytes=
54)
1 0 HASH JOIN (SEMI) (Cost=1131 Card=1 Bytes=54)
2 1 NESTED LOOPS (Cost=1028 Card=1 Bytes=52)
3 2 HASH JOIN (SEMI) (Cost=1027 Card=1 Bytes=42)
4 3 NESTED LOOPS (Cost=924 Card=911 Bytes=36440)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_TR_CONTR_ATT
EST' (TABLE) (Cost=11 Card=911 Bytes=16398)
6 5 INDEX (RANGE SCAN) OF 'CDL_CA_ULTIMA_DATA_IN_100
3' (INDEX) (Cost=3 Card=911)
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_MA_ASSEGN_CO
NTR' (TABLE) (Cost=1 Card=1 Bytes=22)
8 7 INDEX (UNIQUE SCAN) OF 'CDL_AC_ID_ASSEGN_CONTR_P
K_1001' (INDEX (UNIQUE)) (Cost=0 Card=1)
9 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'CDL_FN_GET_I
N_LIST' (PROCEDURE)
10 2 TABLE ACCESS (BY INDEX ROWID) OF 'CDL_MA_CONTROLLI' (T
ABLE) (Cost=1 Card=1 Bytes=10)
11 10 INDEX (UNIQUE SCAN) OF 'CDL_CO_ID_CONTROLLO_PK_1001'
(INDEX (UNIQUE)) (Cost=0 Card=1)
12 1 COLLECTION ITERATOR (PICKLER FETCH) OF 'CDL_FN_GET_IN_LI
ST' (PROCEDURE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40938 consistent gets
0 physical reads
0 redo size
409 bytes sent via SQL*Net to client
283 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
15 rows processed