CURSOR_SHARING,VERSION_COUNT

 

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

评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值