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