在上篇中,我们分析了在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(*)
----------
1
SQL> select /*+ demo_id3_1 */ count(*) from t where id3=2000;
COUNT(*)
----------
1
SQL> select /*+ demo_id3_1 */ count(*) from t where id3=3000;
COUNT(*)
----------
1
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(*)
----------
1
SQL> select /*+ demo_id3_2 */ count(*) from t where id2=2000;
COUNT(*)
----------
1
SQL> select /*+ demo_id3_2 */ count(*) from t where id2=3000;
COUNT(*)
----------
1
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(*)
----------
1
SQL> select /*+ demo_id1_3 */ count(*) from t where id1=2000;
COUNT(*)
----------
1
SQL> select /*+ demo_id1_3 */ count(*) from t where id1=3000;
COUNT(*)
----------
1
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(*)
----------
1
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取值。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-709376/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-709376/