ORACLE统计信息相关整理

统计信息概述

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。

两种方法收集统计信息，ANALYZE命令与DBMS_STATS，总结如下：
(1).DBMS_STATS的优点
· dbms_stats可以并行分析。
· dbms_stats有自动分析的功能(alter table monitor)。
· 有时analyze分析统计信息不准确，主要是指对分区表的处理。dbms_stats会实在的去分析表全局统计信息(当指定参数)；而analyze是将表分区(局部)的statistics汇总计算成表全局statistics，可能导致误差。
(2).DBMS_STATS的缺点
· 不能validate structure。
· 不能收集chained rows，不能收集cluster table的信息，这两个仍旧需要使用analyze语句。
· dbms_stats默认不对索引进行analyze，因为默认cascade是false，需要手工指定为true。
· dbms_stats可以收集表，索引，列，分区的统计，但不收集聚簇统计(需要在各个表上收集替代整个聚簇)。
(3).建议使用DBMS_STATS的情况
· 对于分区表，建议使用dbms_stats，而不是使用analyze语句。analyze命令只收集最低一级对象(子分区或分区)的统计信息，然后推导出上一级对象的统计信息。但是如果上一级对象的统计信息的Global Status值为YES，则将不会覆盖和更新原有的统计信息。
· 使用dbms_stats，可以并行进行，对多个用户，多个Table。
· 使用dbms_stats，可以得到整个分区表的数据和单个分区的数据。
· 使用dbms_stats，可以在不同级别上Compute Statistics:单个分区、子分区、全表、所有分区。
· 使用dbms_stats，可以导出统计信息。
· 使用dbms_stats，可以用户自动收集统计信息。
· 对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。
(4).需要使用ANALYZE的情况
· 使用LIST CHAINED ROWS和VALIDATE子句。
· 收集空闲列表块的统计。

统计信息查询

-- 数据表统计信息
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; # 时间输出格式化
select table_name,num_rows, blocks, last_analyzed from dba_tables where owner = 'USER';
-- 数据索引统计信息
select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed from dba_indexes t where owner in ('USER');

Oracle官方也有一个脚本用于格式化输出某表与索引的详细信息，其中包含字段、统计信息、索引信息等。官方参考链接：MOS：SCRIPT - Select to show Optimizer Statistics for CBO (Doc ID 31412.1)，脚本输出结果参考：
脚本链接：https://github.com/ericzhong2010/Oracle-Scripts/blob/master/01%20Statistics/sosihtml.sql

统计信息收集

自动收集

统计信息自动收集，它必须要满足如下的条件：
（1）对象的统计信息之前没有收集过。
（2）当对象有超过10%的rows 被修改，此时对象的统计信息也称为stale statistics。
※ 通常繁忙的业务系统会将自动收集停止，而设定计划任务或定期手工执行避免导致性能问题。

# 查询auto optimizer stats collection自动收集任务状态
select client_name,status from dba_autotask_client;
# 关闭自动收集任务
exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
# 启动自动收集任务
exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
# 查看自动收集统计信息历史执行情况
SELECT * FROM dba_autotask_client_history WHERE client_name LIKE '%stats%';
# 查询自动收集时间窗，默认周一至周五时间为22点(持续4小时)，周六日为6点(持续20小时)
col REPEAT_INTERVAL for a60
col DURATION for a30
SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
  FROM dba_autotask_window_clients c, dba_scheduler_windows w
 WHERE c.window_name = w.window_name
   AND c.optimizer_stats = 'ENABLED';
# 调整自动收集时间窗
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '"SYS"."FRIDAY_WINDOW"',
    attribute => 'DURATION',
    value => numtodsinterval(180,'minute'));
END;  
/

手工收集

