[Oracle-l] Bind Variable - Query slow

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

Cheers,

Phil

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.

Thanks,
Finn


-----Original Message-----
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

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

Cheers,

Phil

--
http://www.freelists.org/webpage/oracle-l

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

Does the column referenced by the bind variable have a histogram on it? If
so, is the value a popular one or not? Have you experimented w/o a
histogram?
I would troubleshoot the root cause, but one option is to use a SQL plan
baseline to give the plan from the literal version to the bind version.
https://blogs.oracle.com/optimizer/entry/how_do_i_deal_with_a_third_party_application_that_has_embedded_hints_that_result_in_a_sub-optimal_ex

I deleted the histogram and it worked. So i went ahead and deleted the
statistics, gathered stats , checked the histogram / buckets; deleted the
histogram for the columns of the particular problematic table , verified
and it is now working fine. Of interest is the Metalink article :
1031826.6 ; The concepts remain the same. Don't worry about the version. I
wish i had seen it earlier.

Hi Greg
Oracle's behaviour seems a bit strange. Contrary to metalink entry
method_opt 254 buckets does get decent response though not the best.
We have more issues to iron out. But at least we seem to have hold of
one issue :-(