I have below oracle query. It is taking very long time to run. Can you please suggest some performance tuning for this query.
select ss.registration_id as REGISTRATION_ID,
ss.batch_id as BATCH_ID
from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss,
(select a.exceptn_criteria_val,
a.exceptn_criteria_rtrn_val
from EXCEPTN_CRITERIA a,
EXCEPTN_EXPRESSION b
where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
and b.IS_CURRENT_INDCTR = 1
and a.IS_CURRENT_INDCTR = 1) sub_query
where ss.REVENUE_CD = sub_query.exceptn_criteria_val
and ss.batch_id = 821370
and exists (select 'x'
from submtd_srvc PARTITION(SUBMTD_SRVC_821370) ss2,
(select a.exceptn_criteria_val,
a.exceptn_criteria_rtrn_val
from EXCEPTN_CRITERIA a,
EXCEPTN_EXPRESSION b
where a.EXCEPTN_EXPRESSION_ID = b.EXCEPTN_EXPRESSION_ID
and b.EXCEPTN_EXPRESSION_NAME = 'NC_CUSTOM_REV_CAT'
and b.IS_CURRENT_INDCTR = 1
and a.IS_CURRENT_INDCTR = 1) sub_query2
where ss2.REVENUE_CD = sub_query2.exceptn_criteria_val
and ss2.registration_id = ss.registration_id
and ss2.batch_id = ss.batch_id
and ss2.batch_id = 821370
and sub_query2.exceptn_criteria_rtrn_val <> sub_query.exceptn_criteria_rtrn_val)
Order By Ss.Registration_Id,
ss.batch_id;