RE: Unshared cursors redux
- From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
- To: "Yasin Baskan" <yasin.baskan@xxxxxxxxxxxxxxxx>
- Date: Thu, 22 Feb 2007 11:02:01 -0600 (CST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
