Agree on the datatype theory. Find the sql id in the shared pool and then use
select * from table(dbms_xplan.display_cursor('&sql_id',nvl('&child_cursor',0)));
Look at the notes section. If there's something about internal functions or other things that look suspicious you have a bind var data type issue. E.g. ODBC makes bind vars chars by default so comparing to a number column makes oracle execute a function on the column eliminating the possibility of using an index. The developers would have to declare the ODBC var as a number to fix that.
From: [email protected]
On Behalf Of Phillip Jones
Sent: Friday, March 23, 2012 5:19 AM
To: [email protected]
Cc: Oracle-L Freelists
Subject: Re: Bind Variable - Query slow
I've seen this happen before - the culprit was hibernate/JDBC passing in a value as a an NVARCHAR2 instead of a VARCHAR2 - I had to create a functional index (TO_NCHAR) as a workaround.
Have a look in dba_hist_sql_bind_metadata & check what type Oracle thinks the bind variable is.
On Fri, Mar 23, 2012 at 5:53 AM, GovindanK wrote:
Linux RAC ,220.127.116.11
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.
This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. -IP2