统计量收集Method_Opt参数使用（上）

最新推荐文章于 2023-12-15 08:43:15 发布

ciqu9915

最新推荐文章于 2023-12-15 08:43:15 发布

阅读量208

点赞数

文章标签：数据库

对Oracle优化器CBO而言，统计量是至关重要的工作基础。在早期的CBO版本中，我们使用analyze table方法进行统计量收集。到10g之后，这种方法被精细度更高的dbms_stats包取代。相比analyze table方法，dbms_stats在很多方面有不可取代的优势。

下面是一个典型的dbms_stats方法调用。

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

PL/SQL procedure successfully completed

其中,method_opt参数是一个重要的工作控制参数，与表列统计量有关。目前的method_opt参数主要负责三部分职责：

ü 用来指定对哪些数据列收集或者不收集统计量；

ü 数据列直方图Histogram生成控制；

ü 拓展统计量extended statistics数据收集；

本篇分别从实验的各个环节进行分析。

1、实验环境介绍

我们选择11gR2版本进行试验。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

数据表T创建。

SQL> create table t as select * from dba_objects ;

Table created

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

PL/SQL procedure successfully completed

2、Method_Opt参数官方解析

从官方资料中，我们可以找到dbms_stats方法的说明如下：

procedure gather_table_stats

(ownname varchar2, tabname varchar2, partname varchar2 default null,

estimate_percent number default DEFAULT_ESTIMATE_PERCENT,

block_sample boolean default FALSE,

method_opt varchar2 default DEFAULT_METHOD_OPT,

degree number default to_degree_type(get_param('DEGREE')),

granularity varchar2 default DEFAULT_GRANULARITY,

cascade boolean default DEFAULT_CASCADE,

stattab varchar2 default null, statid varchar2 default null,

statown varchar2 default null,

no_invalidate boolean default

to_no_invalidate_type(get_param('NO_INVALIDATE')),

stattype varchar2 default 'DATA',

force boolean default FALSE);

-- method_opt - method options of the following format

-- method_opt := FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

-- FOR COLUMNS [size_clause]

-- column|attribute [size_clause]

-- [,column|attribute [size_clause] ... ]

-- size_clause := SIZE [integer | auto | skewonly | repeat],

-- where integer is between 1 and 254

-- column := column name | extension name | extension

-- default is FOR ALL COLUMNS SIZE AUTO.

-- The default value can be changed using set_param procedure.

-- Optimizer related table statistics are always gathered.

-- If an extension is provided, the procedure create the extension if it

-- does not exist already. Please refer to create_extended_stats for

-- description of extension.

从说明材料中，可以发现几个方面的内容：首先，method_opt并不只是gather_table_stats过程独有的参数，而是dbms_stats包若干方法中共有的一个控制参数。如果没有指定的话，Oracle选择了一个默认值DEFAULT_METHOD_OPT赋值。

在包声明中寻找DEFAULT_METHOD_OPT取值，可以找到一个定义值’z’。

DEFAULT_METHOD_OPT CONSTANT VARCHAR2(1) := 'Z';

取值内部Z是一个编码内部解析的原理，不是随便的字符可以得到解析。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'Z');

PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'A');

begin dbms_stats.gather_table_stats(user,'T',method_opt => 'A'); end;

ORA-20000: Cannot parse for clause: A

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

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

ORA-06512: 在 line 1

从官方解析的看，11gR2这个参数的取值作用是for all columns size auto。注意：之前版本9i时，这个参数取值不是这个。

那么，这个method_opt默认取值for all columns size auto的作用是什么呢？这个取值是要给所有的数据列生成统计量，并且依据auto的原则生成直方图Histogram。

3、auto与直方图

直方图Histogram是Oracle为了解决倾斜列Skew进行的一种折中处理方法。因为如果一个列取值分布是不平均的，那么where条件中出现不同的取值，对应的最优执行计划可能是不同的。

但是，传统的密度、选择率等统计量是不能够反应取值分布差异的。所以，Oracle选择使用高度均衡和频度均衡直方图的方法对数据进行描述。

那么，是不是“描述”的越多越好呢？我们先不讨论正常数据模型列的数据分布是不是应该有很多的倾斜列。Oracle进行直方图的收集，要针对列每一个数据行进行复杂的检索动作，而且直方图数据要对应额外的存储空间。

