CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

• FORCE

  Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.

• EXACT

  Only allows statements with identical text to share the same cursor.

  Notes:

  • If you set CURSOR_SHARING, then Oracle recommends the FORCE setting unless you are in a DSS environment.

  
  

这个参数是用来告诉Oracle在什么情况下可以共享游标，即SQL重用。
    Cursor_sharing参数有3个值可以设置：
     1)  EXACT：通常来说，exact值是Oracle推荐的，也是默认的，它要求SQL语句在完全相同时才会重用，否则会被重新执行硬解析操作。
     2)  FORCE：force是在任何情况下，无条件重用SQL。
   备注：上面所说的SQL重用，仅仅是指谓词条件不同的SQL语句，实际上这样的SQL基本上都在执行同样的业务操作。
             其中11gr2已经放弃SIMILAR了，SIMILAR：similar是在Oracle认为某条SQL语句的谓词条件可能会影响到它的执行计划时，才会被重新分析，否则将重用SQL。

Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following statements do not resolve to the same SQL area:

SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;

The only exception to this rule is when the parameter CURSOR_SHARING has been set to FORCE. Similar statements can share SQL areas when theCURSOR_SHARING is set to FORCE. 


How Similar Statements Can Share SQL Areas

When SQL statements use literals rather than bind variables, a nondefault setting for CURSOR_SHARING enables the database to replace literals with system-generated bind variables. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area.

When CURSOR_SHARING is set to a nondefault value, the database performs the following steps during the parse:

1. Searches for an identical statement in the shared pool

   If an identical statement is found, then the database skips to Step 3. Otherwise, the database proceeds to the next step.

2. Searches for a similar statement in the shared pool

   If a similar statement is not found, then the database performs a hard parse. If a similar statement is found, then the database proceeds to the next step.

3. Proceeds through the remaining steps of the parse phase to ensure that the execution plan of the existing statement is applicable to the new statement

   If the plan is not applicable, then the database performs a hard parse. If the plan is applicable, then the database proceeds to the next step.

4. Shares the SQL area of the statement

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, settingCURSOR_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.

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 and use the CREATE_STORED_OUTLINES parameter.

Setting CURSOR_SHARING to FORCE has the following drawbacks:

• The database must perform extra work during the soft parse to find a similar statement in the shared pool.

• 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.

• Star transformation is not supported.

When deciding whether to set CURSOR_SHARING to FORCE, 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. For example, consider the following statement:

SELECT * FROM hr.employees WHERE employee_id = 101

If FORCE is used, 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.