根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting [ID 1169017.1]>在11g中将逐渐废弃cursor_sharing参数的SIMILAR选项,原因是在今后的版本中Exact和Force选项可以满足游标共享的需求了,使用SIMILAR选项可能引发额外的version_count过多或cursor pin s on X等待事件。

We recommend that customers discontinue setting cursor_sharing = SIMILAR due to the many problematic situations customers have experienced using it. The ability to set this will be removed in version 12 of the Oracle Database (the settings of EXACT and FORCE will remain available). Instead, we recommend the use of Adaptive Cursor Sharing in 11g. A number of customers have seen an increase in the number of child cursors since migrating to Oracle Database 11g Release 2. This can lead to many problems including complete CPU saturation of a machine requiring a database instance bounce or general database performance issues in the form of waits on mutexes and ‘library cache lock’.
From Oracle versions 9.0 through 11.1, an oracle instance would limit the number child cursors in the shared pool associated with a single parent to 1024 before it would mark the parent OBSOLETE effectively invalidating it and all the children. Then a new parent with one child would be created and used going forward. But this would only limit the degradation of performance for some environments rather than fix something that could be addressed more effectively through improved application coding. (The attempt to address this from the database side also introduced other issues like bug 5177766). The child limit was removed by Oracle development because it was only masking an application problem at the expense of database performance for better designed applications. In addition, the obsolete code would not work in cases when SQL was wrapped within PL/SQL. The fundamental problem that obsolete code was masking is application code that was written incorrectly with regards to the ability to be shared. For example,  it is not written with user binds or the literal characteristics differ to a high degree.
Therefore, setting cursor_sharing = SIMILAR is highly discouraged in Oracle Database 11g Release 2 (and generally has not been recommended for most environments even in earlier versions) for several reasons:
1) This parameter is generally overly restrictive in what it actually allows to be shared. SIMILAR tells oracle to try and share cursors by replacing all literals with binds for legacy applications, but directs that sharing only be performed when all the replaced literal values were exactly the same (in the case of predicates referencing columns with histograms or using inequality operators such as BETWEEN, <, and !=)
2) This parameter seems to bypass a lot of the improvements made with Oracle Database 11g’s Adaptive Cursor Sharing feature and other abilities in the Cost Based Optimizer code to make better decisions on what execution plans should and should not be shared.
3) Having many child cursors all associated with 1 parent cursor could perform much worse than having many parent cursors that would be seen with having the default setting of cursor_sharing = EXACT (or FORCE). The scenario of many thousands of child cursors associated with 1 parent results in a potential bottleneck for searches for matching cursors (soft parsing) within the library cache.
The cursor_sharing parameter was introduced as a workaround for legacy applications that could not scale because they had not yet been redesigned to use bind variables. It has been presumed that most applications have been redesigned since then. If you are still using such an application, our recommendation is to set cursor_sharing = FORCE. This setting maximizes cursor sharing while leveraging the Adaptive Cursor Sharing framework to generate multiple execution plans based on different literal value ranges if necessary.

What do you need to do?

Change the cursor_sharing to either FORCE or EXACT, keeping in mind the effects of either.

this is due to the setting in the init.ora:

Please set immediately
alter system set cursor_sharing=’FORCE’ scope=both;

to reduce the number of versions for a single sql statement whic is overloading your shared_pool.

377847.1 for SIMILA not FORCE are not the same

When bind variables are peeked. The parse engine makes a decision as to the ‘safety’ of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.

The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be ‘unsafe’ because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be ‘safe’ in terms of performance. If the bind is deemed ‘unsafe’ then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.


With CURSOR_SHARING=SIMILAR whenever the optimizer looks at a replaced bind value to make a decision then that bind is checked to see if it should be considered unsafe. The check made is :

Is the operator NEITHER of   ‘=’ or ‘!=’
Are there Column Histograms present on the column.

If either of these are true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:

Note:261020.1 High Version Count with CURSOR_SHARING = SIMILAR or FORCE

With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key

select … from orders where orderid=’literal’;

If  there is a histogram on “orderid” then the bind will likely get marked unsafe and a new child will be produced for every single value of ‘literal’. The SQL would only be shared if the value of ‘literal’ matches exactly to a value used already.

Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.

