RE: Unshared cursors redux
- From: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- To: <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 23 Feb 2007 11:42:46 +0200
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
----------
SQL_TEXT
------------------------------------------------------------------------
--------
1007845333
select /*+ bind */ * from dual where 1=:b1
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@xxxxxxxxxxxxxxxxxxxxx]
Sent: Thursday, February 22, 2007 7:02 PM
To: Yasin Baskan
Cc: oracle-l@xxxxxxxxxxxxx
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,
sequence_id, metric_value, instance_name from wri$_alert_outstanding
where
internal_instance_number > -2
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.
>
> Jonathan Lewis has a post about this,
> http://jonathanlewis.wordpress.com/2007/01/05/bind-variables
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
