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

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