如果给每一个数据列都收集统计量直方图，统计量收集过程要消耗很多的性能和空间。这就意味着，我们需要统计那些“需要收集统计量直方图”的列。

参数method_opt的默认值for all columns size auto是目前Oracle选择出的适应性最强的功能取值。我们本部分探讨一下该参数取值的机理。我们先看一下默认取值下的统计量信息。

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_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

OWNER NONE

OBJECT_NAME NONE

SUBOBJECT_NAME NONE

OBJECT_ID NONE

DATA_OBJECT_ID NONE

OBJECT_TYPE NONE

CREATED NONE

LAST_DDL_TIME NONE

TIMESTAMP NONE

STATUS NONE

TEMPORARY NONE

GENERATED NONE

SECONDARY NONE

NAMESPACE NONE

EDITION_NAME NONE

15 rows selected

注意，此时Oracle为“all columns”也就是所有列生成了统计量，但是在目前情况下，没有一个直方图被生成。auto参数没有明确指定为哪个列生成直方图，更没有指定直方图bucket数量。

熟悉dba_objects数据源的朋友很清楚，一些数据列，如status，owner和object_type有明显的数据倾斜特性。

auto取值是和Oracle内部的column使用监控机制有关。我们站在Oracle的角度看待问题，auto选项让Oracle并不是在意所有数据列是否真正倾斜，因为这样做会去探究所有数据列一遍，从而消耗资源。Oracle的做法是：探究一下“使用过”的数据列是不是倾斜的，如果是倾斜的就生成直方图。

这就引入了基表col_usage$。这个内部数据表是列使用监控的一个重要结果记录，当一个SQL发出的时候，会将SQL使用数据列的情况作为统计项目进行记录。

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

OBJECT_ID

----------

78037

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

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

以object_id作为检索项，我们可以明确的看到一个数据表的数据列被引用的情况。在之前的数据表T中，因为从来没有针对where列的检索条件，所以没有针对任何列的使用情况，进而也就在auto情况下不会去尝试检测是否需要生成直方图。

那么我们使用条件SQL进行测试。

SQL> select count(*) from t where wner='SYS' and status='VALID';

COUNT(*)

----------

30922

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

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

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

我们设置了where条件列，注意默认情况下，监控monitor信息是不会立即写入到col_usage$中的，我们可以使用dbms_stats的flush_database_monitoring_info来手工刷新到基表中。

但是从上面的例子看，即使手工刷新，有时候也不能立即写入。笔者这里的经验是：当使用单列条件的时候，刷新机会要高很多。

SQL> select count(*) from t where wner='SYS';

COUNT(*)

----------

30926

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed

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

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

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

78037 1 1 0 0 0 0 0 21-九月-13

Intcol#表示的含义是internal column id，大部分情况下，这个id与列id相匹配，但是不是绝对的。有一些场合下，这两个值还存在差异。我们留待以后进行讨论。

在有“使用”owner信息的情况下，我们手工收集统计量。

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_col_statistics where wner='SYS' and table_name='T';

COLUMN_NAME HISTOGRAM

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

OWNER FREQUENCY

OBJECT_NAME NONE

SUBOBJECT_NAME NONE

OBJECT_ID NONE

DATA_OBJECT_ID NONE

OBJECT_TYPE NONE

CREATED NONE

LAST_DDL_TIME NONE

TIMESTAMP NONE

STATUS NONE

TEMPORARY NONE

GENERATED NONE

SECONDARY NONE

NAMESPACE NONE

EDITION_NAME NONE

15 rows selected

直方图生成。这就是auto默认参数，也是11g之后默认Oracle直方图行为。应该说，这样的机制是比较公允的。

之前，笔者使用了比较大的篇幅，介绍了Oracle method_opt的默认参数。在之前直方图的早期版本，Oracle默认是不收集直方图的。要注意这个差异。

下面，我们从格式和使用上继续介绍method_opt参数。

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

转载于:http://blog.itpub.net/17203031/viewspace-772951/

ciqu9915

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
统计量收集Method_Opt参数使用（上）

对Oracle优化器CBO而言，统计量是至关重要的工作基础。在早期的CBO版本中，我们使用analyze table方法进行统计量收集。到10g之后，这种方法被精细度更高的dbms_stats包取代。相比analyze t...
复制链接

扫一扫