Oracle中的统计信息

2023-08-24 21:50:52

一、什么是统计信息

统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。例如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。
统计信息是存放在数据字典表中的，如tab$，一般可通过察看某些视图来获取统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS等。在这些视图中包含表示统计信息的一些字段，这些字段只有搜集过统计信息之后才有值，否则是空的。例如，last_analyzed 字段表示上次统计信息搜集的时间，可以根据这个字段，快速的了解最近一次统计信息搜集的时间。

二、收集统计信息的方法

  1. 使用gather_stats_job自动收集是在创建数据库时自动创建的，并由调度程序进行管理。他会收集数据库中优化程序统计信息缺失或已过时的所有对象的统计信息。
  2. 使用dbms_stats 程序包手动收集收集的是系统统计信息。
  3. 通过设置数据库初始化参数进行收集。
  4. 通过从另一个数据库导入统计信息进行收集。

三、Oracle自动收集统计信息的原理

统计信息对于Oracle数据库来说至关重要，尤其是在使用CBO（基于成本的优化器）模式的时候，统计信息包括表的使用块数、空闲块数、平均行长度、统计信息收集时间等。在Oracle9i数据库中，两种优化器模式RBO和CBO并存，在默认情况下，optimizer_mode参数的值是choose，choose不是优化器模式，它表示在分析数据库中的语句时，如果在对象上有统计信息，就是用CBO方式生成执行计划，如果对象上没有统计信息，是使用RBO模式。

从总体上来说，CB的准确度高于RBO，但是它要求要有统计信息和统计信息必须准确，否则Oracle可能会做出错误的判断。所以在Oracle9i数据库中，我们会自己来规划在什么样的时间采用什么样的策略来收集统计信息。也就是说，Oracle9i的统计信息收集工作必须通过手工方式来实现。

到了Oracle10g，默认情况下，optimizer_mode=all_rows，也就是采用了CBO的方式，为了保证执行计划的准确，在周一到周五（晚22:00-次日6:00），通过一个job（gather_stat_job）自动收集对象的统计信息。这种自动收集统计信息的方式并不是收集所有对象的统计信息，而是收集没有统计信息的对象和统计信息过旧的对象。

Automatic Statistics Gathering是由Scheduler调度GATHER_STATS_JOB作业来完成的，在GATHER_STATS_JOB作业中则调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC存储过程。GATHER_DATABASE_STATS_JOB_PROC是一个内部的存储过程，基本上跟DBMS_STATS.GATHER_DATABASE_STATS的功能一样，但在其内部有优先顺序的考虑，更新量（变化量）越多的表将会越优先收集统计信息。为对象收集统计信息的条件是，之前从来没有收集过的或者是更新的（包括insert,update,delete,truncate）记录数超过当前总记录数10%的表（在Oracle11g中则提供了SET_TABLE_PREFS函数修改10%这个阈值）。记录数的更改量由Oracle数据库自动监控，在初始化参数statistics_level设置为TYPICAL或者ALL时，自动监控即会生效。

3.1 调整当更新量达（变化量）达到多少时开始统计信息收集任务

1
2
3
4
BEGIN
  DBMS_STATS.SET_TABLE_PREFS ( ownname =>'XXXXX', tabname =>'T1', pname =>'STALE_PERCENT', pvalue =>'5');
END;
/

3.2 调整自动收集统计信息的执行时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');
 
WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION
------------------------------ ------------------------------------------------------------ --------------------
MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00
TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00
WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00
THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00
FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00
SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00
SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00
 
# WINDOW_NAME：任务名
# REPEAT_INTERVAL：任务重复间隔时间
# DURATION：持续时间
 
# 1.停止任务
BEGIN
DBMS_SCHEDULER.DISABLE(
name=>'"SYS"."FRIDAY_WINDOW"',
force=>TRUE);
END;
 
# 2.修改任务的持续时间，单位是分钟
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'DURATION',
value=>numtodsinterval(180, 'minute'));
END;
 
# 3.开始执行时间，BYHOUR=2,表示2点开始执行
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name=>'"SYS"."FRIDAY_WINDOW"',
attribute=>'REPEAT_INTERVAL',
value=>'FREQ=WEEKLY;BYDAY=MON;BYHOUR=2;BYMINUTE=0;BYSECOND=0');
END;
 
# 4.开启任务
BEGIN
DBMS_SCHEDULER.ENABLE(
name=>'"SYS"."FRIDAY_WINDOW"');
END;

3.3 禁用统计信息自动收集

1
2
3
4
BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

四、DBMS_STATS包

DBMS_STATS包，主要提供了搜集（gather），删除（delete），导出（export），导入（import），修改（set）统计信息的方法。

dbms_stats与analyze的区别：

