Update statistics介绍

Update statistics介绍

1、Update statistics是什么？
Update statistics是生成数据库表的统计信息和数据分布信息，并把这些信息记录在系统表中。这些信息为查询优化器生成更加准确的执行计划提供服务。
统计更新不能运行在任何辅助节点上。
Update statistics需要需要内存和磁盘资源来构建数据分布，这两部分资源的来自于：
1、 会话中设置PDQ，会使用PDQ参数中设置的内存信息；
并发运行PDQ其实的有效的，虽然通过nmon监控看到运行统计更新的时候只有一个CPU在很高的使用。但是在PDQ的MEDIUM|HIG的时候会有sort的操作，PDQ可以提高sort部分的速度。
在LOW模式下：
PDQ的值在1-10。10表示所有的分片都会同时进行；5表示50%的分片数并发执行；
在MEDIUM|HIG模式下：
PDQ的值可以大于10。因为会有大量的sort动作，需要PDQ来加速。
2、 DBUPSPACE环境变量来指定系统磁盘空和内存数量，在MEDIUM|HIGH模下使用该空间来构建列的数据分布；
setenv DBUPSPACE 2500:1 设置了2500KB的disk和1MB memory。
默认是1024KB disk+15MB memory。且磁盘空间如果设置的小于1024KB,则仍然使用1024KB。
如果磁盘空间不够大，不允许一次创建多个分布，至少要完成一个分布（即使请求的磁盘空间超过了DBUPSPACE变量指定的空间）。一个表的单个列的生成数据分布的空间要求是DBUPSPACE的最小要求。
2、Update statistics的三种模式
low
默认模式。
LOW模式仅仅更新表、行、页的统计信息。
涉及的系统表有：systables、syscolumns,sysindexes。没有数据分布的信息创建出来。
medium
在MEDIUM模式的时候，数据分布是通过采样一组数据获得的，还需要使用一个你指定的统计信任水平。
你还可以指定一个最小采样的大小。因为MEDIUM样例的大小通常远远小于实际行数的大小，所以比HIGH模式的执行速度更快。
通过抽样获得分布，结果可能会有所不同。
如果Resolution没有指定采样百分比，默认是2.5，它将范围分割成40个区间；如果你没有指定信任水平（confidence），默认是0.95。
涉及的系统表有：systables、syscolumns、sysindexes、sysindices、sysfragments、sysdistrib、sysfragdist。
confidence MEDIUM和HIGH一样,default=0.95 0.8-0.99
percent 在每个分发箱（distribution bin）中样品的平均百分比。MEDIUM=2.5 HIGH=0.5 1/nrows - nrows
min 随机选择最小的整数行，用来生成数据分布。 nrows>min>0
Distribution是将列中的数据映射为一组列值，按数量或规则排序。这些样本值的范围被划分成区间，被称为箱，每个箱都包含列值样本的相等部分。例如一个bin包含了2%的样本数据，大约50个这样的间隔包含了全部样本数据。

这些统计文本被称为等价类，每个包含一个从列采样数值的范围或子集。
如下示例说明，每个箱有4%的数据（也就指定有25个箱子），同时指定了信任水平（confidence level）是90%，且不检查索引数据。

UPDATE STATISTICS MEDIUM FOR TABLE orders RESOLUTION 4 0.90 DISTRIBUTIONS ONLY;
指定样本的大小：
UPDATE STATISTICS MEDIUM FOR TABLE customer (city, state) SAMPLING SIZE 200 RESOLUTION 2 DISTRIBUTIONS ONLY;
DISTRIBUTIONS ONLY：不更新索引信息；这个选项不影响表的信息、使用的页数、行数、分片信息的收集；UPDATE STATISTICS需要这些信息来构造精确的列分布，需要更少的时间，更少的资源来收集它。
high
HIGH模式与MEDIUM模式更新的统计信息项是完全一样的，两者的差别在于样本的行数。

HIGH会扫描全表，而MEDIUM仅仅扫描部分行，基于resolution、confidence来控制。
对于创建了索引的表，对每个列都有一个MEDIUM模式的分布统计信息。在你对Index Key的每个列执行UPDATE STATISTICS HIGH之后，查询优化器会选择更高效的执行计划。
如果你没有指定Resolution，默认是0.5，它将每个列的值的范围划分成大约200个区间。
为了减少时间，建议指定表名和列名，而不是只指定表名（将对所有的列都做数据分布，而很多列又不参与优化器去生成执行计划，所以白白浪费时间。仅仅参与优化器执行计划的列是有意义的）。