# 收集整个数据库的统计信息
exec dbms_stats.gather_database_stats(estimate_percent=>'10', cascade=>true, granularity=>'ALL', degree => DBMS_STATS.AUTO_DEGREE);
# 收集某个用户的统计信息
exec dbms_stats.gather_schema_stats(ownname=> 'USER', estimate_percent=>'10', degree=>DBMS_STATS.AUTO_DEGREE, cascade=>true,granularity=>'ALL');
# 收集表统计信息
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent =>'10', method_opt=> 'for all indexed columns');
# 收集索引统计信息
exec dbms_stats.gather_index_stats(ownname => 'USER',indname => 'IDX_OBJECT_ID',estimate_percent =>'10', degree => DBMS_STATS.AUTO_DEGREE);
# 收集表和索引统计信息 
exec dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent =>'10',method_opt=> 'for all indexed columns',cascade=>TRUE, degree => DBMS_STATS.AUTO_DEGREE);

DBMS_STATS参数说明
ownname： USER_NAME
tabname： TABLE_NAME
partname: 分区表的某个分区名
estimate_percent: 采样百分比，有效范围为[0.000001,100] 通常10%-30%采样率就可以，不需要每次都100%。
block_sample：使用随机块采样代替随机行采样
method_opt：
cascade:是否收集此表索引的统计信息
degree:并行处理的cpu数量；如果不清楚配置多少合适，可以考虑DBMS_STATS.AUTO_DEGREE (注意不是并发，并行与并发是有差异的)
granularity： 统计数据的收集，‘ALL’ - 收集所有（子分区，分区和全局）统计信息

并发与并行信息收集

并行与并发差异：

• 并行指对某个对象进行统计收集时会采用多个并行Slave进行处理，但是对于多个对象(表、索引或分区)来讲，处理还是串行的。
• 并发是在11.2.0.2版本起，Oracle为了能够使多个对象的统计信息收集也能够同时进行，推出了并发收集统计信息(CONCURRENT)模式，使多进程的环境更加有效率。(12.1.0.1版本将会在自动收集统计信息任务启用并发收集。)
  并发收集统计信息时，数据库生成的JOB数会根据具体情况来分配，大多数情况下，DBMS_STATS 程序会给每个对象分配一个JOB；但如果对象（表或者分区）的大小太小，为了节省资源，Oracle会合并多个表和分区在一个JOB中执行。
  并发功能相关参数：

exec dbms_stats.set_global_prefs('CONCURRENT','<参数>');

参数参考：
在11.2.0.2~11.2.0.4的版本上，可以设置的值：

TRUE  ：并发有效
FALSE ：并发无效

12c的版本上，可以设置以下的值：

MANUAL   : 只有当手动收集时，并发有效
AUTOMATIC: 只有当自动收集时，并发有效   
ALL      : 当手动/自动收集，并发都有效
OFF      : 并发无效

通过Job监控可以查询并发状态：

SET LINESIZE 200
COLUMN TARGET FORMAT a25
COLUMN TARGET_TYPE FORMAT a25
COLUMN JOB_NAME FORMAT a14
COLUMN START_TIME FORMAT a40
SELECT TARGET, TARGET_TYPE, JOB_NAME,
       TO_CHAR(START_TIME, 'dd-mon-yyyy hh24:mi:ss')
FROM   DBA_OPTSTAT_OPERATION_TASKS
WHERE  STATUS = 'IN PROGRESS'
AND    OPID = (SELECT MAX(ID)
               FROM   DBA_OPTSTAT_OPERATIONS
               WHERE  OPERATION = 'gather_schema_stats');

动态统计信息收集

Bug 9272549 - User statistics are ignored when dynamic sampling occurs 9272549.8
该BUG会导致严重的性能问题，官方申明，只有在12.1版本才解决这个问题，临时解决方案是手动关闭动态采样。

如果表没有统计信息，那么CBO将会在动态的收集表的统计信息。可以通过如下命令判断动态收集统计信息的级别：

SYS@ zhongpdb SQL>show parameter OPTIMIZER_DYNAMIC_SAMPLING

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