dbms_stats是Oracle9i及后续版本中用于收集统计信息的包，虽然analyze命令也一直可以使用，但是现在已经不推荐使用analyze命令来收集统计信息，而是使用dbms_stats。两者之间有很大的不同，dbms_stats能正确收集分区表的统计信息，也就是说能够收集global statistic，而analyze只能收集最低层次对象的统计信息，然后推导和汇总出高一级对象的统计信息，如果分区表只会收集分区统计信息，然后再汇总出所有分区的统计信息，得到表一级的统计信息。

4.1 什么是golbal statistic

golbal statistic是指直接从对象本身收集到的统计信息，而不是从下一级对象“推导”和“汇总”出来的统计信息，golbal statistic对于优化器来说非常重要，一个SQL，除非其查询条件限制了数据只在分区上，否则大多数情况下需要golbal statistic才能得到正确的执行计划。有的统计值可以从下一级对象进行汇总后得到，如表的总行数，可以通过各分区的行数相加得到。但有的统计值不能通过下一级对象得到，比如列上的唯一值数量（distinct value）以及密度值（density）。

4.2 使用DBMS_STATS.GATHER_DATABASE_STATS收集整个数据库的统计信息

1
2
3
4
5
6
7
BEGIN
  dbms_stats.gather_database_stats(estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,
                                   method_opt       => 'for all indexed columns',
                                   options          => 'GATHER AUTO',
                                   cascade          => TRUE);
END;
/

参数说明：

1.estimate_percent:采样的百分比，使用dbms_stats.auto_sample_size选项允许Oracle自动估算要采样的一个segment的最佳百分比。

2.method_opt选项适合在表和索引数据发生变化时刷新统计数据：

  • for table:只统计表
  • for all indexed columns:只统计有索引的表列
  • for all indexes:只分析统计相关索引
  • for all columns：分析所有的列

dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

如果有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的选项，包括skewonly，repeat和auto：

  • method_opt=>'for all columns size skewonly'
  • method_opt=>'for all columns size repeat'
  • method_opt=>'for all columns size auto'

(1).skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。如果dbms_stat发现一个索引的各个列分布得不均匀，那么就会为该索引创建直方图，帮助基于成本的SQL优化器决定是进行索引访问，还是进行全表扫描访问。
(2).repeat选项在重新分析任务所消耗的资源就会少一些。使用repeat选项时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，应该采取这种方式。
(3).auto选项根据数据分布以及应用程序访问列的方式来创建直方图。

3.options控制Oracle统计信息的刷新方式:

  • gather:重新分析整个架构
  • gather empty:只分析目前还没有统计的表
  • gather stale:只重新分析修改量超过10%的表（包括插入、更新和删除）
  • gather auto:重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。使用gather auto类似于组合使用gather stale和gather empty

4.3 使用DBMS_STATS.GATHER_SCHEMA_STATS收集整个用户下对象的统计信息

1
2
3
4
5
6
7
exec dbms_stats.gather_schema_stats(
ownname => 'SCOTT',
options => 'GATHER AUTO', 
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
degree =>15
)

4.4 使用DBMS_STATS.GATHER_TABLE_STATS收集表、列、索引的统计信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
dbms_stats.gather_table_stats(
owner VARCHAR2,
tablename VARCHAR2,
partname VARCHAR2,
estimate_percent NUMBER,
block_sample BOOLEAN,
method_opt VARCHAR2,
degree NUMBER,
granularity VARCHAR2,
cascade BOOLEAN,
stattab VARCHAR2,
statid VARCHAR2,
statown VARCHAR2,
no_invalidate BOOLEAN,
force BOOLEAN
)

参数说明：

1.owner:要分析表的所有者

2.tablename：要分析的表的表名

3.partname：分区名

4.estimate_percent:采样行的百分比，从0.000001-100，null为全部分析，不采样。常量DBMS_STATS.AUTO_SAMPLE_SIZE是默认值，由Oracle决定最佳采样率。

5.block_sample:是否用块采样代替行采样。

6.method_opt：决定histograms信息是怎样被统计的，method_opt的取值如下：

  • for all columns：统计所有的histograms
  • for all indexed columns：统计所有index列的histograms
  • for all hidden coloumns：统计hidden列的histograms
  • for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY 统计指定列的histograms，N的取值范围是0-254

7.degree:设置统计信息收集的并行度，默认值为null。

8.cascade：收集索引的统计信息，默认为false

9.stattab：指定存储统计信息的表。

10.statid：如果多个表的统计信息存储在一个stattab中时，statid用作分区条件。

11.statown：存储统计信息表的所有着。

如果不指定上述三个参数，则统计信息会被更新到数据字典。

12.force:即使表锁住了也收集统计信息。

4.5 统计信息的导出导入删除操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
# 1.创建统计信息历史保留表
exec dbms_stats.create_stat_table(
ownname => '',
stattab => ''
)
 
