Oracle Database 12c DBMS_STATS详解(二)

设置或者获取统计信息
可以使用如下的子程序来存储或者取回单独的与列相关的、与索引相关的以及与表相关的统计信息:

PREPARE_COLUME_VALUES
PREPARE_COLUMN_VALUES_NVARCHAR
PREPARE_COLUMN_VALUES_ROWID

SEED_COL_USAGE
SET_INDEX_STATS
SET_SYSTEM_STATS
SET_TABLE_STATS

GET_COLUMN_STATS
GET_INDEX_STATS
GET_SYSTEM_STATS
GET_TABLE_STATS


对于用户自定义的统计信息,如果提供了如下信息,SET_*_STATS的某些特殊版本,将会把统计信息存储在系统字典表中,也可以存储在用户自定义的统计信息表中:
用户自定义的统计信息;
统计信息类型的所有者;
统计信息类型的名称。

用户自定义的统计信息和对应的统计信息类型,将被插入到系统字典表USTATS$。在自定义统计信息的时候,你也可以不指定统计信息的类型名称。
GET_*_STATS过程的特殊版本可以获得指定schema对象的用户自定义的统计信息、统计信息类型所有者以及名称,并将它们作为输出参数进行返回。如果用户自定义的统计信息没有收集,则返回null。

删除统计信息
对于给定的schema对象,如下的DELETE_*过程既可以删除用户自定义的统计信息,也可以删除系统标准的统计信息:

DELETE_COLUMN_STATS
DELETE_DATABASE_STATS
DELETE_DICTIONARY_STATS
DELETE_FIXED_OBJECTS_STATS
DELETE_INDEX_STATS
DELETE_SCHEMA_STATS
DELETE_SYSTEM_STATS
DELETE_TABLE_STATS

需要注意的是,DELETE_TABLE_STATS,DELETE_DICTIONARY_STATS,DELETE_DATABASE_STATS和DELETE_SCHEMA_STATS都有一个stat_category参数,该参数指定了哪种统计信息将要被删除。它可以接受用逗号分隔的多个值。该参数支持的值为OBJECT_STATS(表统计信息,列统计信息和索引统计信息)和SYNOPSES(当统计信息被增量维护时创建的辅助统计信息)。该参数默认值为“OBJECT_STATS,SYNOPSES”。

传输统计信息
可以使用如下过程来创建和丢弃用户自定义统计信息表:
CREATE_STAT_TABLE
DROP_STAT_TABLE

使用如下过程来传输统计信息:
从系统字典表到用户自定义表(EXPORT_*)
从用户自定应表到系统字典表(IMPORT_*)

EXPORT_COLUMN_STATS
EXPORT_DATABASE_STATS
EXPORT_DICTIONARY_STATS
EXPORT_FIXED_OBJECTS_STATS
EXPORT_INDEX_STATS
EXPORT_SCHEMA_STATS
EXPORT_SYSTEM_STATS
EXPORT_TABLE_STATS

IMPORT_COLUMN_STATS
IMPORT_DATABASE_STATS
IMPORT_DICTIONARY_STATS
IMPORT_FIXED_OBJECTS_STATS
IMPORT_INDEX_STATS
IMPORT_SCHEMA_STATS
IMPORT_SYSTEM_STATS
IMPORT_TABLE_STATS

需要注意的是,oracle不支持在不同字符集的数据库之间传输统计信息。

锁定或者解锁统计信息
可以使用如下过程对一个对象上的统计信息进行锁定或者解锁:

LOCK_PARTITION_STATS
LOCK_SCHEMA_STATS
LOCK_TABLE_STATS

UNLOCK_PARTITION_STATS
UNLOCK_SCHEMA_STATS
UNLOCK_TABLE_STATS

LOCK_*过程会冻结当前的统计信息或者保持这些统计信息不会被修改。当一个表的统计信息被锁定,那么所有依赖于该表的统计信息,包括表的统计信息、列的统计信息、直方图,以及依赖于该表的索引的统计信息,都将被视为处于锁定状态。

恢复或者清除历史统计信息
使用如下的过程可以将统计信息恢复到某一特定的时间戳。当新收集的统计信息产生了非最优的执行计划并且dba想把统计信息恢复到以前状态时(可以产生最优执行计划),这些过程就很有用处。
RESET_GLOBAL_PREF_DEFAULTS
RESTORE_DATABASE_STATS
RESTORE_DICTIONARY_STATS
RESTORE_FIXED_OBJECTS_STATS
RESTORE_SCHEMA_STATS
RESTORE_SYSTEM_STATS
RESTORE_TABLE_STATS

无论何时,只要系统字典表中的统计信息被修改,那么旧版本的统计信息就会被自动保存起来以备将来恢复之用。旧版本的统计信息会以一定的时间间隔进行自动清除。该间隔基于系统设置的历史统计信息保留时间和系统最近收集统计信息的时间来指定。保留时间可以用ALTER_STATS_HISTORY_RETENTION过程来设置。

其他与恢复统计信息相关的过程如下:
PURGE_STATS:该过程允许你手工清理旧版本的统计信息。
GET_STATS_HISTORY_RETENTION:获取当前历史统计信息保留时间。
GET_STATS_HISTORY_AVAILABILITY:获取可用的最老的统计信息的时间戳。用户无法将统计信息恢复到比该时间戳更老的时间戳上去。
RESTORE_*过程不支持用户自定义的统计信息。

