 | | | SQL sharing | SQL sharing 2006-06-30 - By ramick
Back
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 (See http://ora-600.ora-code.com) [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?
TIA
--
http://www.freelists.org/webpage/oracle-l
|
|
|