Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> SQL sharing

SQL sharing

From: ramick <>
Date: Fri, 30 Jun 2006 09:20:14 -0700
Message-Id: <>

2 node RAC 9205 on RH 2.4.9-e.57

I have a number of SQL statements that each use a large amount of shared memory and have high version counts. Taking one as an example, it is a very small SQL statement that is using ~85M of sharable memory with 784 versions. This is a select statement against a single table with no partitioning and it uses a single bind variable against a PK indexed column.

Using the address of the parent to look up the reason(s) why it's not shared with existing child cursors in the view v$sql_shared_cursor shows all columns for this parent are N.

The SQL is using a bind variable per the developer. I checked for invalidations in v$sqlarea and there aren't any for this statement. There also have been no truncates on the table in the statement. This SQL is run from separate sessions, but always in the same schema.

We use cursor_sharing=similar instance-wide in an attempt to combat our problem of non-bind SQL. We cannot use cursor_sharing=exact due to apps not using binds causing LC latch contention and we cannot use cursor_sharing=force because this will cause execution plans to be a problem and leads to ora-600 [kkslhsh1] fairly quickly. See also, Oracle Note:261020.1 - High Version Count with CURSOR_SHARING = SIMILAR or FORCE.

We are using histograms on the table and field in the where clause. There are 201 buckets in dba_histograms for this owner.table.column. Is there any way to determine if Oracle is converting incoming literals to binds for this statement?
Even if it is, there should be a max of 201 children, right?

I know if the bind variable length changes, there will be a number of children, but I don't know the thresholds at which this occurs. Would someone in the know please post these or post a way to find out?

The bind variable used in this case should always be the same size and looks like it is from the view v$sql_bind_metadata for the children, but I want to make sure this is not the reason for the children not being shared - this is a PHP app.

Is there a way I can determine if this is a result of bind peeking?

I have looked in numerous places (Oracle documentation, MetaLink, Google, some of oracle-l, etc.), but cannot determine why there are so many children for this statement.

Other than those listed in v$sql_shared_cursor, what are reasons why a SQL cursor is not shared?

I have asked the developer to set cursor_sharing=exact in the sessions that use this SQL; this seems to have cleared this particular problem, but we have this problem in other places as well - too many to just say "add this to every place that that occurs". Now, I'm wondering if I'm hitting Oracle Bug 3406977 High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE. This bug alludes to "binds marked as non-data". Is there any way I can determine if the binds are being marked as non-data?


Received on Fri Jun 30 2006 - 11:20:14 CDT

Original text of this message