Oracle 统计信息收集 - Leohahah - 博客园

标签： | 发表时间：2021-06-08 08:54 | 作者：
出处：https://www.cnblogs.com

官网网址参考：

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#CIHBIEII

https://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_4005.htm#i2150533

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::p11_question_id:5792247321358

https://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm#TGSQL366

查询表上一次收集统计信息的时间：

1
select  owner,table_name,last_analyzed  from  dba_tables  where  owner= 'SCOTT' ;

统计信息涉及的视图：

Column statistics appear in the data dictionary views  USER_TAB_COLUMNS,  ALL_TAB_COLUMNS, and  DBA_TAB_COLUMNS. Histograms appear in the data dictionary views  USER_TAB_HISTOGRAMS,  DBA_TAB_HISTOGRAMS, and  ALL_TAB_HISTOGRAMS;  USER_PART_HISTOGRAMS,  DBA_PART_HISTOGRAMS, and  ALL_PART_HISTOGRAMS; and  USER_SUBPART_HISTOGRAMS,  DBA_SUBPART_HISTOGRAMS, and  ALL_SUBPART_HISTOGRAMS.

收集统计信息主要有2种方法：

1. analyze

analyze可以用来收集表，索引，列以及系统的统计信息和直方图，以下为一些典型用法：

1
2
3
4
5
6
7
8
9
analyze  table  scott.emp compute  statistics ;  --收集所有的统计信息和直方图信息，包括表、列、索引。
analyze  table  scott.emp compute  statistics  for  table ;  --收集emp表的统计信息，不含列、索引统计信息和直方图。
analyze  table  scott.emp compute  statistics  for  all  columns;   --收集所有列的统计信息和直方图（超大表较耗资源，因为只要列中有非空值，那么就会收集这个列的统计信息和直方图）。
analyze  table  scott.emp compute  statistics  for  all  indexed columns;   --收集所有索引列的统计信息和直方图。
analyze  table  scott.emp compute  statistics  for  all  indexes;  --收集所有索引统计信息，不含列的统计信息和直方图。
analyze  table  scott.emp compute  statistics  for  columns 列1,列2；  --收集2个列的统计信息和直方图。
analyze  index  idx_ename  delete  statistics ;  --删除索引idx_ename的统计信息。
analyze  table  scott.emp  delete  statistics ;  --删除表t1所有的表，列，索引的统计信息和列直方图。
analyze  table  scott.emp estimate  statistics  sample 15 percent  for  table ;  --收集emp表的统计信息，以估算模式采样比例为15%进行收集，不含列、索引统计信息和直方图。

从语法可以看出，只有指定列统计信息收集时，才会收集相关列的直方图，此外收集直方图时for子句还可以加size子句，size的取值范围是1-254，默认值是75，表示直方图的buckets的最大数目。而dbms_stats包的size选择则有：数字|auto|repeat|skewonly选项，但analyze的size只能是数字。

 

关于直方图：

A histogram is a special type of column statistic that provides more detailed information about the data distribution in a table column. A histogram sorts values into "buckets," as you might sort coins into buckets.

从官网解释（参考第四个网址）来看，直方图就是一种特殊的列统计信息，这也与我们上边的推断相符，只有列才有直方图。

这里贴一个Tom Kyte用于查看analyze后统计信息的SQL：(已稍作改进，仅示例，这种格式的SQL不推荐，原SQL较简单参考第三个网址)

1
2
3
4
5
select  t.num_rows  as  num_rows_in_table, i.index_name, i.num_rows  as  num_rows_in_index, co.num_analyzed_cols,ch.histogram_cnt
from  ( select  num_rows  from  user_tables  where  table_name = 'EMP' ) t,
      ( select  index_name,num_rows  from  user_indexes  where  table_name =  'EMP' ) i,
      ( select  count (*)  as  num_analyzed_cols  from  user_tab_columns  where  table_name= 'EMP'  and  num_distinct  is  not  null ) co,
      ( select  count ( distinct  column_name) histogram_cnt  from  user_tab_histograms  where  table_name =  'EMP'  ) ch;

需要注意的一点是for table选项在某些版本中并不只收集表统计信息，而是连列和索引的统计信息一块收集了，至于具体哪些版本的表现不同这里不做深究，使用上述SQL可以轻易的测试出你的analyze和dbms_stats语句到底收集了什么统计信息和直方图。

 

