| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unshared cursors redux
BIND_MISMATCH is set to Y when you use different datatypes for the same bind variable. If you use a number and then a varchar2 for the same bind variable you will have two versions with a difference in BIND_MISMATCH. Test case below.
It is not set to Y when you provide different length values for a bind variable. This also produces versions of the same statement when you cross boundaries, but you will not see any difference in BIND_MISMATCH.
SQL> var b1 varchar2(1);
SQL> exec :b1 := '1';
PL/SQL procedure successfully completed.
SQL> select /*+ bind */ * from dual where 1=:b1;
D
-
X
SQL> select hash_value,sql_text from v$sql where upper(sql_text) like '% BIND %'; HASH_VALUE
3429269945
select hash_value,sql_text from v$sql where upper(sql_text) like '% BIND
%'
SQL> select bind_mismatch from v$sql_shared_cursor where kglhdpar=(select distinct address from v$sql where hash_value=1007845333);
B
-
N
SQL> var b1 number
SQL> exec :b1 := 1;
PL/SQL procedure successfully completed.
SQL> select /*+ bind */ * from dual where 1=:b1;
D
-
X
SQL> select bind_mismatch from v$sql_shared_cursor where kglhdpar=(select distinct address from v$sql where hash_value=1007845333);
B
-
N
Y
-----Original Message-----
From: Rich Jesse [mailto:rjoralist_at_society.servebeer.com]
Sent: Thursday, February 22, 2007 7:02 PM
To: Yasin Baskan
Cc: oracle-l_at_freelists.org
Subject: RE: Unshared cursors redux
Excellent article! I should have known Jonathan would have had
something
like this. I was about to attempt to use his test case to reproduce the
symptoms I'm seeing, but upon further investigation, I have at least a
few
statements where this does not appear to be the case.
If I understand it correctly, Jonathan's article is saying that one of
the
side effects of bind variable sizing that crosses the 4 different
allocation
sizes in different executions is that it causes the cursor to not be
shared
because of the memory allocation differences. So, based on that, I
would
expect that the absence of explicit binds, along with NOT using the
dreaded
CURSOR_SHARING=FORCE|SIMILAR init.ora parameter, in a cursor would
cause
that cursor to either be shared or to have a reason in
V$SQL_SHARED_CURSOR
as to why it would not be shared. But binds for different executions
that
cross allocation sizes would seem to be the definition for the
"BIND_MISMATCH" column of V$SQL_SHARED_CURSOR, wouldn't it?
In any case, to see multiple children of statements not using explicit binds, I reran my original query after adding a filter of:
AND vsa.sql_text NOT LIKE '%:%'
It returns less rows, but still more than I'd like, including this one, which currently has 5 versions:
select reason_id, object_id, subobject_id, internal_instance_number, time_suggested, context, reason_argument_1, reason_argument_2,
reason_argument_3, reason_argument_4, reason_argument_5, action_argument_1, action_argument_2, action_argument_3, action_argument_4, action_argument_5,
So, I'm thinking...I'm not sure what I'm thinking. It's about a week
from
go-live and I'm struggling to wrap my brain around this one. It's
probably
not a big deal, but I'd rather be sure it's not. :)
Thoughts?
Thanks!!!
Rich
> We have several sql statements having tens of versions and
> v$sql_shared_cursor does not show any difference between versions.
>
> This can be related to the sizes of the values for your bind
variables.
> For example, if you have a varchar2(128) column and you provide a 10
> character input for it and then you run the same sql with a 100
> character input you get two versions of the same statement. Support
says
> the solution for this is to first run the sql with the largest input
> values possible.
>
Bu mesaj ve onunla iletilen tum ekler gonderildigi kisi ya da kuruma ozel ve Bankalar Kanunu geregince, gizlilik yukumlulugu tasiyor olabilir. Bu mesaj, hicbir sekilde, herhangi bir amac icin cogaltilamaz, yayinlanamaz ve para karsiligi satilamaz; mesajin yetkili alicisi veya alicisina iletmekten sorumlu kisi degilseniz, mesaj icerigini ya da eklerini kopyalamayiniz, yayinlamayiniz, baska kisilere yonlendirmeyiniz ve mesaji gonderen kisiyi derhal uyararak bu mesaji siliniz. Bu mesajin iceriginde ya da eklerinde yer alan bilgilerin dogrulugu, butunlugu ve guncelligi Bankamiz tarafindan garanti edilmemektedir ve bilinen viruslere karsi kontrolleri yapilmis olarak yollanan mesajin sisteminizde yaratabilecegi zararlardan Bankamiz sorumlu tutulamaz.
This message and the files attached to it are under the privacy liability in accordance with the Banking Law and confidential to the use of the individual or entity to whom they are addressed. This message cannot be copied, disclosed or sold monetary consideration for any purpose. If you are not the intended recipient of this message, you should not copy, distribute, disclose or forward the information that exists in the content and in the attachments of this message; please notify the sender immediately and delete all copies of this message. Our Bank does not warrant the accuracy, integrity and currency of the information transmitted with this message. This message has been detected for all known computer viruses thence our Bank is not liable for the occurrence of any system corruption caused by this message
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 23 2007 - 03:42:46 CST
 |
 |