#DBMS_STATS

DBMS_STATS包提供了一组变量、函数和存储过程，用于管理优化器的统计信息。

# 手动设置统计信息

# SET_TABLE_STATS

DBMS_STATS.SET_TABLE_STATS (
	ownname    VARCHAR,
	tabname    VARCHAR,
	partname   VARCHAR,
	numrows    NUMBER,
	numblks    NUMBER,
	avgrlen    NUMBER
);

此存储过程用于手动设置表/AC或者分区的统计信息，旧的统计信息将会被覆盖，主要在测试需要或者统计信息修正场景中使用。

参数	描述
ownname	用户名
tabname	表/AC名
partname	分区名
numrows	表/AC或者分区的行数
numblks	表/AC或者分区的数据块数
avgrlen	表/AC或者分区的平均行长度

示例

BEGIN
    DBMS_STATS.SET_TABLE_STATS( 'SALES', 'SALES_INFO', 'P_SALES_INFO_1', 10, 1, 15);
END;
/

# SET_INDEX_STATS

DBMS_STATS.SET_INDEX_STATS (
    ownname      VARCHAR,
    indname      VARCHAR,
    partname     VARCHAR,
    numrows      NUMBER,
    numlblks     NUMBER,
    numdist      NUMBER,
    numfirstdist NUMBER,
    avglblk      NUMBER,
    avgdblk      NUMBER,
    clstfct      NUMBER,
    indlevel     NUMBER
);

此存储过程用于手动设置索引或者分区的统计信息，旧的统计信息将会被覆盖，主要在测试需要或者统计信息修正场景中使用。

参数	描述
ownname	用户名
indname	索引名
partname	索引分区名
numrows	索引键个数
numlblks	叶子块个数
numdist	索引键唯一值个数
numfirstdist	组合索引第一列的键唯一值个数
avglblk	平均每个叶子块包含键的个数
avgdblk	平均每个叶子块指向数据块的个数
clstfct	索引的聚集因子
level	BTree的高度

示例

CREATE INDEX idx_sales_info_2 ON sales_info (year) LOCAL;

SELECT PARTITION_NAME FROM DBA_IND_PARTITIONS
WHERE index_name='IDX_SALES_INFO_2';
PARTITION_NAME                                                   
---------------------------
SYS_P63                                                         
SYS_P64                                                         
SYS_P65  

BEGIN
    DBMS_STATS.SET_INDEX_STATS('SALES', 'IDX_SALES_INFO_2', 'SYS_P63', 100, 10, 10, 1, 10, 10, 1, 1);
END;
/

# SET_COLUMN_STATS

DBMS_STATS.SET_COLUMN_STATS (
    ownname    VARCHAR,
    tabname    VARCHAR,
    colname    VARCHAR
    partname   VARCHAR,
    distinct   NUMBER,
    density    NUMBER,
    nullcnt    NUMBER,
    avgclen    NUMBER,
);

此存储过程用于手动设置表/AC或者分区的列统计信息，旧的统计信息将会被覆盖，主要在测试需要或者统计信息修正场景中使用。

参数	描述
ownname	用户名
tabname	表/AC名
colname	列名
partname	分区名
distinct	列distinct数量
density	列density，取值范围为[0,1]
nullcnt	列NULL值数量
avgclen	平均列长度

示例

BEGIN
  DBMS_STATS.SET_COLUMN_STATS( 'SALES', 'SALES_INFO', 'AMOUNT', 'P_SALES_INFO_1', 10, 0.2, 15, 10);
END;
/

# 删除统计信息

# DELETE_COLUMN_STATS

DBMS_STATS.DELETE_COLUMN_STATS (
    ownname    VARCHAR,
    tabname    VARCHAR,
    colname    VARCHAR
    partname   VARCHAR,
    type       VARCHAR
);

此存储过程用于手动删除列的统计信息，主要在清理不准确的统计信息场景中使用。

