CURSOR_SHARING:determines what kind of SQL statements can share the same cursors.
cursor_sharing有三个值，默认的是exact，表示每条SQL精确匹配。force表示强制绑定类似的SQL。similar，当收集了柱状图的统计信息之后，对于不同的变量会从新解析，如果没有收集柱状图，similar就和force一样。
如果SQL没有使用绑定变量，当收集了列的柱状图信息时，ORACLE会认为SQL传递过来的变量不可靠，从而为每个SQL产生一个child cursor,从而导致VERSION_COUNT增加，进一步导致library cache latch 。
下面来进行一个简单的测试，测试表test结构和内容如下:
SQL> select * from test;
NAME ADDRESS
--------------- ------------
Robinson ChongQing
luoluo China
luobingsen Earth
ROBINSON YuBei
bingbing Tiananmen
aaaaaa bbbbbbbbbb
aaaaaa bbbbbbbbbbb
已选择7行。
SQL> alter system flush shared_pool;
系统已更改。
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string EXACT
查询如下语句
SELECT NAME FROM TEST WHERE ADDRESS='ChongQing';
SELECT NAME FROM TEST WHERE ADDRESS='Tiananmen';
SELECT NAME FROM TEST WHERE ADDRESS='China';
SELECT NAME FROM TEST WHERE ADDRESS='Earth';
SELECT NAME FROM TEST WHERE ADDRESS='YuBei';
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbb';
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbbb'; ------这里就不贴出来了
SQL> select sql_text,hash_value,child_address from v$sql where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT HASH_VALUE CHILD_AD
-------------------------------------------------- ----------- --------
SELECT NAME FROM TEST WHERE ADDRESS='Tiananmen' 1916436014 342CE78C
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbbb' 4126855556 34075EFC
SELECT NAME FROM TEST WHERE ADDRESS='YuBei' 2460154197 342DA7C8
SELECT NAME FROM TEST WHERE ADDRESS='ChongQing' 1516063327 34076300
SELECT NAME FROM TEST WHERE ADDRESS='Earth' 0595162101 342BFC08
SELECT NAME FROM TEST WHERE ADDRESS='China' 2799421539 342ED838
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbb' 2831800484 342F1E38
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT VERSION_COUNT
-------------------------------------------------------------------- -------------
SELECT NAME FROM TEST WHERE ADDRESS='Tiananmen' 1
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbbb' 1
SELECT NAME FROM TEST WHERE ADDRESS='YuBei' 1
SELECT NAME FROM TEST WHERE ADDRESS='ChongQing' 1
SELECT NAME FROM TEST WHERE ADDRESS='Earth' 1
SELECT NAME FROM TEST WHERE ADDRESS='China' 1
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbb' 1
可以看到每一条SQL都硬解析了一次
SQL> alter system set cursor_sharing=similar;
系统已更改。
重启数据库
SQL> exec dbms_stats.delete_schema_stats('ROBINSON'); ----删除ROBINSON模式下所有的统计信息
PL/SQL 过程已成功完成。
再次运行上面的查询语句 ----这里为了节省篇幅不贴了
SQL> select sql_text,hash_value,child_address from v$sql where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT HASH_VALUE CHILD_AD
---------------------------------------------------------------------- ----------- --------
SELECT NAME FROM TEST WHERE ADDRESS='Tiananmen' 1916436014 342FFE8C
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbbb' 4126855556 342CC200
SELECT NAME FROM TEST WHERE ADDRESS='YuBei' 2460154197 342EE2E8
SELECT NAME FROM TEST WHERE ADDRESS='ChongQing' 1516063327 3403EB10
SELECT NAME FROM TEST WHERE ADDRESS='Earth' 0595162101 342F9898
SELECT NAME FROM TEST WHERE ADDRESS='China' 2799421539 342C0A50
SELECT NAME FROM TEST WHERE ADDRESS='bbbbbbbbbb' 2831800484 342C0250
可以看到由于没有统计（柱状图）信息，设置了这个参数并没有强制绑定变量
收集一下柱状图
SQL> exec dbms_stats.gather_table_stats('ROBINSON','TEST',cascade=>false,method_opt=>'for columns address size 2');
PL/SQL 过程已成功完成。
SQL> select sql_text,hash_value,child_address from v$sql where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT HASH_VALUE CHILD_AD
-------------------------------------------------- ----------- --------
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 3418DB10
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 3405695C
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 34056878
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 34056734
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 340565F0
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 340564AC
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 34056368
已选择7行。
SQL> select histogram from user_tab_columns where table_name='TEST';
HISTOGRAM
---------------
NONE
HEIGHT BALANCED
SQL>
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT VERSION_COUNT
-------------------------------------------------- -------------
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 7
可以看到此处虽然强制绑定了变量，但是由于收集了柱状图，导致了这么多的VERSION_COUNT，VERSION_COUNT高，有什么副作用呢？我们知道，SQL执行的时候，如果一条SQL执行过了，那么ORACLE就不会对其进行硬解析，而是软解析，这里version_count的个数，就代表一条SQL语句有多少个执行计划，也就是说同一条SQL语句再次运行会扫描执行计划，扫描的时候会产生library cache latch等待事件。
如果versionc_count过高，library cache latch就非常明显，这个时候性能就会明显下降。要解决此问题，可以将cursor_sharing设为force，不过这个是万不得已的情况，最根本的解决方法就是重写SQL，使用绑定变量。
SQL> alter system set cursor_sharing=force;
系统已更改。
SQL> alter system flush shared_pool;
再次执行上面的查询语句 -----同样为了节省篇幅，不贴了
SQL> select sql_text,hash_value,child_address from v$sql where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT HASH_VALUE CHILD_AD
-------------------------------------------------- ----------- --------
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 2234846619 3428D160
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'SELECT NAME FROM TEST%';
SQL_TEXT VERSION_COUNT
-------------------------------------------------- -------------
SELECT NAME FROM TEST WHERE ADDRESS=:"SYS_B_0" 1
可以看到这里 version_count降为1
专家的建议:不要随意修改可能对性能产生影响的参数，修该之前要做详细的测试，这里最好不要将cursor_sharing设置为similar,从应用下手，查找未使用绑定变量的SQL，然后将其改过来，如果应用无法修改，那么建议设置为force。
version_count过高，不仅仅是由于设置了cursor_sharing参数，也有可能是应用程序使用了绑定变量，但是传递进来的值长度变了，导致重新解析，另外不同模式，数据库BUG(SQL使用了绑定变量，结果还是很高的version_count)，环境变量不同，等等都会产生不同版本。这里有个小窍门，如果查询sql_text看到有SYS_B_之类的，就表示SQL没有使用绑定变量，而是设置了cursor_sharing参数导致不同版本.
关于柱状图与CURSOR_SHARING=SIMILAR的关系，请查看http://blog.csdn.net/robinson1988/archive/2009/12/10/4979443.aspx
扫一扫
451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