# 2.导出整个scheme的统计信息
exec dbms_stats.export_schema_stats(
ownname => '',
stattab => ''
)
 
# 3.分析scheme
Exec dbms_stats.gather_schema_stats(
ownname => '', 
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns ',
degree => 6
)  
 
# 4.分析表
exec dbms_stats.gather_table_stats(
ownname => '',
tabname =>'',
estimate_percent => 10,
method_opt=> 'for all indexed columns'
)
 
# 5.分析索引
exec dbms_stats.gather_index_stats(
ownname => '',
indname => '',
estimate_percent => 10,
degree => 6
)
 
# 6.如果发现执行计划走错，删除表的统计信息
exec dbms_stats.delete_table_stats(
ownname => '',
tabname => ''
)
 
# 7.导入表的历史统计信息
exec dbms_stats.import_table_stats(
ownname => '',
tabname => '',
stattab => '') 

4.6 锁住统计信息

将一个表的统计信息锁住，以防止错误的统计信息将此正确的信息覆盖掉时需要用到LOCK_TABLE_STATS包：

1
2
3
4
DBMS_STATS.LOCK_TABLE_STATS(
ownname    VARCHAR2,
tabname    VARCHAR2
);

参考文档：

http://blog.csdn.net/outget1/article/details/4834000

http://wenku.baidu.com/link?url=u1VHYsNHno9ny0jOZWIbK409el27zEjMtzXwXvrxk5Dv5C7Xq6LGTCVNz88alfbzPFwP2IPvoWru92-PGkZgBvE_woQdzfiUrbIiAk4I7-O

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

在CBO(基于代价的优化器模式)条件下，SQL语句的执行计划由统计信息来决定，若没有统计信息则会采取动态采样的方式决定执行计划！可以说统计信息关乎sql的执行计划是否正确，属于sql执行的指导思想，oracle的初始化参数statistics_level控制收集统计信息的级别，有三个参数值:

BASIC :收集基本的统计信息

TYPICAL：收集大部分统计信息(数据库的默认设置)

ALL：收集全部统计信息

Oracle 10g之后，Query Optimizer就已经将CBO作为默认优化器，并且Oracle官方不再支持RBO服务。但是，通过优化器参数optimizer_mode，我们可以控制Oracle优化器生成不同模式下的执行计划。

关于优化器的请参考：《SQL性能优化之optimizer_mode参数原理渗透解析》

2.如何收集统计信息

2.1 统计信息的内容：

1）Table statistics

Number of rows --行数量

Number of blocks --block数量

Average row length --平均行的长度.

2）Column statistics

Number of distinct values (NDV) in column --列中distinct的值

Number of nulls in column --列中null的值

Data distribution (histogram)  --数据分布

3）Index statistics

Number of leaf blocks --子节点的块数量

Levels --子节点数量

Clustering factor --集群因子

4）System statistics

I/O performance and utilization --IO性能和利用率

CPU performance and utilization --CPU的性能和利用率

2.2 收集统计信息

Oracle Statistic 的收集，可以使用analyze 命令，也可以使用DBMS_STATS 包来收集，Oracle 建议使用DBMS_STATS包来收集统计信息，因为DBMS_STATS包收集的更广，并且更准确。analyze 在以后的版本中可能会被移除。

DBMS_STATS常用的几个过程如下：

1
2
3
4
5
6
7
8
9
10
11
12
dbms_stats.gather_table_stats 收集表、列和索引的统计信息；
dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息
dbms_stats.GATHER_DICTIONARY_STATS：所有字典对象的统计；
DBMS_STATS.GATHER_DICTIONARY_STATS 其收集所有系统模式的统计
dbms_stats.delete_table_stats 删除表的统计信息
dbms_stats.delete_index_stats 删除索引的统计信息
dbms_stats.export_table_stats 输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats 设置表的统计
dbms_stats.auto_sample_size

analyze 命令的语法如下：

1
2
3
SQL>analyze table tablename compute statistics;
SQL>analyze table tablename compute statistics for all indexes;
SQL>analyze table tablename delete statistics
2.3 统计信息的分类

Oracle 的Statistic 信息的收集分两种：自动收集和手工收集。

Oracle 的Automatic Statistics Gathering 是通过Scheduler 来实现收集和维护的。Job 名称是GATHER_STATS_JOB, 该Job收集数据库所有对象的2种统计信息：

（1）Missing statistics（统计信息缺失）

（2）Stale statistics（统计信息陈旧）

该Job 是在数据库创建的时候自动创建，并由Scheduler来管理。Scheduler 在maintenance windows open时运行gather job。 默认情况下，job 会在每天晚上10到早上6点和周末全天开启。该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

