Oraclecursor_sharing参数详解

原 Oracle cursor_sharing 参数 详解https://blog.csdn.net/tianlesoftware/article/details/6551723版权声明： https://blog.csdn.net/tianlesoftware/article/details/6551723
  一. 官网的说明
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams042.htm#REFRN10025
  1.1 CURSOR_SHARING
Property
Description
Parameter type
String
Syntax
CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
Default value
EXACT
Modifiable
ALTER SESSION, ALTER SYSTEM
Basic
No
         CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
  Values:
（1）FORCE
       Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
  （2）SIMILAR
       Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
  （3）EXACT
       Only allows statements with identical text to share the same cursor.
       --只有SQL 语句完全相同的情况下，才会使用相同的cursor，即执行计划。
  Notes:
       （1）If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment. FORCE limits the growth of child cursors that can occur when the setting is SIMILAR.
       （2）The value of the CURSOR_SHARING parameter has performance implications. Refer to Oracle Database Performance Tuning Guide before setting this parameter.
      1.2 When to Set CURSOR_SHARING to a Nondefault Value
       The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING. However, for applications with many similar statements, setting CURSOR_SHARING can significantly improve cursor sharing, resulting in reduced memory usage, faster parses, and reduced latch contention. Consider this approach when statements in the shared pool differ only in the values of literals, and when response time is poor because of a very high number of library cache misses.
  Setting CURSOR_SHARING to FORCE or SIMILAR has the following drawbacks:
       （1）The database must perform extra work during the soft parse to find a similar statement in the shared pool.
       （2）There is 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 does not change.
       （3）Star transformation is not supported.
       （4）If stored outlines were generated with CURSOR_SHARING set to EXACT, then the database does not use stored outlines generated with literals. To avoid this problem, generate outlines with CURSOR_SHARING set to FORCE or SIMILAR and use the CREATE_STORED_OUTLINES parameter.
           When deciding whether to set CURSOR_SHARING to FORCE or SIMILAR, consider the performance implications of each setting.
       When CURSOR_SHARING is set to FORCE, the database uses one parent cursor and one child cursor for each distinct SQL statement. The database uses the same plan for each execution of the same statement.
           When set to SIMILAR, database behavior depends on the presence of histograms:
       （1）Histogram absent for column with system-generated bind value
              Only one parent cursor and one child cursor exists for each distinct SQL statement. In this case, all executions of a SQL statement use the same plan.
       （2）Histogram present for column with system-generated bind value
              If the same SQL statement is executed multiple times, each execution has its own child cursor. In this case, the database peeks at bind variable values and create a new child cursor for each distinct value. Thus, each statement execution uses a plan based on the specific literals in the statement.
  For example, consider the following statement:
       SELECT * FROM hr.employees WHERE employee_id = 101
         If FORCE is used, or if SIMILAR is used when no histogram exists, then the database optimizes this statement as if it contained a bind variable and uses bind peeking to estimate cardinality. Statements that differ only in the bind variable share the same execution plan.
       If SIMILAR is used, and if a histogram does exist, then the database does not treat the statement as if a bind variable were used. The same query for a different employee may not use the same plan.
         If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment. FORCE limits the growth of child cursors that can occur when the setting is SIMILAR.
       Also, function-based indexes may not work when using SIMILAR because the database converts index parameters to bind variables.
       For example, if the index is SUBSTR(id,1,3), then the database converts it to SUBSTR("ID",:SYS_B_0,:SYS_B_1)=:id, rendering the index invalid.
  Oracle 绑定变量 详解
http://www.cndba.cn/Dave/article/1572
  二. 测试
2.1 cursor_sharing=exac