优化统计信息描述了数据库中的对象细节。查询优化使用这些信息选择最合适的执行计划。使用 DBMS_STATS 包来收集统计、删除信息,将收集的统计信息记录在数据字典中。
21.1 数据类型
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 INT;
21.2 相关方法
DBMS_STATS 包中包含的过程和函数如下详细介绍:
- COLUMN_STATS_SHOW
根据模式名,表名和列名获得该列的统计信息。用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。返回两个结果集:一个是列的统计信息;另一个是直方图的统计信息。
语法如下:
PROCEDURE COLUMN_STATS_SHOW (
OWNNAME IN VARCHAR(128),
TABNAME IN VARCHAR(128),
COLNAME IN VARCHAR(128)
);
列的统计信息和直方图的统计信息,格式分别如下表 21.1、表 21.2:
名称 | 解释 |
---|---|
NUM_DISTINCT | 不同列值的个数 |
LOW_VALUE | 列最小值 |
HIGH_VALUE | 列最大值 |
NUM_NULLS | 空值的个数 |
NUM_BUCKETS | 直方图桶的个数 |
SAMPLE_SIZE | 样本容量 |
HISTOGRAM | 直方图的类型 |
名称 | 解释 |
---|---|
OWNER | 模式名 |
TABLE_NAME | 表名 |
COLUMN_NAME | 列名 |
HISTOGRAM | 直方图类型 |
ENDPOINT_VALUE | 样本值 |
ENDPOINT_HEIGHT | 对于频率直方图,样本值的个数;对于等高直方图,小于样本值大于前一个样本值的个数。 |
ENDPOINT_KEYGHT | 对于频率直方图无效;对于等高直方图,样本值的个数。 |
ENDPOINT_DISTINCT | 对于频率直方图无效;对于等高直方图,小于样本值大于前一个样本值之间不同样本的个数。 |
参数详解
-
ownname
模式名,区分大小写。
-
tabname
表名,区分大小写。
-
colname
列名,区分大小写。
- TABLE_STATS_SHOW
根据模式名,表名获得该表的统计信息。用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。
语法如下:
PROCEDURE TABLE_STATS_SHOW (
OWNNAME IN VARCHAR(128),
TABNAME IN VARCHAR(128)
);
表的统计信息,格式如下表 21.3:
名称 | 解释 |
---|---|
NUM_ROWS | 表的总行数 |
LEAF_BLOCKS | 总的页数 |
LEAF_USED_BLOCKS | 已经使用的页数 |
参数详解
-
ownname
模式名,区分大小写。
-
tabname
表名,区分大小写。
- INDEX_STATS_SHOW
根据模式名,索引名获得该索引的统计信息。用于经过 GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS 收集之后展示。返回两个结果集:一个是索引的统计信息;另一个是直方图的统计信息。
语法如下:
PROCEDURE INDEX_STATS_SHOW (
OWNNAME IN VARCHAR(128),
INDEXNAME IN VARCHAR(128)
);
索引的统计信息和直方图的统计信息,格式分别如下表 21.4、表 21.5:
名称 | 解释 |
---|---|
BLEVEL | B_Tree 的层次 |
LEAF_BLOCKS | 页数 |
DISTINCT_KEYS | 不同样本的个数 |
CLUSTERING_FACTOR | 聚集因子,表示索引的数据分布与物理分布之间的关系,暂不支持 |
NUM_ROWS | 行数 |
SAMPLE_SIZE | 样本容量 |
名称 | 解释 |
---|---|
OWNER | 模式名 |
NAME | 索引名 |
COLUMN_NAME | 列名 |
HISTOGRAM | 直方图类型 |
ENDPOINT_VALUE | 样本值 |
ENDPOINT_HEIGHT | 对于频率直方图,样本值的个数;对于等高直方图,小于样本值大于前一个样本值的个数。 |
ENDPOINT_KEYGHT | 对于频率直方图无效;对于等高直方图,样本值的个数。 |
ENDPOINT_DISTINCT | 对于频率直方图无效;对于等高直方图,小于样本值大于前一个样本值之间不同样本的个数。 |
参数详解
-
ownname
模式名,区分大小写。
-
INDEXname
索引名,区分大小写。
- GATHER_TABLE_STATS
根据设定的参数,收集表、表中的列和表上的索引的统计信息。其中,对于表,只搜集表的总行数、总的页数、已经使用的页数等基本信息。
语法如下:
PROCEDURE GATHER_TABLE_STATS (
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
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,
STATOWN VARCHAR DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
ownname
模式名,区分大小写。
-
tabname
表名,区分大小写。
-
partname
分区表名,缺省为 NULL,区分大小写。
-
estimate_percent
收集的百分比,范围为 0.000001~100,缺省为系统自定。
-
block_sample
保留参数,是否使用随机块代替随机行,缺省为 TRUE。
-
method_opt
控制列的统计信息集合和直方图的创建的格式,缺省为 FOR ALL COLUMNS SIZE AUTO。其中 BLOB、IMAGE、LONGVARBINARY、CLOB、TEXT、LONGVARCHAR、BOOLEAN 类型不能被收集。格式选项如下:
FOR ALL [INDEXED | HIDDEN] COLUMNS [<size_clause>]<size_clause>::= SIZE {INTEGER | REPEAT | AUTO | SKEWONLY}
或者
FOR COLUMNS[<size clause>] <<column_name>|[<size_clause>]>{,<column_name | [<size_clause>]>}
各参数解释如下:
-
INDEXED | HIDDEN 表示只统计索引或者隐藏的列,缺省为都统计
-
integer 直方图的桶数,取值范围 1~10000
-
REPEAT 只统计已经有直方图的列
-
AUTO 根据数据分布和工作量自动决定统计直方图的列
-
SKEWONLY 根据数据分布决定统计直方图的列
-
DEGREE 收集的并行度,缺省为 1。
-
GRANULARITY 收集的粒度,默认为 Auto;GRANULARITY 可选参数如下:AUTO | DEFAULT |ALL | PARTITION | SUBPARTITION | GLOBAL | GLOBAL AND PARTITION。
各参数解释如下:
◆ALL 收集全部的统计信息(subpartition,partition和global)
◆AUTO 缺省值,根据分区的类型来决定如何收集
◆DEFAULT 和auto功能相同
◆GLOBAL 收集global表的统计信息
◆GLOBAL AND PARTITION 收集GLOBAL 和 PARTITION表的统计信息
◆PARTITION 收集PARTITION表的统计信息
◆SUBPARTITION收集subpartition表的统计信息。当子分区表个数超过50时,因为采用跳跃采样,所以统计信息会有误差。即使采样率为100,也还是会有误差。可以通过减少统计层次的方式降低这种误差,即将granularity=
>'subpartition'修改成 granularity=>'GLOBAL AND PARTITION'或 granularity=>'GLOBAL'。
-
cascade
是否收集索引信息,TRUE 或 FALSE,默认为 TRUE。当表对象类型为 HUGE 表时,CASCADE 参数应置为 FALSE。
-
stattab
统计信息存放的表,默认为 NULL。
-
statid
统计信息的 ID,默认为 NULL。
-
statown
统计信息的模式,默认为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。NO_INVALIDATE 可选参数如下:TRUE | FALSE | DBMS_STATS.AUTO_INVALIDATE。
各参数解释如下:
◆ TRUE 游标不失效,原有的执行计划保持原状态
◆ FALSE 游标失效,移除原有的相关执行计划
◆ DBMS_STATS.AUTO_INVALIDATE 和 TRUE 用法相同
-
force
保留参数,是否强制收集统计信息,默认为 FALSE。
- GATHER_INDEX_STATS
根据设定的参数,收集索引的统计信息。
语法如下:
PROCEDURE GATHER_INDEX_STATS (
OWNNAME VARCHAR(128),
INDNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
ESTIMATE_PERCENT DOUBLE DEFAULT TO_ESTIMATE_PERCENT_TYPE(GET_PREFS('ESTIMATE_PERCENT')),
STATTAB VARCHAR DEFAULT NULL,
STATID VARCHAR DEFAULT NULL,
STATOWN VARCHAR DEFAULT NULL,
DEGREE INT DEFAULT TO_DEGREE_TYPE(GET_PREFS('DEGREE')),
GRANULARITY VARCHAR DEFAULT GET_PREFS('GRANULARITY'),
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
ownname
模式名,区分大小写。
-
INDname
索引名,区分大小写。
-
partname
分区索引名,缺省为 NULL,区分大小写。
-
estimate_percent
收集的百分比,取值范围为 0.000001~100,默认系统自定。
-
stattab
保留参数,统计信息存放的表,缺省为 NULL。
-
statid
保留参数,统计信息的 ID,缺省为 NULL。
-
statown
保留参数,统计信息的模式,缺省为 NULL。
-
degree
保留参数,收集的并行度,缺省为 1。
-
granularity
保留参数,收集的粒度,缺省为 ALL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
- 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。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
-
obj_filter_list
存放过滤条件的模式名、表名和子表名,缺省为 NULL。
- DELETE_TABLE_STATS
根据设定参数,删除与表相关对象的统计信息。
语法如下:
PROCEDURE DELETE_TABLE_STATS (
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR DEFAULT NULL,
STATID VARCHAR DEFAULT NULL,
CASCADE_PARTS BOOLEAN DEFAULT TRUE,
CASCADE_COLUMNS BOOLEAN DEFAULT TRUE,
CASCADE_INDEXES BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
ownname
模式名,区分大小写。
-
tabname
表名,区分大小写。
-
partname
分区表名,缺省为 NULL,区分大小写。
-
stattab
保留参数,统计信息存放的表,缺省为 NULL。
-
statid
保留参数,统计信息的 ID,缺省为 NULL。
-
cascade_parts
是否级联删除分区表信息,缺省为 TRUE。
-
cascade_columns
是否级联删除表中列的信息,取值 TRUE 或 FALSE。缺省为 TRUE。
-
cascade_indexes
是否级联删除表的索引信息,取值 TRUE 或 FALSE。缺省为 TRUE。
-
statown
保留参数,统计信息的模式,缺省为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
- DELETE_SCHEMA_STATS
根据设定参数,删除模式下对象的统计信息。
语法如下:
PROCEDURE DELETE_SCHEMA_STATS (
OWNNAME VARCHAR(128),
STATTAB VARCHAR DEFAULT NULL,
STATID VARCHAR DEFAULT NULL,
STATOWN VARCHAR DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
ownname
模式名,区分大小写。
-
stattab
保留参数,统计信息存放的表,缺省为 NULL。
-
statid
保留参数,统计信息的 ID,缺省为 NULL。
-
statown
保留参数,统计信息的模式,缺省为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
- DELETE_INDEX_STATS
根据设定参数,删除索引的统计信息。
语法如下:
PROCEDURE DELETE_INDEX_STATS (
OWNNAME VARCHAR(128),
INDNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR DEFAULT NULL,
STATID VARCHAR DEFAULT NULL,
CASCADE_PARTS BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
ownname
模式名,区分大小写。
-
indname
索引名,区分大小写。
-
partname
分区表名,缺省为 NULL,区分大小写。
-
stattab
保留参数,统计信息存放的表,缺省为 NULL。
-
statid
保留参数,统计信息的 ID,缺省为 NULL。
-
cascade_parts
是否级联删除分区表信息,TRUE 或 FALSE。缺省为 TRUE。
-
statown
保留参数,统计信息的模式,缺省为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
- DELETE_COLUMN_STATS
根据设定参数,删除列的统计信息。
语法如下:
PROCEDURE DELETE_COLUMN_STATS (
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
COLNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR DEFAULT NULL,
STATID VARCHAR DEFAULT NULL,
CASCADE_PARTS BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE,
COL_STAT_TYPE VARCHAR DEFAULT 'ALL'
);
参数详解
-
ownname
模式名,区分大小写。
-
TABname
表名,区分大小写。
-
colname
列名,区分大小写。
-
partname
分区表名,默认为 NULL,区分大小写。
-
stattab
保留参数,统计信息存放的表,缺省为 NULL。
-
statid
保留参数,统计信息的 ID,缺省为 NULL。
-
cascade_parts
是否级联删除分区表信息,TRUE 或 FALSE。缺省为 TRUE。
-
statown
保留参数,统计信息的模式,缺省为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
保留参数,是否强制收集统计信息,缺省为 FALSE。
-
col_stat_type
保留参数,是否只删除直方图信息,缺省为 ALL。
- UPDATE_ALL_STATS
更新已有的统计信息。
语法如下:
PROCEDURE UPDATE_ALL_STATS ();
- CONVERT_RAW_VALUE
把二进制流转换为指定类型。
语法如下:
PROCEDURE CONVERT_RAW_VALUE (
I_RAW VARBINARY(8188),
M_N OUT NUMBER
);
PROCEDURE CONVERT_RAW_VALUE (
I_RAW VARBINARY(8188),
M_N OUT DATETIME
);
PROCEDURE CONVERT_RAW_VALUE (
I_RAW VARBINARY(8188),
M_N OUT VARCHAR(8188)
);
PROCEDURE CONVERT_RAW_VALUE (
I_RAW VARBINARY(8188),
M_N OUT DOUBLE
);
参数详解
-
I_RAW
输入参数,VARBINARY 类型的最大或最小值,期望来源于 SYSSTATS 表的 V_MAX 或 V_MIN 列。
-
M_N
输出参数,由 I_RAW 转换而来,支持 NUMBER、DATETIME、VARCHAR(8188)、DOUBLE 类型。
- CREATE_STAT_TABLE
根据用户指定的名称创建一个增加了前缀的临时统计信息表,用于保存待导出的统计信息。统计信息保存到该表中后,可以使用 DM 的数据导入导出工具进行跨实例导入导出。
创建的统计信息表需要通过包方法 DROP_STAT_TABLE 进行删除,不支持使用 DROP TABLE 语句删除。
语法如下:
PROCEDURE CREATE_STAT_TABLE (
STATOWN VARCHAR(128),
STATTAB VARCHAR(128),
TABLESPACE VARCHAR(128) DEFAULT NULL,
GLOBAL_TEMPORARY BOOLEAN DEFAULT FALSE
);
参数详解
-
statown
统计信息表的模式名,区分大小写。
-
stattab
统计信息表名,区分大小写,系统实际创建的表会增加前缀。
-
tablespace
表空间名,默认为 NULL,区分大小写。
-
global_temporary
是否创建为会话级的全局临时表。
- DROP_STAT_TABLE
删除统计信息表。
语法如下:
PROCEDURE DROP_STAT_TABLE (
STATOWN VARCHAR(128),
STATTAB VARCHAR(128)
);
参数详解
-
statown
统计信息表的模式名,区分大小写。
-
stattab
创建时指定的统计信息表名,区分大小写。
- EXPORT_TABLE_STATS
把目标表的统计信息导出到指定统计信息表中。
语法如下:
PROCEDURE EXPORT_TABLE_STATS(
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR(128),
STATID VARCHAR(128) DEFAULT '',
CASCADE BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR(128) DEFAULT NULL,
STAT_CATEGORY VARCHAR(128) DEFAULT NULL
);
参数详解
-
OWNNAME
目标表的模式名,区分大小写。
-
TABNAME
目标表名,区分大小写。
-
PARTNAME
目标表分区名,如果不指定分区,则一起导出所有子表的统计信息,缺省为 NULL。
-
STATTAB
统计信息表名,区分大小写。
-
STATID
由用户指定的统计信息标识名,缺省认为空字符串。
-
CASCADE
是否连列和索引的统计信息一起导出,缺省为 TRUE
-
STATOWN
统计信息表的模式名,区分大小写,缺省为 NULL。
-
STAT_CATEGORY
仅保留参数以兼容 Oracle,功能暂未实现。
- IMPORT_TABLE_STATS
把统计信息表中的统计信息导入到目标表中。
语法如下:
PROCEDURE IMPORT_TABLE_STATS(
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PARTNAME VARCHAR(128) DEFAULT NULL,
STATTAB VARCHAR(128),
STATID VARCHAR(128) DEFAULT '',
CASCADE BOOLEAN DEFAULT TRUE,
STATOWN VARCHAR(128) DEFAULT NULL,
NO_INVALIDATE BOOLEAN DEFAULT TO_NO_INVALIDATE_TYPE(GET_PREFS('NO_INVALIDATE')),
FORCE BOOLEAN DEFAULT FALSE,
STAT_CATEGORY VARCHAR(128) DEFAULT NULL
);
参数详解
-
OWNNAME
目标表的模式名,区分大小写。
-
TABNAME
目标表名,区分大小写。
-
PARTNAME
目标表分区名,如果不指定分区,则一起导入所有子表的统计信息,缺省为 NULL。
-
STATTAB
统计信息表名,区分大小写。
-
STATID
由用户指定的统计信息标识名,缺省为空字符串。
-
CASCADE
是否连列和索引的统计信息一起导出,缺省为 TRUE
-
STATOWN
统计信息表的模式名,区分大小写,缺省为 NULL。
-
no_invalidate
是否让依赖游标失效,缺省为 TRUE。具体用法请参考 GATHER_TABLE_STATS 方法的 NO_INVALIDATE 参数。
-
force
仅保留参数以兼容 Oracle,功能暂未实现。
-
stat_category
仅保留参数以兼容 Oracle,功能暂未实现。
- SET_TABLE_PREFS
该过程设置普通用户表的静态属性值。
语法如下:
PROCEDURE SET_TABLE_PREFS (
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
PPNAME VARCHAR(128),
PVALUE VARCHAR(128)
);
参数详解
-
OWNNAME
模式名,不区分大小写。
-
TABNAME
表名,不区分大小写。
-
ppname
属性名,不区分大小写。可以取 STALE_PERCENT、estimate_percent、method_opt、degree、granularity、cascade、no_invalidate、PREFERENCE_OVERRIDES_PARAMETER 和 MONITOR_MODIFICATIONS_FLAG。
STALE_PERCENT 决定表的统计信息过时且需要重收集统计信息时表的行数百分比。有效取值范围是非负数,缺省值是 10,即一张表有超过 10% 的数据改变则可认为统计信息过时。取值范围[0,100],该值置为 0 时,则认为需要将该表的 STALE_PERCENT 的属性值消除。
PREFERENCE_OVERRIDES_PARAMETER 决定是否用静态属性值覆盖参数的输入值。为 TRUE 时,忽略参数的输入值,使用对应的属性值;为 FALSE 时,使用参数的输入值。
MONITOR_MODIFICATIONS_FLAG 决定是否对表进行数据变化监控。为 TRUE 时,监控表的数据变化;为 FALSE 时,不监控表的数据变化。默认值为 FALSE。
其他属性值范围参考 GATHER_TABLE_STATS 中的参数描述。
-
pvalue
属性名对应的属性值,不区分大小写。
- GET_PREFS
该函数返回普通用户表的静态属性值。
语法如下:
FUNCTION GET_PREFS(
PPNAME VARCHAR(128),
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128)
)RETURN VARCHAR;
参数详解
-
OWNNAME
模式名,不区分大小写。
-
TABNAME
表名,不区分大小写。
-
PPNAME
属性名,不区分大小写。可以取 STALE_PERCENT、estimate_percent、method_opt、degree、granularity、cascade、no_invalidate、PREFERENCE_OVERRIDES_PARAMETER 和 MONITOR_MODIFICATIONS_FLAG。
- COPY_TABLE_STATS
该过程将基表中指定源分区的统计信息复制到同一基表的指定的目的分区,目的分区原有统计信息将被覆盖。
语法如下:
PROCEDURE COPY_TABLE_STATS(
OWNNAME VARCHAR(128),
TABNAME VARCHAR(128),
SRCPARTNAME VARCHAR(128),
DSTPARTNAME VARCHAR(128),
SCALE_FACTOR VARCHAR(128) DEFAULT '1',
FORCE BOOLEAN DEFAULT FALSE
);
参数详解
-
OWNNAME
模式名,区分大小写。
-
TABNAME
基表名,区分大小写。
-
SRCPARTNAME
源分区名,区分大小写。
-
DSTPARTNAME
目标分区名,区分大小写。
-
SCALE_FACTOR
仅保留参数以兼容 Oracle,功能暂未实现。
-
FORCE
仅保留参数以兼容 Oracle,功能暂未实现。
21.3 约束
以下对象不支持统计信息:
- 外部表、DBLINK 远程表、动态视图表、记录类型数组所用的临时表。
- 所在表空间为 OFFLINE 的对象。
- 位图索引,位图连接索引、虚索引、全文索引、空间索引、数组索引、无效的索引。
- BLOB、IMAGE、LONGVARBINARY、CLOB、TEXT、LONGVARCHAR、自定义类型列和空间类型列等列类型。
21.4 举例说明
使用包内的过程和函数之前,如果还未创建过系统包,请先调用系统过程创建系统包。
SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_STATS');
SET SERVEROUTPUT ON; //PRINT需要设置这条语句,才能打印出消息
例 1 收集模式 PERSON 下表 ADDRESS 的统计信息,并打印收集的表信息。
BEGIN
declare
objtab DBMS_STATS.ObjectTab;
obj_filter_list DBMS_STATS.ObjectTab;
BEGIN
obj_filter_list(0).ownname = 'PERSON';
obj_filter_list(0).objtype = 'TABLE';
obj_filter_list(0).objname = 'ADDRESS';
DBMS_STATS.GATHER_SCHEMA_STATS(
'PERSON',
1.0,
FALSE,
'FOR ALL COLUMNS SIZE AUTO',
1,
'AUTO',
TRUE,
NULL,
NULL,
'GATHER',
objtab,
NULL,
TRUE,
TRUE,
obj_filter_list
);
print objtab.count;
FOR i IN 0..objtab.count-1 LOOP
print objtab(i).ownname;
print objtab(i).objtype;
print objtab(i).objname;
print objtab(i).partname;
print objtab(i).subpartname;
print '-------- ';
end loop;
END;
END;
/
打印结果如下:
1
PERSON
TABLE
ADDRESS
NULL
NULL
例 2 获得 PERSON 模式下表 ADDRESS 中列 ADDRESSID 的统计信息。
DBMS_STATS.COLUMN_STATS_SHOW('PERSON','ADDRESS','ADDRESSID');
返回结果集 1:
NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
16 1 16
0 16 16 FREQUENCY
结果集 2:
OWNER TABLE_NAME COLUMN_NAME HISTOGRAM ENDPOINT_VALUE ENDPOINT_HEIGHT
ENDPOINT_KEYHEIGHT ENDPOINT_DISTINCT
PERSON ADDRESS ADDRESSID FREQUENCY 1 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 2 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 3 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 4 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 5 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 6 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 7 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 8 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 9 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 10 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 11 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 12 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 13 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 14 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 15 1 NULL NULL
PERSON ADDRESS ADDRESSID FREQUENCY 16 1 NULL NULL
例 3 删除 PERSON 模式下表 ADDRESS 的统计信息。
BEGIN
DBMS_STATS.DELETE_TABLE_STATS ('PERSON', 'ADDRESS');
END;
/
例 4 自动收集用户表的统计信息。
第一步,打开监控。
设置 INI 参数 AUTO_STAT_OBJ 为 1 或 2。1:对所有表进行监控;2:只对用户通过 DBMS_STATS. SET_TABLE_PREFS 设置过 STALE_PERCENT 属性的表对象进行监控。如果 AUTO_STAT_OBJ=2,需进一步使用 DBMS_STATS.SET_TABLE_PREFS 设置 STALE_PERCENT 属性。
用 AUTO_STAT_OBJ=1 打开对 T 表的监控。
create table t(a int);
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',1);
用 AUTO_STAT_OBJ=2 打开对 T 表的监控。
create table t(a int);
SP_SET_PARA_VALUE(1,'AUTO_STAT_OBJ',2);
DBMS_STATS.SET_TABLE_PREFS('SYSDBA','T','STALE_PERCENT',15);
//对修改行数占总行数的比达到STALE_PERCENT要求的对象和总行数为0的对象收集统计信息
下面以 AUTO_STAT_OBJ=2 为例。
第二步,执行统计信息收集操作。自动收集统计信息使用 SP_CREATE_AUTO_STAT_TRIGGER 设置触发器。
SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'11:20', '2021/1/11',0,1);
此处,也可将自动收集方法换成手动收集方法,其它三步用法不变。
第三步,对用户表 T 进行增删改操作。
insert into t select level connect by level<=20;
commit;
第四步,通过系统表 SYSSTATTABLEIDU 和动态视图 V$AUTO_STAT_TABLE_IDU 查看监控信息,通过系统表 SYSSTATS 查看统计信息。
打开监控后并执行统计信息收集操作之后才能查看到 SYSSTATTABLEIDU 和 SYSSTATS 的变化。SYSSTATTABLEIDU 为 AUTO_STAT_OBJ 等于 1 或 2 时对上一次的统计信息的监控。V$AUTO_STAT_TABLE_IDU 为 AUTO_STAT_OBJ 等于 1 时的统计信息实时监控,不需要执行收集统计信息操作也能查看。
下面以 AUTO_STAT_OBJ=2 为例。
查看监控信息:
//在触发器触发之后查看
SQL> SELECT * FROM SYSSTATTABLEIDU;
//查询结果如下:
行号 ID TOTAL_ROWS INSERT_ROWS DELETE_ROWS UPDATE_ROWS LAST_STAT_DT MONITOR_FLAG RSVD1 TRUNCATED
---------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------------- ------------ ---------- -----------
1 1074 20 0 0 0 2022-01-11 14:37:02.000000 2 NULL 0
查看统计信息:
SQL> SELECT * FROM SYSSTATS WHERE ID=1074;
行号 ID COLID T_FLAG T_TOTAL N_SAMPLE N_DISTINCT N_NULL V_MIN V_MAX BLEVEL N_LEAF_PAGES N_LEAF_USED_PAGES CLUSTER_FACTOR N_BUCKETSM
---------- ----------- ----------- ------ -------------------- -------------------- -------------------- -------------------- ---------- ---------- ----------- -------------------- -------------------- -------------- -----------
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COL_AVG_LEN LAST_GATHERED INFO1 INFO2
----------- -------------------------- ---------- ----------
1 1074 -1 T 20 0 0 0 NULL NULL 0 1 1 0 0
0x0000FFFF00000700
-1 2022-01-11 14:37:02.279000 NULL NULL
行号 ID COLID T_FLAG T_TOTAL N_SAMPLE N_DISTINCT N_NULL V_MIN V_MAX BLEVEL N_LEAF_PAGES N_LEAF_USED_PAGES CLUSTER_FACTOR N_BUCKETS
---------- ----------- ----------- ------ -------------------- -------------------- -------------------- -------------------- ---------- ---------- ----------- -------------------- -------------------- -------------- -----------
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COL_AVG_LEN LAST_GATHERED INFO1 INFO2
----------- -------------------------- ---------- ----------
2 1074 0 C 20 20 20 0 0x01000000 0x14000000 0 1 1 0 20
0x1400010008000700010000000100000001000000020000000100000003000000010000000400000001000000050000000100000006000000010000000700000001000000080000000100000009000000010000000A000000010000000B000000010000000C000000010000000D000000010000000E000000010000000F00000001000000100000000100000011000000010000001200000001000000130000000100000014000000
4 2022-01-11 14:37:02.307000 NULL NULL
如果用户想进一步对监控统计信息进行收集,可以参考《DM8 系统管理员手册》的 22.5 统计信息章节 SYSDBA.GET_AUTO_STAT_INFO_FUNC 用法。