参数	描述
ownname	用户名
tabname	表/AC名
colname	列名
partname	分区名
type	删除类型（'ALL'：同时删除column统计信息和直方图，'HISTOGRAM'：只删除直方图）

示例：

BEGIN
    DBMS_STATS.DELETE_COLUMN_STATS('SALES', 'SALES_INFO', 'AMOUNT', 'P_SALES_INFO_1', 'HISTOGRAM ');
END;
/

# 收集统计信息

# GATHER_TABLE_STATS

DBMS_STATS.GATHER_TABLE_STATS (
    ownname             VARCHAR,
    tabname             VARCHAR,
    partname            VARCHAR,
    estimate_percent    NUMBER,
    block_sample        BOOLEAN,
    method_opt          VARCHAR,
    degree              NUMBER,
    granularity         VARCHAR,
    cascade             BOOLEAN
);

此存储过程用于收集表/AC或分区的统计信息。

对于组合分区表，暂不支持指定收集某个分区/子分区，仅收集其GLOBAL级统计信息。

参数	描述
ownname	用户名
tabname	表/AC名
partname	分区名
estimate_percent	采样率，见统计信息选项说明
block_sample	是否采用块级采样
method_opt	列统计信息选项，见统计信息选项说明
degree	并行度，对于大表增大并行度可以提升统计信息收集的效率，见统计信息选项说明
granularity	分区统计粒度，见统计信息选项说明
cascade	是否收集索引统计信息，见统计信息选项说明

示例

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS('SALES', 'SALES_INFO', 'P_SALES_INFO_1', 0.2, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);
END;
/

# GATHER_INDEX_STATS

DBMS_STATS.GATHER_INDEX_STATS (
    ownname             VARCHAR,
    indname             VARCHAR,
    partname            VARCHAR,
    estimate_percent    NUMBER,
    degree              NUMBER,
    granularity         VARCHAR,
);

此存储过程用于收集索引或索引分区的统计信息。

对于组合分区索引，暂不支持指定收集某个分区/子分区，仅收集其GLOBAL级统计信息。

参数	描述
ownname	用户名
indname	索引名
partname	索引分区名，NULL表示统计所有分区
estimate_percent	采样率百分比，见统计信息选项说明
degree	并行度，见统计信息选项说明
granularity	分区统计粒度，见统计信息选项说明

示例

CREATE INDEX idx_sales_info_2 ON sales_info (year) LOCAL;

BEGIN
    DBMS_STATS.GATHER_INDEX_STATS('SALES', 'IDX_SALES_INFO_2', '', 0.2, 2,'ALL');
END;
/

# GATHER_SCHEMA_STATS

DBMS_STATS.GATHER_SCHEMA_STATS (
    ownname           VARCHAR,
    estimate_percent  NUMBER,
    block_sample      BOOLEAN,
    method_opt        VARCHAR,
    degree            NUMBER,
    granularity       VARCHAR,
    cascade           BOOLEAN
);

此存储过程用于收集指定用户下所有对象（表、AC、列、索引）的统计信息。

参数	描述
ownname	用户名
estimate_percent	采样率百分比，见统计信息选项说明
block_sample	是否采用块级采样
method_opt	列统计信息选项，见统计信息选项说明
degree	并行度，见统计信息选项说明
granularity	分区统计粒度，见统计信息选项说明
cascade	是否收集索引统计信息，见统计信息选项说明

exec DBMS_STATS.GATHER_SCHEMA_STATS('SALES', 1, TRUE, 'FOR ALL COLUMNS SIZE AUTO', 1, 'ALL', TRUE);

# GATHER_DATABASE_STATS

DBMS_STATS.GATHER_DATABASE_STATS (
    options             VARCHAR,
    estimate_percent    NUMBER,
    degree              NUMBER,
    method_opt          VARCHAR,
    granularity         VARCHAR,
    cascade             BOOLEAN,
    gather_sys          BOOLEAN
);

此存储过程用于收集数据库的统计信息。

