Bind Variable - Query slow
From: GovindanK <gkatteri_at_gmail.com>
Date: Thu, 22 Mar 2012 22:53:15 -0700
Message-ID: <CAPb52X4OmWrBkQTbdCfLmFEJM4YWZAZ_nyK9NVfL5EKys62VKQ_at_mail.gmail.com>
Linux RAC ,11.1.0.7
Query runs fast with hard coding / SQL*Plus '&' ; But when run through the application using bind variables , the plan is different and it goes for MERGE CARTESIAN JOIN. Metalink , google , underscore parameters are not of much help. Tried different bucket sizes with statistics ; Tried rewriting the query; Marginal improvement ; If you hard code or with SQL*Plus '&' the query runs in 1 second .. but with bind variable it takes anywhere from 3 to 10 minutes depending on the input values. With hard code , it does not do MCJ but plan is different for few tables. I don't say the query is written the best way.
Date: Thu, 22 Mar 2012 22:53:15 -0700
Message-ID: <CAPb52X4OmWrBkQTbdCfLmFEJM4YWZAZ_nyK9NVfL5EKys62VKQ_at_mail.gmail.com>
Linux RAC ,11.1.0.7
Query runs fast with hard coding / SQL*Plus '&' ; But when run through the application using bind variables , the plan is different and it goes for MERGE CARTESIAN JOIN. Metalink , google , underscore parameters are not of much help. Tried different bucket sizes with statistics ; Tried rewriting the query; Marginal improvement ; If you hard code or with SQL*Plus '&' the query runs in 1 second .. but with bind variable it takes anywhere from 3 to 10 minutes depending on the input values. With hard code , it does not do MCJ but plan is different for few tables. I don't say the query is written the best way.
Any input would be appreciated.
TIA
GovindanK
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 23 2012 - 00:53:15 CDT