2. 调用dbms_stats包

dbms_stats与analyze的区别是：

analyze收集系统内部对象会报错，而dbms_stats不会

analyze不能正确的收集分区表的统计信息 ，而dbms_stats可以通过指定粒度来实现（granularity）。

analyze不能并行的收集统计信息，而dbms_stats可以（可以加上degree=>4来实现并行度为4的收集）。

Oracle推荐使用dbms_stats来收集统计信息，analyze将会被逐渐抛弃。

dbms_stats中负责收集统计信息的是以下几个存储过程：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
GATHER_DATABASE_STATS
     --This procedure gathers statistics for all objects in the database.
GATHER_DICTIONARY_STATS
     --This procedure gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components.
GATHER_FIXED_OBJECTS_STATS
     --This procedure gathers statistics for all fixed objects (dynamic performance tables).
GATHER_INDEX_STATS
     --This procedure gathers index statistics. It attempts to parallelize as much of the work as possible. Restrictions are described in the individual parameters. This operation will not parallelize with certain types of indexes, including cluster indexes, domain indexes, and bitmap join indexes. The granularity and no_invalidate arguments are not relevant to these types of indexes.
GATHER_SCHEMA_STATS
     --This procedure gathers statistics for all objects in a schema.
GATHER_SYSTEM_STATS
     --This procedure gathers system statistics.
GATHER_TABLE_STATS
     --This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.

三个常用Procedure用法详解：GATHER_SCHEMA_STATS（两种用法）、GATHER_TABLE_STATS、GATHER_INDEX_STATS

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
PROCEDURE  GATHER_SCHEMA_STATS
  Argument  Name           Type             In / Out  Default ?
  ------------------------------ ----------------------- ------ --------
  OWNNAME            VARCHAR2         IN
  ESTIMATE_PERCENT   NUMBER           IN      DEFAULT
  BLOCK_SAMPLE       BOOLEAN          IN      DEFAULT
  METHOD_OPT         VARCHAR2         IN      DEFAULT
  DEGREE             NUMBER           IN      DEFAULT
  GRANULARITY        VARCHAR2         IN      DEFAULT
  CASCADE             BOOLEAN          IN      DEFAULT
  STATTAB            VARCHAR2         IN      DEFAULT
  STATID             VARCHAR2         IN      DEFAULT
  OPTIONS            VARCHAR2         IN      DEFAULT
  OBJLIST            OBJECTTAB        OUT
  STATOWN            VARCHAR2         IN      DEFAULT
  NO_INVALIDATE      BOOLEAN          IN      DEFAULT
  GATHER_TEMP        BOOLEAN          IN      DEFAULT
  GATHER_FIXED       BOOLEAN          IN      DEFAULT
  STATTYPE           VARCHAR2         IN      DEFAULT
  FORCE               BOOLEAN          IN      DEFAULT
  OBJ_FILTER_LIST    OBJECTTAB        IN      DEFAULT
 
PROCEDURE  GATHER_SCHEMA_STATS
  Argument  Name           Type             In / Out  Default ?
  ------------------------------ ----------------------- ------ --------
  OWNNAME            VARCHAR2         IN
  ESTIMATE_PERCENT   NUMBER           IN      DEFAULT
  BLOCK_SAMPLE       BOOLEAN          IN      DEFAULT
  METHOD_OPT         VARCHAR2         IN      DEFAULT
  DEGREE             NUMBER           IN      DEFAULT
  GRANULARITY        VARCHAR2         IN      DEFAULT
  CASCADE             BOOLEAN          IN      DEFAULT
  STATTAB            VARCHAR2         IN      DEFAULT
  STATID             VARCHAR2         IN      DEFAULT
  OPTIONS            VARCHAR2         IN      DEFAULT
  STATOWN            VARCHAR2         IN      DEFAULT
  NO_INVALIDATE      BOOLEAN          IN      DEFAULT
  GATHER_TEMP        BOOLEAN          IN      DEFAULT
  GATHER_FIXED       BOOLEAN          IN      DEFAULT
  STATTYPE           VARCHAR2         IN      DEFAULT
  FORCE               BOOLEAN          IN      DEFAULT
  OBJ_FILTER_LIST    OBJECTTAB        IN      DEFAULT
  