参数	描述
options	见统计信息选项说明
estimate_percent	采样率，见统计信息选项说明
degree	并行度，见统计信息选项说明
method_opt	列统计信息选项，见统计信息选项说明
granularity	分区统计粒度，见统计信息选项说明
cascade	是否收集索引统计信息，见统计信息选项说明
gather_sys	是否收集系统表的统计信息

示例

exec DBMS_STATS.GATHER_DATABASE_STATS('GATHER AUTO', 1, 2,  'FOR ALL COLUMNS SIZE AUTO', 'AUTO', TRUE, FALSE);

# FLUSH_DATABASE_MONITORING_INFO

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

此存储过程用于持久化表/AC的变化信息。

示例

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

# 锁定/解锁统计信息

# LOCK_PARTITION_STATS

DBMS_STATS.LOCK_PARTITION_STATS (
    ownname             VARCHAR,
    tabname             VARCHAR,
    partname            VARCHAR
);

此存储过程用于锁定表中某个表分区的统计信息，锁定后该分区的统计信息不再被修改。

参数	描述
ownname	用户名
tabname	表名
partname	分区名

示例

exec DBMS_STATS.LOCK_PARTITION_STATS('SALES', 'SALES_INFO', 'P_SALES_INFO_1');

# LOCK_TABLE_STATS

DBMS_STATS.LOCK_TABLE_STATS (
    ownname             VARCHAR,
    tabname             VARCHAR
);

此存储过程用于锁定某张表的统计信息，锁定后该表的统计信息不再被修改。

参数	描述
ownname	用户名
tabname	表名

示例

exec DBMS_STATS.LOCK_TABLE_STATS('SALES', 'SALES_INFO');

# LOCK_SCHEMA_STATS

DBMS_STATS.LOCK_PARTITION_STATS (
    ownname             VARCHAR
);

此存储过程用于锁定某个schema下所有表的统计信息，锁定后该schema下所有表的统计信息不再被修改，但该schema下在此之后新建的表并不锁定。

参数	描述
ownname	用户名

示例

exec DBMS_STATS.LOCK_SCHEMA_STATS('SALES');

# UNLOCK_PARTITION_STATS

DBMS_STATS.UNLOCK_PARTITION_STATS (
    ownname             VARCHAR,
    tabname             VARCHAR,
    partname            VARCHAR
);

此存储过程用于解锁表中某个表分区的统计信息。

参数	描述
ownname	用户名
tabname	表名
partname	分区名

示例

exec DBMS_STATS.UNLOCK_PARTITION_STATS('SALES', 'SALES_INFO', 'P_SALES_INFO_1');

# UNLOCK_TABLE_STATS

DBMS_STATS.UNLOCK_TABLE_STATS (
    ownname             VARCHAR,
    tabname             VARCHAR
);

此存储过程用于解锁某张表的统计信息。

参数	描述
ownname	用户名
tabname	表名

示例

exec DBMS_STATS.UNLOCK_TABLE_STATS('SALES', 'SALES_INFO');

# UNLOCK_SCHEMA_STATS

DBMS_STATS.UNLOCK_SCHEMA_STATS (
    ownname             VARCHAR
);

此存储过程用于解锁某个schema下所有表的统计信息。

参数	描述
ownname	用户名

示例

exec DBMS_STATS.UNLOCK_SCHEMA_STATS('SALES');

# 设置统计信息选项

在调用统计信息收集程序时，YashanDB提供一系列选项，如分区粒度、采样率等，供用户指定，以获取更精准可用的数据，这些选项的值可通过如下三种方式设置：

作为收集程序的参数，在调用时指定，此种方式优先级最高。
通过SET_*_PREFS程序进行通用设定，当未指定选项参数，或选项参数为NULL时，系统使用此通用设定值。
当未按上述两种方式指定值时，由系统给出默认值。

其中，某个选项一旦由SET_*_PREFS程序设定，即可通过ALL_TAB_STAT_PREFS、DBA_TAB_STAT_PREFS、USER_TAB_STAT_PREFS视图查看该选项在表上对应的值。

