Cursor_sharing=SIMILAR取值与直方图（下）

在上篇中，我们分析了在cursor_sharing=SIMILAR的情况下，Oracle不会考虑数据字典和列描述信息。这个过程中，SIMILAR的效果和FORCE效果相同。本篇我们继续研究SIMILAR的工作原理。...

ciqu9915

123人浏览 · 2011-10-19 21:35:14

ciqu9915 · 2011-10-19 21:35:14 发布

在上篇中，我们分析了在cursor_sharing=SIMILAR的情况下，Oracle不会考虑数据字典和列描述信息。这个过程中，SIMILAR的效果和FORCE效果相同。本篇我们继续研究SIMILAR的工作原理。

4、有直方图情况下的SIMILAR现象

直方图是描述数据偏移的重要指标。Oracle会重视直方图所体现出的数据描述内容。那么，直方图在促使SIMILAR其作用的过程中，是否起作用呢？

首先我们强制命令收集直方图。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id3 size 5');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 53196 1 NONE

ID2 53196 1 NONE

ID3 53196 5 HEIGHT BALANCED

我们收集了id3列的高度直方图。下面观察执行效果。

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=1000;

COUNT(*)

----------

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=2000;

COUNT(*)

----------

SQL> select /*+ demo_id3_1 */ count(*) from t where id3=3000;

COUNT(*)

----------

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id3_1 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3_ 1xydsanchug00 3 3

1 */ count(*) from t

where id3=:"SYS_B_0 "

SQL> select sql_id, child_number, executions from V$sql where sql_id='1xydsanchug00';

SQL_ID CHILD_NUMBER EXECUTIONS

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

1xydsanchug00 0 1

1xydsanchug00 1 1

1xydsanchug00 2 1

注意：情况有了不同，在对应不同的id3取值情况下，由于存在直方图的不同。SIMILAR为每一个id3取值生成一个单独的执行计划。当下次再次出现这个取值的时候，会共用相同取值的执行计划。

Id3列是一个普通列，那么对于有直方图（强令生成）的主键列和唯一索引列，效果是如何呢？

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id2 size 5');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 53196 1 NONE

ID2 53196 5 HEIGHT BALANCED

ID3 53196 5 HEIGHT BALANCED

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=1000;

COUNT(*)

----------

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=2000;

COUNT(*)

----------

SQL> select /*+ demo_id3_2 */ count(*) from t where id2=3000;

COUNT(*)

----------

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id3_2 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id3_ 5gam31v1vf5a9 3 3

2 */ count(*) from t

where id2=:"SYS_B_0

唯一索引列的效果相同，主键列呢？

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for columns id1 size 5');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct, NUM_BUCKETS, HISTOGRAM from dba_tab_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM

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

ID1 53196 5 HEIGHT BALANCED

ID2 53196 5 HEIGHT BALANCED

ID3 53196 5 HEIGHT BALANCED

此时使用的SQL进行查看。

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=1000;

COUNT(*)

----------

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=2000;

COUNT(*)

----------

SQL> select /*+ demo_id1_3 */ count(*) from t where id1=3000;

COUNT(*)

----------

SQL> select sql_text, sql_id, version_count, executions from v$sqlarea where sql_text like 'select /*+ demo_id1_3 */%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select /*+ demo_id1_ 3vys9sd50v2sw 3 3

3 */ count(*) from t

where id1=:"SYS_B_0

连主键列都被判定为“Unsafe SQL”了。

上面的实验，也就证明了SIMILAR工作的原理：只看列统计量是否存在直方图。当SQL条件列存在直方图的时候，并且cursor_sharing取值为SIMILAR，这样就认为该SQL是unsafe的。就为每一个值确定一个新的执行计划。

那么，SIMILAR效果取决于是否存在直方图。我们的问题就变成如何才能让列产生直方图。

5、列与直方图

我们使用dbms_stats命令收集统计量的时候，通过method_opts参数指定生成直方图的方式。在9i版本中，这个参数设置为NONE，表示默认不生成直方图。所以在那个时代，我们经常需要手工设置method_opts参数生成偏移列的直方图。

到了Oracle 10g，method_opts参数的默认值变成为“for all columns size auto”。简单的说，由Oracle自己去决定是不是生成直方图。

SQL> create table t as select object_id id1, object_id id2, object_id id3 from dba_objects;

Table created

SQL> select object_id, data_object_id from dba_objects where wner='SYS' and object_name='T';

OBJECT_ID DATA_OBJECT_ID

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

56256 56256

SQL> select * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

我们重新构建了数据表T，内部表col_usage$记录了数据表使用情况，主要是列出现的情况。如果我们在SQL中使用了列，就会对应信息出现在该内部表中。

此时，必然没有直方图信息。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 NONE

ID2 NONE

ID1 NONE

无直方图被收集。Oracle首先会去看一看该列是否使用过，如果从没有使用过，就不会生成直方图。因为直方图毕竟是要消耗额外的CPU和存储资源的。

--使用一次数据列

SQL> select count(*) from t where id1=1000;

COUNT(*)

----------

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

SQL> select * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

56256 1 1 0 0 0 0 0 2011-10-18

--存在使用信息之后，收集统计量。

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 NONE

ID2 NONE

ID1 NONE

我们使用了数据列id1，并且在col_usage$中包含了相应记录。但是依然没有生成直方图。是否潜在意味着描述列取值的其他统计量，如密度等因素，也在起作用？我们进行额外处理id3。

SQL> update t set id3=mod(id3,10);

50682 rows updated

SQL> commit;

Commit complete

对id3取余数处理，增加数据列重复值出现的概率。

--使用一次id3

SQL> select count(*) from t where id3=0;

COUNT(*)

----------

5100

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true,method_opt => 'for all columns size auto');

PL/SQL procedure successfully completed

--出现对id3列使用记录；

SQL> select * from col_usage$ where obj#=56256;

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

56256 1 1 0 0 0 0 0 2011-10-18

56256 3 1 0 0 0 0 0 2011-10-18

SQL> select column_name,HISTOGRAM from dba_tab_cols where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

ID3 FREQUENCY

ID2 NONE

ID1 NONE

直方图出现！

说明：Oracle 10g的method_opts参数默认取值“for all columns size auto”的是相当复杂的判定过程。首先，Oracle会看这个数据列是否使用过，其次会检查该列的一些统计量细节，如密度和重复值等，之后就会判断是否生成直方图。

6、结论

经过上下两篇的研究，我们已经对cursor_sharing=SIMILAR取值的工作原理很清楚了。

首先，当一个数据列不存在直方图统计信息时候，cursor_sharing=SIMILAR的效果与cursor_sharing=FORCE相同。都会发生条件列自动绑定变量替换，都会为所有的SQL使用相同的执行计划。

但是，当一个数据列存在直方图的时候，无论该直方图是何种方式生成，cursor_sharing=SIMILAR会认为这个SQL是unsafe的，需要对每个列取值生成单独的执行计划。所以，在cursor_sharing=SIMILAR的时候，一个显著现象就是一些SQL带有非常大量的version_count取值。