Oracle直方图副作用,直方图Histograms与CRUSOR_SHARING

有时候可以将cursor_sharing设置为similar来降低由于没有使用绑定变量导致硬解析过高的问题。

可是它有个副作用，如果等值查询的列上存在直方图，ORACLE就会认为=后面的变量产生的CURSOR都是危险的，进而导致硬解释。

且version_count增加。

简单实验如下：

创建测试表

SQL>create table test as select * from dba_objects;

分析表

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME    => 'SCOTT',

TABNAME    => 'TEST',

CASCADE    => TRUE,

METHOD_OPT => 'FOR ALL COLUMNS SIZE auto');

END;

查看Dba_Histograms视图

SQL>SELECT * FROM Dba_Histograms WHERE table_name='TEST';(输出结果略)

发现object_id列上已经存在了直方图。

SQL>alter system set cursor_sharing='similar';

查询test表：

SQL>select * from test where object_id=1;

通过V$SQL得到这个语句的sql_id，然后可以通过v$sqlarea的version_count字段，观察变化。

object_id的改变都会导致version_ count加1。

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

1

SQL>select * from test where object_id=15;

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

2

SQL>select * from test where object_id=10;

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

3

我们取消直方图看看是什么效果：

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname => 'TEST1',cascade => TRUE,method_opt => 'FOR COLUMNS OBJECT_ID SIZE 1');

继续实验：

SQL>select * from test where object_id=30;

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

3

SQL>select * from test where object_id=50;

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

3

SQL>select * from test where object_id=60;

SQL>SELECT version_count FROM v$sqlarea WHERE sql_id='dd0u25bgmr2rj'；

VERSION_COUNT

-------------

3

version_count不再变化了。

有几个概念需要解释一下，方便菜鸟级的理解本贴：

一个语句能否被共享，需要满足parent cursor和child cursor都可以被共享才行。

parent cursor：保存的是sql语句的文本。

chlid     cursor：保存的是解析计划和环境信息。

在本案例中，都是chlid cursor没有被共享导致的硬解析。

version_count过高的坏处就是，可能会导致library cache latch竞争。每次执行sql前都需要到library cache中先查找parent cursor是否可以共享，如果可以共享，继续查询child cursor是否可以共享，如果可以就是软解析，否则为硬解析。这个过程里，是需要获得library cache latch的，如果version_count过多，就会导致搜索过程变长，占用library cache latch时间就会变长，这在高并发的数据库中很可能就是灾难(latch的串行机制。)

[本帖最后由 wei-xh 于 2010-5-2 20:42 编辑]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-662228/，如需转载，请注明出处，否则将追究法律责任。