【cursor_sharing】cursor_sharing参数设置exact,similar,force的区别

Oracle中为了提高sql的执行效率，需要减少硬解析，实现shared cursor共享，最常见的方法是使用绑定变量，但很多时候由于各种原因未能在开发初期使用绑定变量，对于减少硬解析的目的，退而求其次地方法是设置cursor_sharing.1.准备环境实验环境BALLON@PROD> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 – Production 使用脚本插入数据后：BALLONTT@PROD> desc t;Name Null？ Type----------------------- -------- ----------------ID VARCHAR2（5）NAME NUMBER（38） BALLONTT@PROD> select id,count（*） from t group by id;ID COUNT（*）----- ----------d 6a 10000b 20000c 20 BALLONTT@PROD> create index ind_id on t（id）;Index created. 2.取值为exact时（默认）：BALLONTT@PROD> show parameter cursor_sharing;NAME TYPE VALUE------------------------------------ ----------- -----------------cursor_sharing string EXACT BALLONTT@PROD> select count（*） from t where id="b";COUNT（*）----------20000 BALLONTT@PROD> select count（*） from t where id="d";COUNT（*）----------6 BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlareawhere sql_text like "select count（*） from t where id=%"; SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS---------------------------------------- ------------- ------------- ----------select count（*） from t where id="d" b0gfs7u9r55rv 1 1select count（*） from t where id="b" fqurbumy7bsg6 1 1可以看到两条查询语句没有使用绑定变量，有各自对应的sql_id，子游标数均为1个。两个sql查询没有任何关系。查看两次sql的执行计划：BALLONTT@PROD>select * from table（dbms_xplan.display_cursor（"b0gfs7u9r55rv",0,"advanced"））;PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID b0gfs7u9r55rv, child number 0-------------------------------------select count（*） from t where id="d"Plan hash value: 3666266488---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 （100）| || 1 | SORT AGGREGATE | | 1 | 2 | | | PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|* 2 | INDEX RANGE SCAN| IND_ID | 14 | 28 | 1 （0）| 00:00:01 |----------------------------------------------------------------------------第一次查询利用了索引。 BALLONTT@PROD> select * from table（dbms_xplan.display_cursor（"fqurbumy7bsg6",0,"advanced"））;PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID fqurbumy7bsg6, child number 0-------------------------------------select count（*） from t where id="b" Plan hash value: 2966233522---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |---------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 15 （100）| || 1 | SORT AGGREGATE | | 1 | 2 | | | PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|* 2 | TABLE ACCESS FULL| T | 19783 | 39566 | 15 （0）| 00:00:01 |---------------------------------------------------------------------------第二次查询利用了全表扫描我们在更直观地来看一下两次sql查询后的硬解析统计情况：BALLONTT@PROD> select count（*） from t where id="a";COUNT（*）----------10000BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%";NAME VALUE---------------------------------------------------------------- ----------parse time cpu 2133parse time elapsed 4463parse count （total） 54889parse count （hard） 6579（硬解析数目）parse count （failures） 52 BALLONTT@PROD> select count（*） from t where id="c";COUNT（*）----------20BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%";NAME VALUE---------------------------------------------------------------- ----------parse time cpu 2134parse time elapsed 4464parse count （total） 54895parse count （hard） 6580（硬解析数目）parse count （failures） 52硬解析数目再次加1因为数据的巨大差异性，导致了对两次查询有不同的执行计划，这也说明在cursor设置为exact时，两条sql语句如果存在一点不同，就不会共享cursor，而进行两次硬解析。 3.设置为force时Oracle对输入的SQL值，会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时，会进行cursor sharing共享游标BALLONTT@PROD> alter system set cursor_sharing=force;System altered.BALLONTT@PROD> show parameter cursor_sharing;NAME TYPE VALUE------------------------- ------------ ------- cursor_sharing string FORCE 清除一下share pool中已缓存的cursorBALLONTT@PROD> alter system flush shared_pool;System altered. 查看硬解析情况：BALLONTT@PROD> select count（*） from t where id="b";COUNT（*）----------20000BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%"; NAME VALUE------------------------------------- ----------parse time cpu 2163parse time elapsed 4506parse count （total） 55097parse count （hard） 6668parse count （failures） 52 BALLONTT@PROD> select count（*） from t where id="d";COUNT（*）----------6BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%";NAME VALUE----------------------------------------- ----------parse time cpu 2164parse time elapsed 4507parse count （total） 55101parse count （hard） 6669parse count （failures） 52硬解析加1了，这不应该呀！！ BALLONTT@PROD> select sql_text,child_number from v$sql where sql_text like "select count（*） from t where id%";SQL_TEXT CHILD_NUMBER---------------------------------------- ------------select count（*） from t where id="d" 0select count（*） from t where id="b" 0可以看到并没有使用绑定变量，force的设置没有生效。 解决办法：应在设置cursor_sharing前，执行两次下面语句：alter system flush shared_pool;BALLONTT@PROD> alter system flush shared_pool;System altered.BALLONTT@PROD> alter system flush shared_pool;System altered.BALLONTT@PROD> alter system set cursor_sharing=force;System altered.设置好了，接着进行sql测试BALLONTT@PROD> select count（*） from t where id="d";COUNT（*）----------6BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%";NAME VALUE-------------------------------------- ----------parse time cpu 2216parse time elapsed 4572parse count （total） 55867parse count （hard） 6910parse count （failures） 55 BALLONTT@PROD> select count（*） from t where id="b"; COUNT（*）----------20000BALLONTT@PROD> select name,value from v$sysstat where name like "%parse%";NAME VALUE--------------------------------------- ----------parse time cpu 2216parse time elapsed 4572parse count （total） 55869parse count （hard） 6910parse count （failures） 55硬解析的次数没有发生变化 BALLONTT@PROD> select sql_text,sql_id,version_count,executions from v$sqlarea2 where sql_text like "select count（*） from t where%";SQL_TEXT SQL_ID VERSION_COUN EXECUTIONS---------------------------------------------- ------------- ------------- -------------select count（*） from t where id=:"SYS_B_0" g82ztj8p3q174 1 2可以看到两次查询使用了绑定变量，将谓词的值用sys_B_0代替。该语句执行了两次，有一个child cursor（子游标）。在来看一下两次查询语句的执行计划：BALLONTT@PROD> select * from table（dbms_xplan.display_cursor（"g82ztj8p3q174",0,"advanced"））;PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID g82ztj8p3q174, child number 0-------------------------------------select count（*） from t where id=:"SYS_B_0" Plan hash value: 3666266488 ----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 1 （100）| || 1 | SORT AGGREGATE | | 1 | 4 | | | PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------|* 2 | INDEX RANGE SCAN| IND_ID | 6 | 24 | 1 （0）| 00:00:01 |----------------------------------------------------------------------------两次的查询使用了同一个执行计划：索引扫描。这就是force的设置。对与参数cusor_sharing设置为force时，根据实验，我们可以得出下列结论：