PROCEDURE  GATHER_TABLE_STATS
  Argument  Name           Type             In / Out  Default ?
  ------------------------------ ----------------------- ------ --------
  OWNNAME            VARCHAR2         IN
  TABNAME            VARCHAR2         IN
  PARTNAME           VARCHAR2         IN      DEFAULT
  ESTIMATE_PERCENT   NUMBER           IN      DEFAULT
  BLOCK_SAMPLE       BOOLEAN          IN      DEFAULT
  METHOD_OPT         VARCHAR2         IN      DEFAULT
  DEGREE             NUMBER           IN      DEFAULT
  GRANULARITY        VARCHAR2         IN      DEFAULT
  CASCADE             BOOLEAN          IN      DEFAULT
  STATTAB            VARCHAR2         IN      DEFAULT
  STATID             VARCHAR2         IN      DEFAULT
  STATOWN            VARCHAR2         IN      DEFAULT
  NO_INVALIDATE      BOOLEAN          IN      DEFAULT
  STATTYPE           VARCHAR2         IN      DEFAULT
  FORCE               BOOLEAN          IN      DEFAULT
  
PROCEDURE  GATHER_INDEX_STATS
  Argument  Name           Type             In / Out  Default ?
  ------------------------------ ----------------------- ------ --------
  OWNNAME            VARCHAR2         IN
  INDNAME            VARCHAR2         IN
  PARTNAME           VARCHAR2         IN      DEFAULT
  ESTIMATE_PERCENT   NUMBER           IN      DEFAULT
  STATTAB            VARCHAR2         IN      DEFAULT
  STATID             VARCHAR2         IN      DEFAULT
  STATOWN            VARCHAR2         IN      DEFAULT
  DEGREE             NUMBER           IN      DEFAULT
  GRANULARITY        VARCHAR2         IN      DEFAULT
  NO_INVALIDATE      BOOLEAN          IN      DEFAULT
  STATTYPE           VARCHAR2         IN      DEFAULT
  FORCE               BOOLEAN          IN      DEFAULT

GATHER_SCHEMA_STATS参数详解：（其他存储过程的参数解释参见官方页面，很多参数description都是通用的）

详见： Table 103-30 GATHER_SCHEMA_STATS Procedure Parameters

一些实际用例：

1
2
3
EXEC  DBMS_STATS.GATHER_SCHEMA_STATS( 'SCOTT' ,estimate_percent=>80,method_opt=> 'FOR ALL COLUMNS SIZE AUTO' ,degree=>4, cascade => TRUE );
EXEC  DBMS_STATS.GATHER_TABLE_STATS( 'SCOTT' , 'EMP' ,estimate_percent=>80,method_opt=> 'FOR ALL COLUMNS SIZE AUTO' ,degree=>4, cascade => TRUE );
EXEC  DBMS_STATS.GATHER_INDEX_STATS( 'SCOTT' , 'PK_EMP' ,estimate_percent=>80,degree=>4);

一些特别提示：

  • 虽然method_opt的description中并未提及for table这个选项，但其实这个选项也是有效的，同analyze一样，这个参数在不同版本的表现也是不一样的，具体差异也可以轻易的使用本文中提供的SQL观察到。
  • 如果想使用compute方式收集统计信息，将estimate_percent设为100或者null即可。
  • Oracle有auto optimizer stats collection的自动维护任务定期的收集统计信息，这些任务是默认开启的，但当数据库变的很大之后就会引发严重的性能问题，建议只保留周末的一个窗口，其他窗口全部关闭。
  • 直方图统计信息并不是那么的重要，只有在遇到对倾斜列（skew）的查询很频繁时才有用，这种情况并不常见。
  • 不再推荐使用analyze来收集统计信息，除非是做测试或者表很小，dbms_stats的并行度选项能加快收集速度。
  • 对大表采样收集统计信息时一般采样比例不需要很大，通常10%到30%即可，如果业务可以提供维护窗口，那100%也没什么大不了。
  • 如果要详细了解统计信息收集了什么内容，可以参考本文提供的网址链接和视图。

关于执行权限：

