tuning about in clause SQL
I have many similar SQL in SGA.
SELECT re.EQP_KEY
FROM RFI_EQP re
WHERE re.LAST_REC_TXN_TYPE_CODE IN ('I', 'U')
AND re.EQP_STS_CODE = 'A'
AND re.RFID_HSE_EST_KEY IN (:1a,:1b,:1c....)
AND re.SRVC_TYPE_KEY IN (:2, )
AND re.RFID_HSE_BLK_KEY IN
(SELECT rhbc.RFID_HSE_BLK_KEY
FROM REF_RFID_HSE_BLK_CNTR rhbc
WHERE rhbc.LAST_REC_TXN_TYPE_CODE IN ('I', 'U')
AND rhbc.CNTR_KEY IN (:4, :5))
The content (:1a,:1b,:1c....) is various as it depends on the input of application interface.
I found that Oracle treat them as different sql and generates many Executing Plan for them. Some plan is really BAD...