SQL Performance (MOSC)

MOSC Banner

tuning about in clause SQL

edited Sep 1, 2016 9:37AM in SQL Performance (MOSC) 8 commentsAnswered

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...

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center