用户自定义统计信息
DBMS_STATS包也支持对用户自定应统计信息的操作。当用户在一个域索引或者列上创建了自定义的统计信息,并且指定了这些统计信息的类型(通过使用associate 子句),那么对该索引或者列的操作,也将会影响到这些用户自定义的统计信息。例如,当你使用GET_INDEX_STATS并且调用用户自定义的统计信息收集方法来为一个域索引(已经指定了统计信息类型)收集统计信息的时候。同样的,删除、传输、导入以及导出操作,也将会影响到用户自定义的统计信息。
SET_*和GET_*也可以操作用户自定义的列和索引统计信息,这需要通过使用SET和GET接口的特殊版本来实现。
EXPORT_*,IMPORT_*和RESTORE_*不支持用户自定义统计信息。

pending统计信息
默认情况下,DBMS_STATS收集统计信息并将其存储到系统字典表当中。用户可以使用SET_*_PREFS过程,并将过程中的PUBLISH选项设置为FALSE,这样就可以把统计信息存储到系统的私有区域当中而非系统字典表当中。PUBLISH选项的默认值为TRUE。存储在系统私有区域中的统计信息不会被cbo使用,除非optimizer_use_pending_statistics参数被设置为TRUE。该参数的默认值为FALSE。并且可以在会话或者系统级别改变这个参数。通过在一个会话当中pending统计信息,用户可以核实新统计信息对查询计划的影响。
pendind统计信息提供了一种机制,使得我们可以在新统计信息被使用之前,就核实它们对查询计划产生的影响。下面是两个相关的场景:
1,将pending的统计信息导出(使用EXPORT_PENDING_STATS)到一个测试系统中,然后执行查询来检验性能或者执行计划。
2,在一个会话中将optimizer_use_pending_statistics参数设置为TRUE,当pending的统计信息已经被收集后,执行查询来检验性能或者执行计划。
一旦这些pending的统计信息对性能或者执行计划的影响被核实,如果结果可被接受,则可以发布这些统计信息(使用PUBLISH_PENDING_STATS);否则就删除这些统计信息(使用DELETE_PENDING_STATS)。
pending的统计信息可以被发布、导出,或者删除。如下的过程提供了管理pending的统计信息的功能:
DELETE_PENDING_STATS
EXPORT_PENDING_STATS
PUBLISH_PENDING_STATS

比较统计信息
DIFF_TABLE_STATS_*可以用来比较来自不同源头的表的统计信息。这些统计信息可以来自于:
两个不同的用户自定义的统计信息表;
存储在一个用户自定义的统计信息表中但是用statid区分的不同的统计信息集合;
一个用户自定义的统计信息表和系统的历史字典表;
pending的统计信息。
这些函数也可以比较依赖对象的统计信息(索引,列,分区等)。如果这些对象的来自两个不同的源头的统计信息之间的差异超过某一特定的阙值,这些函数也可以显示这些统计信息。该阙值可以指定为这些函数的一个参数,其默认值为10%。来自第一个源头(stattab1或者time1)的统计信息会被作为基准,从而来计算来自其他源头的统计信息与其的差异百分比。

扩展统计信息
DBMS_STATS也允许你为某一些列的组合或者表达式收集统计信息。这样的统计信息,被称之为“扩展统计信息”。
当优化器来计算不同列之间的相关性时,这样的统计信息就会很有用处。例如,如果一个查询有c1=1 and c2=1这样的谓词,那么基于列(c1,c2)的扩展统计信息就可以被优化器使用,从而来评估该谓词的联合选择率。基于表达式的扩展统计信息,则会被优化器用来评估使用这些表达式的谓词的选择率。这些扩展统计信息与列统计信息很相似,DBMS_STATS中使用列名作为参数的过程,也可以使用这些扩展统计信息的名称作为参数。
相关子程序如下:
CREATE_EXTENDED_STATS
DROP_EXTENDED_STATS
SHOW_EXTENDED_STATS_NAME

被丢弃的子程序
如下的子程序在11.2中被废弃:
GET_PARAM 由GET_PREFS代替
SET_PARAM 由SET_GLOBAL_PREFS代替
RESET_PARAM_DEFAULTS 由RESET_GLOBAL_PREF_DEFAULTS代替

数据结构
DBMS_STATS定义了如下的记录类型
记录类型
STAT_REC 记录类型
STAT_REC 记录类型
当用户打算手工设置列统计信息时,可以使用该记录类型。该记录类型的字段可以用来设置列的最大与最小值,就跟列的直方图一样。
语法
TYPE STATREC IS RECORD (
epc NUMBER,
minval RAW(2000),
maxval RAW(2000),
bkvals NUMARRAY,
novals NUMARRAY,
chvals CHARARRAY,
eavals RAWARRAY,
rpcnts NUMARRAY,
eavs NUMBER);

字段
字段 说明
epc 直方图中的bucket数目
minval 最小值
maxval 最大值
bkvals bucket数目的数组
novals 正常结束点值的数组
chvals 废弃的结束点值的数组
eavals 结束点实际值的数组
rpcnts 结束点值频率的数组
eavs 用来指示直方图中是否需要实际结束点值的数字,如果使用
PREPARE_COLUMN_VALUES过程,该字段将会被自动填充。

DBMS_STATS 子程序概述
(略)

Posted in Oracle.