Scheduler Job的stop_on_window_close 属性控制GATHER_STATS_JOB 是否继续。该属性默认值为True. 如果该值设置为False，那么GATHER_STATS_JOB 会中断， 而没有收集完的对象将在下次启动时继续收集。

Gather_stats_job 调用dbms_stats.gather_database_stats_job_proc过程来收集statistics 的信息。 该过程收集对象statistics的条件如下：

（1）对象的统计信息之前没有收集过。

（2）当对象有超过10%的rows 被修改，此时对象的统计信息也称为stale statistics。

但是对于高度变化的表在白天的活动期间被TRUNCATE/DROP并重建或者块加载超过本身总大小10%的对象；我们可以将这些表上的统计设置为NULL

可以通过以下SQL来查看：

1
2
3
select job_name, program_name, enabled, stop_on_window_close
  from dba_scheduler_jobs
 where job_name = 'gather_stats_job';

为了决定是否对对象进行监控，Oracle 提供了一个参数STATISTICS_LEVEL。通过设置初始化参数STATISTIC_LEVEL 为TYPICAL 或ALL，就可以自动收集统计信息(默认值为TYPICAL，因此可以随即启用自动收集统计信息的功能)。STATISTIC_LEVEL 参数的值可以激活GATHER_STATS_JOB。

在10g中表监控默认是激活的，如果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：

（1）ASH(Active Session History)

（2）ASSM(Automatic Shared Memory Management)

（3）AWR(Automatic Workload Repository)

（4）ADDM(Automatic Database Diagnostic Monitor)

1
2
3
4
5
sys@ORCL> show parameter statistics_level;
      
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
statistics_level                     string      TYPICAL

当启动对象的监控后，从上次统计信息收集之后的的信息，如inserts，updates，deletes 等，这些改变的信息会记录到user_tab_modifications 视图。

当对象的数据发生改变之后， 经过几分钟的延时，这些信息写入到user_tab_modifications视图，然后dbms_stats.flush_database_monitoring_info过程就会发现这些信息，并讲这些信息保存在内存中。

当监控的对象被修改的部分超过10%时，gather_database_stats 或者gather_schema_stats 过程就会去收集这些stale statistics

3.统计信息的存储位置以及常用数据字典

3.1 统计信息常用数据字典

统计信息收集如下数据：

（1）表自身的分析： 包括表中的行数，数据块数，行长等信息。

（2）列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况。

（3）索引的分析： 包括索引叶块的数量，索引的深度，索引的聚合因子等。

这些统计信息存放在以下的数据字典里：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS
3.2 表的统计信息

包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused的数据，行的平均大小：

1
2
3
4
5
6
7
SELECT NUM_ROWS, --表中的记录数
BLOCKS, --表中数据所占的数据块数
EMPTY_BLOCKS, --表中的空块数
AVG_SPACE, --数据块中平均的使用空间
CHAIN_CNT, --表中行连接和行迁移的数量
AVG_ROW_LEN --每条记录的平均长度
FROM USER_TABLES
3.3索引列的统计信息

包含索引的深度（B-Tree的级别），索引叶级的块数量，集群因子（clustering_factor), 唯一值的个数。

1
2
3
4
5
6
7
SELECT BLEVEL, --索引的层数
LEAF_BLOCKS, --叶子结点的个数
DISTINCT_KEYS, --唯一值的个数
AVG_LEAF_BLOCKS_PER_KEY, --每个KEY的平均叶块个数
AVG_DATA_BLOCKS_PER_KEY, --每个KEY的平均数据块个数
CLUSTERING_FACTOR --群集因子
FROM USER_INDEXES
3.4 列的统计信息

包含唯一的值个数，列最大小值，密度（选择率），数据分布（直方图信息），NUll值个数

1
2
3
4
5
6
7
8
SELECT NUM_DISTINCT, --唯一值的个数
LOW_VALUE, --列上的最小值
HIGH_VALUE, --列上的最大值
DENSITY, --选择率因子（密度）
NUM_NULLS, --空值的个数
NUM_BUCKETS, --直方图的BUCKET个数
HISTOGRAM --直方图的类型
FROM USER_TAB_COLUMNS
对于统计信息的搜集，谈谈个人的几点理解：
1.统计信息默认是存放在数据字典表中的，也只有数据字典中的统计信息，才会影响到CBO。
2.DBMS_STATS 提供的CREATE_STAT_TABLE 过程，只是生成一个用户自定义的特定格式的表，用来存放统计信息罢了，这个表中的统计信息是不会影响到统计信息的。
3.GATHER 系列过程中，如果指定stattab，statid，statown 参数（也可以不指定），则是搜集的统计信息除了更新到数据字典外，还在statown 用户下的stattab 表中存放一份，标示为 statid;
 
https://blog.csdn.net/tianlesoftware/article/details/4668723
上一篇：Calendar 日期类介绍


下一篇：iOS开发零基础--Swift篇 循环