【干货分享】如何利用DBMS_STATS收集统计信息

达梦大数据 2019-12-13

1097

前言

优化统计信息描述了数据库中的对象细节。查询优化使用这些信息选择最合适的执行计划。使用DBMS_STATS包来收集统计、删除信息，将收集的统计信息记录在数据字典中。

DBMS_STATS 包中涉及到类型，如下统一说明。

OBJECTELEM 类型是 DBMS_STATS 专有类型。用户不能引用和改变该记录的内容。

OBJECTELEM 记录类型定义如下：

TYPE OBJECTELEM IS RECORD (

OWNNAME VARCHAR(128) ,

OBJTYPE VARCHAR(6) ,

OBJNAME VARCHAR(128) ,

PARTNAME VARCHAR(128) ,

SUBPARTNAME VARCHAR(128)

) ;

参数详解

OWNNAME 模式名。

OBJTYPE 对象类型，TABLE 或 INDEX。

OBJNAME 对象（表或索引）名称，区分大小写。

PARTNAME 分区名称，区分大小写。

SUBPARTNAME 子分区名称，区分大小写。

OBJECTTAB 为 OBJECTELEM 类型的索引表：

TYPE OBJECTTAB IS TABLE OF OBJECTELEM INDEX BY IN

测试环境：

操作系统：中标麒麟6

达梦数据库：DMV8.1

相关案例分析：

使用包内的过程和函数之前，如果还未创建过系统包。请先调用系统过程创建系统包。SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');

案例1：

收集模式DMHR模式下EMPLOYEE表的统计信息，并打印收集的表信息。

使用方法：GATHER_SCHEMA_STATS

收集模式下对象的统计信息。

打印结果

语法如下：

PROCEDURE GATHER_SCHEMA_STATS (

OWNNAME VARCHAR(128),

ESTIMATE_PERCENT DOUBLE DEFAULT

TO_ESTIMATE_PERCENT_TYPE(GET_PREFS('ESTIMATE_PERCENT')),

BLOCK_SAMPLE BOOLEAN DEFAULT FALSE,

METHOD_OPT VARCHAR DEFAULT GET_PREFS('METHOD_OPT'),

DEGREE INT DEFAULT TO_DEGREE_TYPE(GET_PREFS('DEGREE')),

GRANULARITY VARCHAR DEFAULT GET_PREFS('GRANULARITY'),

CASCADE BOOLEAN DEFAULT TO_CASCADE_TYPE(GET_PREFS('CASCADE')),

STATTAB VARCHAR DEFAULT NULL,

STATID VARCHAR DEFAULT NULL,

OPTIONS VARCHAR DEFAULT 'GATHER',

OBJLIST OUT OBJECTTAB DEFAULT NULL,

STATOWN VARCHAR DEFAULT NULL,

NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE

(GET_PREFS('NO_INVALIDATE')),

FORCE BOOLEAN DEFAULT FALSE,

OBJ_FILTER_LIST OBJECTTAB DEFAULT NULL

参数详解：

• OWNNAME 模式名，区分大小写。

• ESTIMATE_PERCENT 收集的百分比，范围为 0.000001~100，默认系统自定。

• BLOCK_SAMPLE 保留参数，是否使用随机块代替随机行，默认为 TRUE。

• METHOD_OPT 控制列的统计信息集合和直方图的创建；

默认为 FOR ALL COULMNS SIZE AUTO；只支持其中一种格式：

FOR ALL [INDEXED | HIDDEN] COLUMNS [<size_clause>]

<size_clause>：:= SIZE {integer | REPEAT | AUTO | SKEWONLY}

• DEGREE 保留参数，收集的并行度，默认为 1。

• GRANULARITY 保留参数，收集的粒度，默认为 ALL。

• CASCADE 是否收集索引信息，TRUE 或 FALSE。默认为 TRUE。

• STATTAB 保留参数，统计信息存放的表，默认为 NULL。

• STATID 保留参数，统计信息的 ID，默认为 NULL。

• OPTIONS

控制收集的列，默认为 NULL ；选项如下：GATHER|GATHER AUTO|GATHER

STALE|GATHER EMPTY|LIST AUTO|LIST STALE|LIST EMPTY。

各选项解释如下：

• GATHER：收集模式下所有对象的统计信息。

• GATHER AUTO：自动收集需要的统计信息。系统隐含的决定哪些对象需要新的统计信息，以及怎样收集这些统计信息。此时，只有 OWNNAME，STATTAB，STATID，OBJLIST AND STATOWN有效，返回收集统计信息的对象。

• GATHER STALE：对旧的对象收集统计信息。返回找到的旧的对象。

• GATHER EMPTY：收集没有统计信息对象的统计信息。返回这些对象。

• LIST AUTO：返回GATHER AUTO方式处理的对象。

• LIST STALE：返回旧的对象信息。

• LIST EMPTY：返回没有统计信息的对象。

• OBJLIST 返回 OPTION 选项对应的链表，默认为 NULL。

• STATOWN 保留参数，统计信息的模式，默认为 NULL。

• NO_INVALIDATE 保留参数，是否让依赖游标失效，默认为 TRUE。

• FORCE 保留参数，是否强制收集统计信息，默认为 FALSE。

• OBJ_FILTER_LIST 存放过滤条件的模式名、表名和子表名，默认为 NULL

案例2：

获得 DMHR 模式下表 EMPLOYEE 中列 HIRE_DATE 的统计信息。

使用方法：COLUMN_STATS_SHOW

根据模式名，表名和列名获得该列的统计信息。返回两个结果集：一个是列的统计信息;另一个是直方图的统计信息。

DBMS_STATS.COLUMN_STATS_SHOW('DMHR','EMPLOYEE','HIRE_DATE');

返回结果集：

语法如下：

PROCEDURE COLUMN_STATS_SHOW (

OWNNAME IN VARCHAR(128),

TABNAME IN VARCHAR(128),

COLNAME INVARCHAR(128)

);

列的统计信息，格式分别如下：

表列统计信息

名称	解释
NUM_DISTINCT	不同列值的个数
LOW_VALUE	列最小值
HIGH_VALUE	列最大值
NUM_NULLS	空值的个数
NUM_BUCKETS	直方图桶的个数
SAMPLE_SIZE	样本容量
HISTOGRAM	直方图的类型

参数详解:

• OWNNAME 模式名，区分大小写。

• TABNAME 表名，区分大小写。

• COLNAME 列名，区分大小写。

不适用范围

以下对象不支持统计信息：

1. 外部表、DBLINK 远程表、动态视图表、记录类型数组所用的临时表。

2. 所在表空间为 OFFLINE 的对象。

3. 位图索引，位图连接索引、虚索引、全文索引、空间索引、数组索引、无效的索引。

4. BLOB、IMAGE、LONGVARBINARY、CLOB、TEXT、LONGVARCHAR、自定义类型列和空间类型列等列类型。

关于DBMS_STATS更多的使用方法，请查阅官方相关手册。

1	【干货分享】DM数据库如何展示对象的存储空间信息
2	【干货分享】如何快速装载数据？
3	干货分享\|DM8如何在linux中注册与卸载服务？

最后修改时间：2020-06-12 17:43:20

文章转载自达梦大数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

【干货分享】如何利用DBMS_STATS收集统计信息

评论