涉及的系统表有：systables、syscolumns、sysindexes、sysindices、sysfragments、sysdistrib、sysfragdist。
3、何时执行update statistics？
1、 对表执行大量的修改；
2、 应用程序改变了列值分布；
3、 升级数据库到新版本；
何时执行 执行命令
大量的行数据被change UPDATE STATISTICS LOW
DROP DISTRIBUTIONS
不在索引上的列 UPDATE STATISTICS LOW
查询不在索引上的join或过滤列 UPDATE STATISTICS
MEDIUM DISTRIBUTIONS ONLY
查询在索引上列join或过滤列 UPDATE STATISTICS HIGH table (leading column in index)
查询一个复合索引定义的join列或过滤列 UPDATE STATISTICS HIGH table
(first differing column in multicolumn index)
查询一个复合索引定义的join列或过滤列 UPDATE STATISTICS LOW table (all columns in multicolumn index)
Queries have many small tables (fit into one extent) UPDATE STATISTICS HIGH on small tables
Queries use SPL routines UPDATE STATISTICS for procedure
4、命令行示例
UPDATE STATISTICS FOR SPECIFIC FUNCTION Perform_work;
UPDATE STATISTICS FOR SPECIFIC ROUTINE Perform_work;
UPDATE STATISTICS MEDIUM;
UPDATE STATISTICS MEDIUM RESOLUTION 10;
UPDATE STATISTICS MEDIUM RESOLUTION 10 .95;
{ RESOLUTION 10, CONFIDENCE .95}
UPDATE STATISTICS MEDIUM RESOLUTION 10 DISTRIBUTIONS ONLY;
UPDATE STATISTICS MEDIUM RESOLUTION 10 .95 DISTRIBUTIONS ONLY;
UPDATE STATISTICS HIGH;
UPDATE STATISTICS HIGH RESOLUTION 10;
UPDATE STATISTICS HIGH RESOLUTION 10 DISTRIBUTIONS ONLY;
UPDATE STATISTICS FORCE|AUTO;
UPDATE STATISTICS LOW FOR TABLE customer (customer_num) DROP DISTRIBUTIONS;
UPDATE STATISTICS LOW FOR TABLE customer (customer_num) DROP DISTRIBUTIONS ONLY;
UPDATE STATISTICS DROP DISTRIBUTIONS ONLY
5、打印数据分布
Dbschema -d dbname -hd tabname -ss
Update statistics medium for table $tabname; 会在sysdistrib表中对每个字段生成一条或多条mode=M的数据；
Update statistics high for table $tabname($idx_col_name); 在sysdistrib表中会drop对应列的mode=M的数据，生成该列mode=H的数据；
如果再执行Update statistics medium for table $tabname; 会在sysdistrib表中对每个字段生成一条或多条mode=M的数据，并drop之前生成的mode=H的数据；
结论：
Sysdistrib表中的数据分布信息是只保存最后一次执行update statistics的结果。为了对优化器更好的产生影响，所以需要把mode=H操作放到最后执行。
6、表属性STATCHANGE| STATLEVEL
STATCHANGE：对于分片表和非分片表都可以。
用来指定最低变化的百分比（从上一次计算分布统计数据之后，对表或片段中的行进行更新、删除和插入操作）。你可以设置该值从0-100，或设置为AUTO（就会取onconfig文件参数STATCHANGE的值）。
设置AUTO可以自动的进行比较。
当AUTO_STAT_MODE参数设置为AUTO的时候，UPDATE STATISTICS使用显式的或STATCHANGE的值来处理表、索引、分片的分布信息(已经陈旧或丢失)。

STATLEVEL：对于分片表。
确定数据分布的粒度级别和分片表的索引统计信息。
STATLEVEL=TABLE 指定所有分布都是在表级别创建的；
STATLEVEL=FRAGMENT 指定分布的创建和维护都是在fragment级别；
STATLEVEL=AUTO 指定由数据库在运行的时候决定是否启用fragment级别的分布。依据条件如下：
1、 SYSSBSPACENAME 参数是否创建；
2、 表是否被分片，采用这些策略EXPRESSION、INTERVAL、Rolling Window、LIST strategy；
3、 表的行数是否超过百万
如果上述三个条件有任何一个不满足,数据库采用TABLE的优先级高于FRAGMENT。
重要:
参数SYSSBSPACENAME在数据库初始化的时候必须要设置，指定sbspace用来存储fragment-level data distribution statistics。这些被存储在系统表的syfragsdist：encdist列中。
修改分片的水平从FRAGMENT到TABLE：
ALTER TABLE tabFrag STATLEVEL TABLE;
UPDATE STATISTICS LOW FOR TABLE tabFrag (smartblob) DROP DISTRIBUTIONS;
UPDATE STATISTICS HIGH FOR TABLE tabFrag (smartblob);
注意事项：
1、数据分布并(Distributions)不对LVARCHAR,BYTE,TEXT类型的列生效。