ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 统计量收集Method_Opt参数使用(下)

统计量收集Method_Opt参数使用(下)

原创 Linux操作系统 作者:realkid4 时间:2013-09-22 18:16:28 0 删除 编辑

上篇中,我们介绍了dbms_stats的重要参数method_opt的使用和默认参数。本篇中我们继续来讨论这个参数的作用。

 

4method_opt的格式块

 

从官方介绍上看,method_opt格式是一个字符串结构,有如下结构:

 

 

For all [indexed | hidden] columns size xxx

 

或者

 

For columns size xxx column

 

 

含义中,字符串包括两个层面:一个是要确定要收集数据表哪些列的统计量,另一个是要确定收集直方图的时候设置多少个bucket

 

目前实践场景中,第一个层面一般都是选择收集所有的统计量。也就是使用all columns选项。早期CBO和一些特殊场景下,可以考虑使用indexedhidden取值,但是并不推荐。

 

All indexed columns表示只是将出现在数据表索引中的数据列收集统计量。非索引列是不会收集统计量。同时,只有索引列才会创建直方图。

 

我们给实验数据表T添加索引对象。

 

 

SQL> create index idx_t_owner on t(owner);

Index created

 

SQL> create index idx_t_status on t(status);

Index created

 

SQL> create index idx_t_id on t(object_id);

Index created

 

--删除原有的统计量

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed columns size 254');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

OWNER                                   27 FREQUENCY

OBJECT_ID                              254 HEIGHT BALANCED

STATUS                                   2 FREQUENCY

 

 

Indexed columns方法显然是反映了Oracle在列统计量收集问题上的一种想法。但是,这种思考是欠考虑的。因为我们不能保证where条件后面出现的所有列均是索引列,而且其他操作,如group by等也是有列因素在其中的。

 

Hidden columns选项的范围更小,只有那些virtual columns才会被收集统计量。Hidden columns统计量可以帮助提高虚拟列的成本评估。

 

Size部分表示的是生成直方图的时候,选择的bucket个数。Size参数可以有如下控制值:

 

Auto10g之后的默认选项。根据列使用的情况和数据分布情况进行直方图的创建。根据我们在上篇中的讨论,auto情况下要依据col_usage$基础表和数据的倾斜情况来判断。

 

整数:直接指定bucket的个数。在11g版本中,bucket个数在1-254之间。如果选择直接指定bucket个数的方法,我们可以找到的是一个最大bucket数量。具体真实的个数要根据收集过程中的实际情况而定。如上面的代码片段,我们要求生成254 bucket的直方图,但是只有离散度最好的object_id生成了高度均衡的254 bucket直方图,其他都比较少。应该说,实际收集过程中的bucket个数,与distinct值和直方图类型相关。

 

Skewonly:根据数据列的分布情况来判断直方图的生成。

 

 

SQL> exec dbms_stats.delete_table_stats(user,'T');

 

PL/SQL procedure successfully completed

 

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

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

OWNER                                   29 FREQUENCY

OBJECT_NAME                            254 HEIGHT BALANCED

SUBOBJECT_NAME                         110 FREQUENCY

OBJECT_ID                                1 NONE

DATA_OBJECT_ID                         254 HEIGHT BALANCED

OBJECT_TYPE                             35 FREQUENCY

CREATED                                254 HEIGHT BALANCED

LAST_DDL_TIME                          254 HEIGHT BALANCED

TIMESTAMP                              254 HEIGHT BALANCED

STATUS                                   2 FREQUENCY

TEMPORARY                                2 FREQUENCY

GENERATED                                2 FREQUENCY

SECONDARY                                2 FREQUENCY

NAMESPACE                               17 FREQUENCY

EDITION_NAME                             0 NONE

 

15 rows selected

 

 

从上面的结果看,如果使用skewonly选项,Oracle会去分析数据列的分布情况。如果数据呈现出偏移倾斜的情况,会去生成直方图。

 

RepeatOracle的统计量是一个累积的过程。使用repeat选项去收集哪些当前已经有统计量的列。

 

5、特定列的统计量收集

 

For all columns是收集所有的数据列,从格式上,method_opt还支持一种特定列统计量的收集格式。具体格式为for columns size xxx [column name]

 

 

SQL> exec dbms_stats.delete_table_stats(user,'T');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

OWNER                                   10 HEIGHT BALANCED

 

 

这样的格式中,只给指定的列生成指定数量bucket的统计量。那么,如果需要收集多个列的统计量,可以按照两种格式进行指定。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner for columns size 5 object_name');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

OWNER                                   10 HEIGHT BALANCED

OBJECT_NAME                              5 HEIGHT BALANCED

 

 

SQL> exec dbms_stats.delete_table_stats(user,'T');

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 owner object_name object_id');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SYS' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

OWNER                                   10 HEIGHT BALANCED

OBJECT_NAME                             10 HEIGHT BALANCED

OBJECT_ID                               10 HEIGHT BALANCED

 

 

6、拓展extended统计量收集

 

Oracle CBO优化器的统计量统计维度默认是单列。当SQL语句中出现多个条件列的时候,其估算的行数是不准确的。在目前的版本中,有两个方法来提高估算精确度,一个是采用动态采样Dynamic Sampling进行实时收集,另一个就是采用11g的拓展统计量Extended Statistic

 

在之前的文章中,我们介绍过如何创建Extended统计量。我们使用method_opt,也可以进行拓展统计量收集。

 

 

--Sys用户下

SQL> exec dbms_stats.delete_table_stats(user,'T');

 

PL/SQL procedure successfully completed

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

 

begin dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)'); end;

 

ORA-20000: Unable to create extension: not supported for SYS owned table

ORA-06512: "SYS.DBMS_STATS", line 20337

ORA-06512: "SYS.DBMS_STATS", line 20360

ORA-06512: line 1

 

 

转换到scott普通用户下进行试验。

 

 

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> drop table t purge;

 

Table dropped

 

SQL> create table t as select * from dba_objects;

 

Table created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for columns size 10 (owner, object_type)');

 

PL/SQL procedure successfully completed

 

SQL> select column_name, num_buckets, histogram from dba_tab_col_statistics where wner='SCOTT' and table_name='T';

 

COLUMN_NAME                    NUM_BUCKETS HISTOGRAM

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

SYS_STUXJ8K0YTS_5QD1O0PEA514IY          10 HEIGHT BALANCED

 

 

7、结论

 

在诸多Oracle dbms_stats参数中,method_opt灵活性很高。使用好这个参数,可以帮助我们更好的进行精确化统计量定义,提高SQL执行效率。

 

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7630781