下表列示这些选项：

选项	含义	系统默认值
STALE_PERCENT	统计信息失效的变化率阈值	0.1
CASCADE	收集表的统计信息时，是否一并收集索引的统计信息	FALSE
DEGREE	统计信息收集的并行度	1
ESTIMATE_PERCENT	采样率，取值为0或者在[0.0001-1]之内。当取值为0时，将由数据库自动决定采样率。	1
GRANULARITY	分区表收集策略 1. ALL：收集表和分区 2. GLOBAL：只收集表 3. GLOBAL AND PARTITION：收集表和分区 4. PARTITION：只收集分区 5. SUBPARTITION：保留选项。只收集子分区 6. AUTO：由分区的类型自动决定	GLOBAL
METHOD_OPT	统计信息收集选项 1. FOR ALL COLUMNS [size_clause]：收集所有列的统计信息 2. FOR COLUMNS [column_clause] [size_clause]：收集指定列的统计信息 column_clause：(col1,col2,col3) size_clause：SIZE {integer \| AUTO}用于指定直方图信息 1. integer：直方图的bucket数量，范围为[1, 2048] 2. AUTO：由数据库决定是否生成直方图 SET_SCHEMA_PREFS、SET_DATABASE_PREFS、SET_GLOBAL_PREFS只能设置FOR ALL COLUMNS [size_clause]。	FOR ALL COLUMNS SIZE AUTO
OPTIONS	数据库统计信息收集选项 1. GATHER：收集所有表的统计信息 2. GATHER AUTO：默认选项，由数据库决定收集的表 3. GATHER STALE：只收集无效的统计信息 4. GATHER EMPTY：只收集空的统计信息该选项仅能通过SET_GLOBAL_PREFS设置。	GATHER AUTO

# SET_TABLE_PREFS

DBMS_STATS.SET_TABLE_PREFS (
    ownname             VARCHAR,
    tabname             VARCHAR,
    pname               VARCHAR,
    pvalue              VARCHAR
);

此存储过程用于设置表的统计信息选项，方便用户控制表的统计信息收集、失效等行为。

参数	描述
ownname	用户名
tabname	表名
pname	统计信息选项名
pvalue	统计信息选项值

示例

exec DBMS_STATS.SET_TABLE_PREFS('SALES', 'SALES_INFO', 'DEGREE', '4');

# SET_SCHEMA_PREFS

DBMS_STATS.SET_SCHEMA_PREFS (
    ownname             VARCHAR,
    pname               VARCHAR,
    pvalue              VARCHAR
);

此存储过程用于设置schema下当前所有表的统计信息选项，对后续新建的表不生效。

参数	描述
ownname	用户名
pname	统计信息选项名
pvalue	统计信息选项值

示例

exec DBMS_STATS.SET_SCHEMA_PREFS('SALES', 'DEGREE', '4');

# SET_DATABASE_PREFS

DBMS_STATS.SET_DATABASE_PREFS (
    pname               VARCHAR,
    pvalue              VARCHAR,
    add_sys             BOOLEAN
);

此存储过程用于设置数据库下当前所有表的统计信息选项，对后续新建的表不生效。

参数	描述
pname	统计信息选项名
pvalue	统计信息选项值
add_sys	是否设置SYS用户下表的统计信息选项，默认为FALSE

示例

exec DBMS_STATS.SET_DATABASE_PREFS('DEGREE', '4', FALSE);

# SET_GLOBAL_PREFS

DBMS_STATS.SET_GLOBAL_PREFS (
    pname               VARCHAR,
    pvalue              VARCHAR
);

此存储过程用于设置全局的统计信息选项，对现有没有设置该选项的表和后续新建的表生效。

参数	描述
pname	统计信息选项名
pvalue	统计信息选项值

示例

exec DBMS_STATS.SET_GLOBAL_PREFS('DEGREE', '4');