Oracle统计信息的一些总结

Oracle里的统计信息

Oracle数据库里的统计信息是这样的一组数据：他存储在数据字典里，且从多个维度描述了Oracle数据库里对象的详细信息

逐渐消失：analyze
官方推荐：DBMS_STATS包

四种存储过程

• GATHER_TABLE_STATS
• GATHER_INDEX_STATS
• GATHER_SCHEMA_STATS
• GATHER_DATABASE_STATS

例句

exec dbms_stats.GATHER_TABLE_STATS
				 (ownname => 'HH',
				  tabname =>'T2',
				  estimate_percent => 100,
				  method_opt => 'for table',
				  cascade => false);

统计信息概念

什么是统计信息？
oracle数据库中的统计信息存储在数据字典中，从多个维度描述了oracle数据库里的详细信息。

统计信息作用是什么？
oracle CBO优化器会利用统计信息计算目标SQL各种可能、不同的执行路径的成本，并从中选择一条最小的执行路径来作为目标SQL的执行计划。（统计信息不准确，SQL的执行计划会走错，SQL会出现性能问题）

统计信息分类：

• 表的统计信息
  表的统计信息主要包含表的总行数（num_rows）,表块数（blocks）以及平均长度（avg_row_len）

• 索引的统计信息
  索引的统计信息描述了索引的详细信息，所以索引的层级、叶子块的数量、聚簇因子等

• 列统计信息
  列统计信息记录了列的distinct值的数量、null的数量、列最小值和列最大值。

• 系统统计信息
  系统统计信息是描述了oracle数据库服务器的系统处理能力，包含cpu和I/O两个方面，可以通过这两个方面来知道数据库服务器的实际处理能力

• 数据字典统计信息
  描述了字典基表（tab,ind,ind,ind等），数据字典基表上的索引。

• 内部对象统计信息
  记录了一些内部表（x系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为0，x系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为0，x系统表）的详细信息，它的维度和普通表的统计信息类似，但是其表块数为0，x实际上只是oracle自定义的内存结构，不占用实际物理空间。

DBMS_STATS重要参数详解

• ownname:表示表的拥有者，不区分大小写。

• tabname:表示表名字，不区分大小写。

• granularity:表示收集统计信息的粒度，该选项只对分区表生效，默认为 AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。
  对于该选项，我们一般采用AUTO 方式，也就是数据库默认方式，因此在后面的脚本中，省略该选项。

• estimate_percent:表示采样率，范围是0.000 001～100。
  这个参数主要是用于CBO估算表的总行数，采样率越高，CBO估算的表行数越接近于真实值，执行计划越能走正确。
  估算总行数=样本大小(DBA_TAB_STATISTICS.SAMPLE_SIZE）*100/采样率(estimate_percent)
  这个参数可能对于很多新手来说都不知道怎么设置：
  一般对小于 1GB 的表进行100%采样，因为表很小，即使100%采样速度也比较快。有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。
  因此建议对小表 100%采样。我们一般对表大小在1GB～5GB 的表采样50%，对大于5GB的表采样30%。如果表特别大，有几十甚至上百 GB，我们建议应该先对表进行分区，然后分别对每个分区收集统计信息。一般情况下，为了确保统计信息比较准确，我们建议采样率不要低于30%。

  • 小于1GB 建议采样比100%
  • 1GB～5GB 建议采样比50%
  • 大于5GB 建议采样比30%

• method_opt：用于控制收集直方图策略。
  直方图简单来说就是数据库了解表中某列的数据分布，从而更正确的走更优的执行计划
  method_opt => ‘for all columns size 1’ 表示所有列都不收集直方图
  method_opt => ‘for all columns size skewonly’ 表示对表中所有列收集自动判断是否收集直方图。选择率非常高的列和null的列不会收集（谨慎使用）
  method_opt => ‘for all columns size auto’ 表示对出现在 where 条件中的列自动判断是否收集直方图。
  method_opt => ‘for all columns size repeat’ 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。
  在实际工作中，当系统趋于稳定之后，使用 REPEAT 方式收集直方图。

• no_invalidate ：表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS.AUTO_INVALIDATE，表示让 Oracle 自己决定是否立即失效。
  建议将 no_invalidate 参数设置为 FALSE，立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为 false。

• degree： 表示收集统计信息的并行度，默认为 NULL。如果表没有设置 degree。如果表没有设置 degree，收集统计信息的时候后就不开并行；如果表设置了 degree，收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree，一般情况下，表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。

• cascade ：表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让 Oracle 自己判断是否级联收集索引的统计信息。

举例：

exec dbms_stats.gather_schema_stats(
ownname => 'HH',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8, 
granularity => 'ALL',
cascade => TRUE);

删除表T2、表T2的所有列及表T2的所有索引的统计信息

exec dbms_stats.delete_table_stats(ownname => 'HH',tabname =>'T2');

解锁用户

DBMS_STATS.UNLOCK_schema_STATS('用户名');

解锁表

select table_name from user_tab_statistics where stattype_locked is not null;

dbms_stats.unlock_table_stats('用户名','表名');

select 'exec dbms_stats.unlock_table_stats('''||owner||''','''||table_name||''');' from user_tab_statistics where stattype_locked is not null;

exec dbms_stats.gather_schema_stats(ownname => 'HH',estimate_percent => 100,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 8, granularity => 'ALL',cascade => TRUE);

select a.TABLE_NAME,a.NUM_ROWS,a.BLOCKS,a.EMPTY_BLOCKS,a.AVG_SPACE,a.CHAIN_CNT,a.AVG_ROW_LEN,a.GLOBAL_STATS,a.SAMPLE_SIZE,a.LAST_ANALYZED from dba_tables a where OWNER = 'HL';

analyze命令不能正确的收集分区表的统计信息，而DBMS_STATS包却可以
analyze命令不能并行收集统计信息，而DBMS_STATS包却可以（degree，一个问题，如何判断他开多少并行合适）

DBMS_STATS的缺陷：只能收集与CBO相关的统计信息
analyze命令可以用来分析和收集上述额外信息

dba_tables 里的 NUM_ROWS 存储的就是目标表的记录数，目标表的记录数是计算结果集的cardinality（基数）的基础，而结果集的cardinality（基数）则往往直接决定了CBO计算的成本值。

dba_tables 里的 BLOCKS ，目标表表块的数量会直接决定CBO计算出来的对目标表做全表扫描的成本，
目标表表块的数量越大，则对目标表走全表扫描的成本值越大

AVG_ROW_LEN存储的是目标表的平均行长度。平均行长度的计算方法是用目标表的所有行记录所占用的字节数（不算行头）除以目标表的总行数，他可能会被Oracle用来计算目标表对用的结果集所占用内存的大小

Oracle自动收集统计信息的依据
Oracle数据库中的自动统计信息收集作业重复收集统计信息的策略，或者说判断统计信息是否失效的标准就是：
自上次自动统计信息收集作业完成后，如果MON_MODS_ALL中记录的目标表的 delete + insert + update 操作所影响的记录数超过了TAB中记录的目标表记录数的10%，或者自上次自动统计信息手机作业完成之后目标表执行过Truncate操作，那么Oracle就会认为目标表的统计信息已经失效，自动统计信息收集作业在执行时就会对该目标表重新收集统计信息