Oracle对输入的SQL值，会将where条件取值自动替换为绑定变量。以后在输入相同的结构SQL语句时，会进行cursor sharing共享游标；
在第一次进行自动替换绑定变量的时候，Oracle会依据bind peeking取值，获取到一个执行计划，对应成子游标；
在以后的SQL语句中，只要出现父游标可共享的情况，Oracle都会强制使用生成的唯一子游标进行sharing。不去在乎是不是对SQL是最优的执行计划；
FORCE取值的规则思想很简单，对SQL语句进行强制的绑定变量替换。使用第一次的bind peeking值生成执行计划，之后全部使用这个执行计划。这种方式实现了游标共享，避免出现大量的library cache硬解析，限制一个父游标的version_count数量。
如果这种SQL语句本身是“Good SQL”，也就是条件列分布比较平均，没有出现过大的偏移分布。我们认为这种FORCE是很有益的。但是如果数据列分布不平均，这样借用第一次输入的bind peeking生成并且共享执行计划就很成问题。我们说，在cursor_sharing取定FORCE遇到的潜在问题，和我们使用绑定变量时候使用的bind peeking值问题是相同的。

【VIP】RAC中VIP2异常转移的解决方法Oracle 11g ORA-03113故障，重建控制文件相关资讯 cursor_sharing

cursor_sharing引发的ORA-00600错（01月09日）

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