动态采样级别查询：show parameter optimizer_dynamic_sampling 默认值：2
Level 0: Do not use dynamic sampling.

Level 1: Sample all tables that have not been analyzed if the following criteria are met: (1) there is at least 1 unanalyzed table in the query; (2) this unanalyzed table is joined to another table or appears in a subquery or non-mergeable view; (3) this unanalyzed table has no indexes; (4) this unanalyzed table has more blocks than the number of blocks that would be used for dynamic sampling of this table. The number of blocks sampled is the default number of dynamic sampling blocks (32).

Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.默认值

Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.

Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.

Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.

我们有时候需要通过手工清理表统计信息并锁定来实现指定表的动态统计信息收集：

EXEC DBMS_STATS.DELETE_TABLE_STATS(user,'&Table');
EXEC DBMS_STATS.LOCK_TABLE_STATS(user,'&Table');

常见问题

Q1. 统计信息收集速度慢，有什么方法可以加速？
A1. 可以通过同时设置并行与并发方式来加速统计信息收集，参考如下：

--设置并行收集
exec dbms_stats.set_global_prefs('CONCURRENT','TRUE');
--设置统计信息收集
dbms_stats.gather_database_stats(
ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'for all indexed columns', cascade=>true, degree=>DBMS_STATS.AUTO_DEGREE);
--关闭并行收集
exec dbms_stats.set_global_prefs('CONCURRENT','FALSE');

Q2. 为什么启用并行与并发还是那么慢？
A2. 1. 自适应导致并行无效，可将PARALLEL_ADAPTIVE_MULTI_USER参数设置为False。参考说明：https://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams178.htm
2. Oracle内部有一些临界值会导致并发不生效，比如表太小。这使得并发会无效。

Q3. OLAP数据库中经常会大批量导入数据，此时该如何管理统计信息？
A3. 在很多系统业务流程中，特别是数据仓库的ETL应用和批处理业务中，设计和使用了大量中间表或临时表。对这些表经常进行truncate、大批量数据insert、delete等DML操作，即这些表的记录经常会在0或大容量两种极端情况下变化。
这些表或者没有统计信息，或者在每天夜间的固定时间窗口采集的统计信息，并不能正确反映数据的真实情况，从而导致CBO优化器对这些表访问的SQL语句不一定能保证最优化。

（1）锁住统计信息
可采取上述锁住统计信息技术，将这些表在批处理业务中的典型数据状态的统计信息进行采集和锁定。这样无论这些表的信息如何变化，Oracle始终根据典型数据状态的统计信息产生SQL语句执行计划。
该策略的优点是资源消耗较少，基本上能保持SQL语句执行计划的稳定，但缺点是不能完全根据变化的数据，去选择最优的执行计划。

EXEC DBMS_STATS.DELETE_TABLE_STATS(user,'&Table');
EXEC DBMS_STATS.LOCK_TABLE_STATS(user,'&Table');

（2）实时采集统计信息
与上述策略不同的是，在批处理流程中，在这些表数据发生极端情况变化之后，实地时进行统计信息采集。例如：

execute immediate 'truncate table  &TNAME'; 
commit; 
 
execute immediate 'exec DBMS_STATS. GATHER_TABLE_STATS 
    (ownname=>'&OWNER', tabname=>'&TNAME',estimate_percent=>10, 
     Degree=>8, Cascade=>TRUE, Granularity=>'ALL'); 

该策略的优缺点与上述策略正好相反，即优点是能根据每次变化的数据去选择最优的执行计划。缺点是资源消耗较大。
（3）使用HINT技术
通过在SQL语句中使用HINT技术，确保执行计划处于稳定的最优化状态。
该策略的优点是充分利用开发人员自己的经验和水平，而不依赖于Oracle统计信息采集；缺点是万一开发人员用错HINT就麻烦了，而且这种策略太死板，在程序里写死了执行计划，而数据是千变万化的。