Oracle cursor_sharing 参数详解

一. 官网的说明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. 二. 测试2.1 cursor_sharing=exact，这是cursor_sharing的默认值 2.1.1 查看cursor_sharing 值SYS@anqing2（rac2）> show parameter cursor_sharing NAME TYPE VALUE------------------------- -------------------- ---------------------cursor_sharing string EXACT 2.1.2 查看当前硬解析值SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------ ----------parse time cpu 1882056parse time elapsed 2648194parse count （total） 12780229parse count （hard） 9890010（硬解析次数）parse count （failures） 71 2.1.3 执行一条select 语句，然后查看硬解析次数SYS@anqing2（rac2）> select * from ta where id=168; ID NAME---------- ------------------------------ 168 dave SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------ ----------parse time cpu 1882061parse time elapsed 2648196parse count （total） 12780360parse count （hard） 9890021parse count （failures） 71-- 这里硬解析的次数加一，因为之前SQL 没有解析过，所以需要进行硬解析之后才能执行。 2.1.4 执行与之前类似的SQL，谓词值不一样SYS@anqing2（rac2）> select * from ta where id=198; ID NAME---------- ------------------------------ 198 dave SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------ ----------parse time cpu 1882061parse time elapsed 2648196parse count （total） 12780482parse count （hard） 9890022parse count （failures） 71-- 硬解析次数又加1了，没有重用之前的执行计划 2.1.5 执行相同的SQL 语句SYS@anqing2（rac2）> select * from ta where id=198; ID NAME---------- ------------------------------ 198 dave SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------ ----------parse time cpu 1882061parse time elapsed 2648196parse count （total） 12780543parse count （hard） 9890022parse count （failures） 71-- 测试硬解析没有变化。重用之前的cursor。总结：在这种模式下,只有SQL 语句完全相同的情况下，才会使用相同的cursor，即执行计划。这种模式下，表有统计信息和没有统计信息的执行计划是有出入的。所以该模式下的表，需要定期的去收集统计信息。 2.2 cursor_sharing=force--修改cursor_sharing 值SYS@anqing2（rac2）> alter session set cursor_sharing="force";Session altered.SYS@anqing2（rac2）> show parameter cursor_sharingNAME TYPE VALUE------------------------- --------------------- ---------------cursor_sharing string force --查看硬解析次数SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%";NAME VALUE------------------------------ ----------parse time cpu 1882075parse time elapsed 2648219parse count （total） 12782090parse count （hard） 9890067 （硬解析次数）parse count （failures） 71 -- select 查询SYS@anqing2（rac2）> select * from ta where id=88; ID NAME---------- ------------------------------ 88 dave SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%";NAME VALUE------------------------------ ----------parse time cpu 1882075parse time elapsed 2648219parse count （total） 12782215parse count （hard） 9890068 -- 硬解析次数加一parse count （failures） 71 -- 执行相同的select，但谓词值不一样SYS@anqing2（rac2）> select * from ta where id=99; ID NAME---------- ------------------------------ 99 dave SYS@anqing2（rac2）> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------ ----------parse time cpu 1882075parse time elapsed 2648219parse count （total） 12782285parse count （hard） 9890068parse count （failures） 71--注意，这里的硬解析次数没有变化，这个就是force 的作用。只要sql语句相同，不管谓词值是否相同，都会当成相同的sql，重用之前的cursor，不会进行硬解析。 -- 查看child cursor 信息SYS@anqing2（rac2）> select sql_text,child_number from v$sql where sql_text like "select * from ta where%"; SQL_TEXT CHILD_NUMBER---------------------------------------- ------------select * from ta where id=:"SYS_B_0" 0select * from ta where id=:"SYS_B_0" 1select * from ta where id=:"SYS_B_0" 2 注意：对于相同的SQL，oracle 在这里将不同的谓词值改成了变量，这样SQL_TEXT 就相同，正常情况下，应该使用同一个cursor，即执行计划，但是在我上面的查询中，Oracle 并没有重用，而是重新生成了一个child_cursor. 这就说明Oracle 认为这个cursor 并不是最优的，所有重新生成了一个。可以通过如下SQL 查看为什么没有重用之前的cursor： SQL>select * from v$sql_shared_cursor where sql_id="c9swtz4spq3xz"; 如果这里有Y，就是导致不能重用的原因。总结： Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal. 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. FORCE limits the growth of child cursors that can occur when the setting is SIMILAR. 当cursor_sharing 设置为force时， Oracle 会把相同SQL的不同谓词值转换成变量，这样SQL_TEXT就看上去一样。 Oracle 就会使用一个相同的cursor。这样他们的执行计划也是一样的。当Oracle 认为存在的cursor 不是最优的时候，就会重新创建一个child cursor，而不重用之前的已经存在cursor。可以通过v$sql_shared_cursor 查看为什么没有重用。这样就会和我们上面查询的一样，会有多个child cursor，但是他们的parent cursor是一样的。这个child cursor 不是无限增常的，force 和similar 都会限制child cursor 的增长。

由 bind_mismatch 引起的大量 version_count 问题Oracle Statistic 统计信息小结相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）