In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.


If CURSOR_SHARING = FORCE binds can still be “unsafe” (in terms of performance) if used by the optimizer in decisions but this should not then affect shareability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should stil lbe shared. e.g.: In the above example with orderid=’literal’, without histograms, the CBO does not need to look at ‘literal’ to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there is histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for whom different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.

Note that, prior to 11g, unsafe literals are NOT covered by ‘bind mismatch’ in V$SQL_SHARED_CURSOR  as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED  is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.

From the optimizer point of view, these additional plans for ‘unsafe’ bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.

Background Information

This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).





Note #1: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Note #2: Setting CURSOR_SHARING to FORCE causes an increase in the
maximum lengths (as returned by DESCRIBE) of any selected
expressions that contain literals (in a SELECT statement).
However, the actual length of the data returned will not change.

Cursor_sharing 相关的BUG 列表:


NB Bug Fixed Description
14456124 Predicate push may not occur with cursor sharing
14053457, ORA-917 parsing SQL with indicator binds with CURSOR_SHARING
12723295, ORA-600 [qerixGetKey:optdesc] with function based index and CURSOR_SHARING
9877960, ORA-600 or similar using CURSOR_SHARING with HS connections
14087914 Wrong results from ExistsNode with CURSOR_SHARING
+ 13550185,,, Hang / SGA memory corruption / ORA-7445 [kglic0] when using multiple shared pool subpools
13023854, Long parse time / hang for SQL with nested CASE expressions with CURSOR_SHARING enabled
12862170 INSERT ALL fails with ORA-600[kkslhsh1] with CURSOR_SHARING enabled / High Version Count on HASH_MATCH_FAILED
12797420,, “library cache: mutex X” waits on DB instance handle with CURSOR_SHARING
12649442,,, ORA-7445 [kxscod] with CURSOR_SHARING=FORCE or SIMILAR
12596444,,, Cursor not shared with CURSOR_SHARING if SQL has a CASE expression or set operation (UNION)
12534597 Bind Peeking is disabled for remote queries
12374212,,, Assorted dump , internal errors, memory corruptions with cursor_sharing = force
12345980, high parse time with cursor_sharing=force when session_cached_cursors set
11858021, ORA-600 [kpoal8-1] using DG4DRDA with CURSOR_SHARING=force
11806961, ORA-600 [kkspsc0: basehd] using CURSOR_SHARING
11738259,,, ORA-600 [kghssgfr2] using CURSOR_SHARING=FORCE
11714159, ORA-917 occurs with CURSOR_SHARING even if patch:9877980 is applied – superseded
11076030, Wrong results for XDB when CURSOR_SHARING enabled
11069199,,, ORA-600 [kksObsoleteCursor:invalid stub] with CURSOR_SHARING = SIMILAR | FORCE if fix 10187168 present
11063191,,,, ORA-4031 with hint /*+ CURSOR_SHARING_EXACT */ – excessive “KKSSP^nn” memory
10126094,,, ORA-600 [kglLockOwnersListAppend-ovf] from literal replacement on SQL issued from PLSQL
10013170, ORA-600 [736] from literal replacement with a “WAIT n” clause
* 9877980,,, ORA-7445[kkslMarkLiteralBinds] / Assorted Errors on if cursor sharing is enabled – Affects RMAN
9877964, ORA-600 [19003] raised by LIKE :BIND in query
9680430, High version count with CURSOR_SHARING = FORCE due to CBO transformation
9548104, OERI [qcsfbdnp:1] instead of ORA-1788 with cursor sharing
9413962, Many child cursors / ORA-600 [opixrb-3.0] [2005] [ORA-02005] binding literal for remote RPI
9411496, ORA-979 on GROUP BY query with CURSOR_SHARING set
9362218, Literals replaced by binds when CURSOR_SHARING=EXACT
9348402 OERI [kks-hash-collision] can occur with CURSOR_SHARING=FORCE|SIMILAR
9223586, Problems with variable length NCHAR literals with cursor sharing
9031183, ORA-1722 with CURSOR_SHARING=SIMILAR and with NCHAR
9008904, Dump (audTransFro) with CURSOR_SHARING
8545377, ORA-1780 with CURSOR_SHARING on XML queries
8246445, Query rewrite not working for multi-MV rewrite with literal replacement
5751866 Wrong Results with CASE and CURSOR_SHARING
9767674 Dump [kkslmtl] using CURSOR_SHARING – superceded
8794693 Dump [kkscsmtl] using literal replacement (CURSOR_SHARING)
8491399 Adaptive Cursor Sharing does not match the correct cursor version for queries using CHAR datatype
8453245 Many child cursors with CURSOR_SHARING = FORCE
8264642 ORA-600 [kkexbindopn0] with CURSOR_SHARING = SIMILAR
7651092 ORA-1000 with Literal Replacement, EXECUTE IMMEDIATE and CURSOR_SHARING_EXACT hint (affects DBMS_STATS)
7516867,, Intermittent Wrong results from literal replacement with fix for bug 6163785
7272297,,, Memory corruption / OERI[17114] / OERI[17125] with literal replacement
7212120, Session cursor cache not used properly when CURSOR_SHARING=force/similar
6337716,, Wrong max column size for NULL strings with literal replacement
5757106,, OERI[15851] selecting aggregate of a constant with literal replacement
4071519,, GROUP BY query with CURSOR_SHARING fails with ORA-1802
3461251, V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING
7296258, Intermittent Wrong results from literal replacement and remote objects
6163785, Intermittent Wrong Results with dblink and cursor_sharing
8202234 Intermittent Wrong Results with dblink and cursor_sharing
5863277, ORA-1008 from SQL on second run when cursor_sharing=similar/force
5762750, ORA-907 when cursor_sharing is enabled
5476507, OERI[15868] / OERI[15160] can occur with cursor sharing
5364819, OERI[kkslpbp:1] when using literal replacement
5254759, ORA-12801/ORA-1008 occurs on a parallel query with bind variables
5177766, OERI[17059] with SESSION_CACHED_CURSORS
5155885, OERI[kkslgbv0] with CURSOR_SHARING=similar
+ 5146740, Wrong results with bind variables/CURSOR_SHARING
5082178, Bind peeking may occur when it should not
5055175, Dump [kkslpkp] using literal replacement with timezone literals – superceded
4867724, Literal replacement limits column names to 30 characters
4698156, ORA-12850 querying GV$ views when CURSOR_SHARING=FORCE
4607460,, Dump [opipls] when CURSOR_SHARING = SIMILAR | FORCE
4513695, Poor performance for SELECT with ROWNUM=1 with literal replacement
4458226,,, High version count with cursor_sharing=force
4456646, Dump (kxscod/memcmp) using literal replacement
4451881,, OERI[kkslhsh1] from Insert as SELECT
4436832, False ORA-979 “not a group by expression” with literal replacement
4359367,, High version_count with cursor sharing
4254094, OERI[qerrmObnd1][932] possible for queries over DB links
4202503, Parse errors possible using CONTAINS with cursor_sharing=similar/force
4200541, Parse error when CURSOR_SHARING=FORCE|SIMILAR with user binds
3880881,, Dump (kkslMarkLiteralBinds) using cursor sharing
2837580, Dump if SQL has > ~35000 literals
13349665 ORA-600 [kkslmtl-valnotfound] with fix for bug 9767674 (eg After applying PSU
3980673,, Literal replacement may dump (kkslpbp) if user binds have names like SYS_B_xx
3842253,, Dump (kgghash) using literal replacement
3819834,, Dump (kkslflb) with literal replacement
3818541,, CURSOR_SHARING=force does not work if SQL has an NVL or DECODE predicate
3737955,, Long parse times for long inlists / many AND/OR terms
3668572,, ORA-979 when CURSOR_SHARING = force|similar with inline view and GROUP BY
3645694, Poor plan from SQL with literal replacement and LIKE .. ESCAPE ..
3406977,, High version count in V$SQL due to binds marked as non-data with CURSOR_SHARING=FORCE
3148830,, Select over database link fails with OERI[opixmp-7] if CURSOR_SHARING=similar
3128363, OCIAttrGet returns wrong size when CURSOR_SHARING=SIMILAR or FORCE
3007574 OERI:15212 using CURSOR_SHARING=FORCE with database links
5526018 Dump [kkslpbp] / OERI[kkslpbp:1] with materialized view and literal replacement
3132071, Wrong results possible with CURSOR_SHARING=FORCE|SIMILAR
3045623, OERI[kkslhsh1] possible with CURSOR_SHARING=FORCE
2958096, Using XMLType / objects over DBLINKS with CURSOR_SHARING may dump
2843601, DML across a dblink fails with ORA-24370 if cursor_sharing is enabled
2643579, ORA-7445 [kxspoac] using CURSOR_SHARING=FORCE (or similar)
2620541, ORA-7445 [KXSPOAC] can occur on DML using CURSOR_SHARING
2508702, ORA-7445 [KKSLHIB] with CURSOR_SHARING=FORCE with INTO clause
2442125, Wrong results from PQ which compares a CHAR column to a BIND variable
2308292,, ORA-7445 [kkslhib] for INTO <bind> using CURSOR_SHARING and SESSION_CACHED_CURSORS
2273604,, CURSOR_SHARING=FORCE may not share cursors using a BETWEEN clause with CBO
2052836,,, Client dump possible using CALL statement with CURSOR_SHARING=FORCE
4197915 CAST( x as number(n)) fails in PQ slaves with ORA-1727 with cursor_sharing
3614299 Dump adding a subscriber with a rule containing literals with CURSOR_SHARING = FORCE|SIMILAR
2442097 INSERTS into USER-DEFINED TYPES with CURSOR_SHARING=FORCE does not share cursors
2237610 OERI:[kgskbwt1] / OERI:[kskbind1] possible using CURSOR_SHARING
2300719, Spin possible in PRSGNT when CURSOR_SHARING=SIMILAR
2262665,, ORA-7445 [kkslpbp] possible with CURSOR_SHARING=FORCE
2259787, Dump possible in EXPCMPBND() with CURSOR_SHARING and GROUPING SETS
2154645, Spin in KKSFBC possible when CURSOR_SHARING=FORCE
1947974, False ORA-1008 possible using EXECUTE IMMEDIATE with CURSOR_SHARING=SIMILAR
1840199, OERI:EVAGGSID-1 possible using user defined aggregates (UDAG) with CURSOR_SHARING=FORCE|SIMILAR
1782025, Client may dump/error using DML RETURNING with CURSOR_SHARING=FORCE|SIMILAR
1777504,, Hang possible with CURSOR_SHARING=FORCE (or SIMILAR in 9i)
1764925, OERI:QCTSTC2O1 possible using CURSOR_SHARING = SIMILAR
2159152, Cursors not shared with CURSOR_SHARING=FORCE if it has a transitive predicate
1538450, Dump in KXSPOAC possible CURSOR_SHARING=FORCE set
1365873, OERI:17182 / CGA corruption with CURSOR_SHARING=FORCE
1358871, CURSOR_SHARING=FORCE may dump if first bind is a literal
984251, CURSOR_SHARING=FORCE can cause ORA-29909 when using an ANCILLARY OPERATOR
2485018 OERI:[KKSLGOP1] from SQL issued over a DBLINK with CURSOR_SHARING
2221407 ORA-3106 / OERI:15212 using BINDS over DBLINK with CURSOR_SHARING=FORCE
1545473, ORA-979 possible using CURSOR_SHARING=FORCE with GROUP BY & ORDER BY & LITERALS
1111796 Dump possible from CURSOR_SHARING=FORCE
984132 Literal in cursor expression can CORE DUMP with CURSOR_SHARING=FORCE




  1. Jeremmy Lee says:


  2. Jeremmy Lee says:

    使用IDE工具查询没有问题,而使用IBATIS查询就会报错ORA-00600: internal error code, arguments: [kkslgbv0], [], [], [], [], [], [], []。


  1. […] 这种cursor_sharing设置为similar的方法,在《Oracle优化日记》中也被老白提过,但这并不是特别推荐的方法,Maclean Liu的一篇文章可以证明:《根据metalink文档<ANNOUNCEMENT: Deprecating the cursor_sharing = ‘SIMILAR’ setting… […]

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
QQ 47079569