To invoke this procedure you must be owner of the table, or you need the ANALYZE ANY privilege. For objects owned by SYS, you need to be either the owner of the table, or you need the ANALYZE ANY DICTIONARY privilege or the SYSDBA privilege.

当然关于权限还有个取巧的办法，示例如下：

1
2
3
4
5
6
7
8
9
conn hr/hr
create  or  replace  procedure  gather_stats  is
begin 
dbms_stats.gather_table_stats( 'HR' ,  'EMPLOYEES' );
end  gather_stats;
/ 
grant  select  on  hr.employees  to  scott;
grant  execute  on  gather_stats  to  scott; 
conn scott/scott   exec  hr.gather_stats;

相关 [oracle 统计 信息] 推荐：

Oracle 统计信息

- - 数据库 - ITeye博客
--Oracle 优化器统计信息 Oracle优化器统计信息描述了关于数据库和相关对象的统计信息,当执行SQL查询时,优化器会使用这些统计信息估算出各种不同的执行计划的资源消耗, ，从而选择最高效的执行计划. 当统计信息缺失或者陈旧时,Oracle可能会选择错误的执行计划,导致SQL执行效率低下.

[Oracle] 统计信息和dbms_stats包

- - CSDN博客数据库推荐文章
Oracle基于CBO的优化器在生成执行计划时，很大程度上依赖于统计信息，你可以把CBO理解为一个复杂的数学模型，而统计信息是它最主要的输入，执行计划是输出，如果输入都不准确，输出还可能准确吗. 所有，统计信息是否及时有效对执行计划的好坏有着关键的影响. 2、dbms_stats包. Oracle里采用dbms_stats包分析统计信息(Analyze命令已过时，不建议使用），该包的使用方法，官方文档有详细说明（ http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036461），这里挑几个最常用的说说.

Oracle 统计信息收集 - Leohahah - 博客园

- -
查询表上一次收集统计信息的时间：. 收集统计信息主要有2种方法：. analyze可以用来收集表，索引，列以及系统的统计信息和直方图，以下为一些典型用法：. --收集所有的统计信息和直方图信息，包括表、列、索引. --收集emp表的统计信息，不含列、索引统计信息和直方图. --收集所有列的统计信息和直方图（超大表较耗资源，因为只要列中有非空值，那么就会收集这个列的统计信息和直方图）.

Oracle 11g系统自动收集统计信息的一些知识

- - CSDN博客数据库推荐文章
---11g的是 周一到周五 22：00-2：00 周六周日 6：00-4：00. WINDOW_NAME          REPEAT_INTERVAL                                              DURATION. CLIENT_NAME                                                      STATUS.

Oracle里收集与查看统计信息的方法_DBA Fighting!的技术博客_51CTO博客

- -
Oracle数据库里的统计信息是这样的一组数据：它存储在数据字典里，且从多个维度描述了Oracle数据库里对象的详细信息. CBO会利用这些统计信息来计算目标SQL各种可能的、不同的执行路径的成本，并从中选择一条成本值最小的执行路径来作为目标SQL的执行计划. Oracle数据库里的统计信息可以分为如下6种类型：.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前， Ksplice 为 Fedora, Ubuntu 免费提供该功能，对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中，同时停止对其他企业级 Linux 发行版的支持，将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice，MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统，为 Kernel 打补丁无需重启动，做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户，超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

oracle license计算

- Fenng - eagle's home
Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的，可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.

Oracle Exadata初探

- - 技术改变世界 创新驱动中国 - 《程序员》官网
在我们看来，它是一个把硬件和软件根据合理的配置整合在一起的 Oracle数据库（在本文编写时是11gR2版本）平台. Exadata数据库机器包含了存储子系统，在存储层上运行着研发的新软件，这使得研发人员可以做一些在其他平台上无法完成的事情. 实际上，Exadata一开始是以一个存储系统形式诞生的，如果你跟参与研发此产品的人交谈，你经常会听到他们称存储组件为Exadata或者是SAGE （Storage Appliance for Grid Environments，网格环境存储设备），这是该产品研发项目的代码名称.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇，这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来，也算是前面博文中PPT的一个文字版解读吧. Oracle，MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大，国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业，包括运营商以及金融机构都已经开始加入到这个潮流之中.