Oracle统计信息收集详解

大家好，我是RomMr一个乐于分享的it男！今天和大家分享我在工作中整理的Oracle统计信息相关知识

1、涉及统计信息的视图和表

1.1列统计信息相关视图和表

User_tab_columns

All_tab_columns

Dba_tab_columns

1.2 直方图统计信息相关视图和表

User_tab_histograms

All_tab_histograms

Dba_tab_histograms

1.3 表统计信息相关的视图和表

User_tab_statistics

All_tab_statistics

Dba_tab_statistics

1.4 查询相关表的统计信息收集时间和锁定状态

Select last_analyzed，stattype_locked from user_tab_statistics where table_name=’xxx’

2、收集统计信息的方式

2.1 analyze

analyze 可以用来收集表，索引，列的统计信息和直方图：

2.1.1 收集所有的统计信息和直方图，包括表、列、索引

Analyze table scott.emp compute statistics；

2.1.2 收集emp表的统计信息，不包含列、索引和直方图

Analyze table scott.emp compute statistics for table；

2.1.3 收集所有列的统计信息和直方图（较大表较耗资源）

Analyze table scott.emp compute statistics for all columns；

2.1.4 收集所有索引列的统计信息

Analyze table scott.emp compute statistics for all indexed columns；

2.1.5 收集所有索引的统计信息和直方图

Analyze table scott.emp compute statistics forindexes；

2.1.6 收集多列的统计信息和直方图

Analyze table scott.emp compute statistics for columns 列1、列2……；

2.1.7 删除索引ind1的统计信息

Analyze index ind1 delete statistics；

2.1.8 删除表t1的统计信息

Analyze table t1 delete statistics；

2.1.9 以估算的模式采样比例为15%来收集表的统计信息

Analyze table scott.emp estimate statistics sample 15 percent for table；

注：在收集直方图时for字句还可以加size字句，size的取值范围是1-254，默认是75，表示直方图的buckets最大数目

2.1.10直方图

直方图时一种特殊的列统计信息，只有列才有直方图

2.2调用dbms_stats包(介绍常用的三个)

2.2.1收集用户所有对象的统计信息

Dbms_stats.gather_schema_stats()

例：exec dbms_stats.gather_schema_stats(‘scott’,estimate_percent=>10,method_opt=>’for all columns   size auto’,degree=>4,cascade=>true)

2.2.2 收集表的统计信息

Dbms_stats.gather_table_stats()

例：exec dbms_stats.gather_table_stats(‘scott’,’emp’,estimate_percent=>10,method_opt=>’for all columns size auto’,degree=>4,cascade=true,)

2.2.3 收集索引的统计信息

Dbms_stats.gather_index_stats()

例：exec dbms_stats.gather_index_stats(‘scott’,’pk_emp’,estimate_percent=>10,degree=>4;

3、统计信息管理

3.1 锁定统计信息

Exec dbms_stats.lock_table_stats(‘scott’,’enp’,cascade=>true);

3.2 解锁统计信息

Exec dbms_stats.unlock_table_stats(scott’,’enp’,cascade=>true);

3.3 删除统计信息

Exec dbms_stats.delete_table_stats(scott’,’enp’,cascade=>true);

3.4 管理自动统计信息收集

3.4.1查看是否开启

Select client_name,status from  dba_autotask_client;

3.4.2 查看收集频率

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_MAIN TAIN_STATS_SCHED’);

3.4.3 关闭自动统计收集

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

3.4.4 启动自动收集统计

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

4、dbms_stats和analyze的区别

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

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

4.3 analyze不能并行收集统计信息，而dbms_stats可以通过加上degree=>来实现

5、其他提示

5.1虽然method_opt的description中并未提及for table这个选项，但其实这个选项也是有效的，同analyze一样，这个参数在不同版本的表现也是不一样的，具体差异也可以轻易的使用本文中提供的SQL观察到。

5.2 如果想使用compute方式收集统计信息，将estimate_percent设为100或者null即可。

5.3 Oracle有auto optimizer stats collection的自动维护任务定期的收集统计信息，这些任务是默认开启的，但当数据库变的很大之后就会引发严重的性能问题，建议只保留周末的一个窗口，其他窗口全部关闭。

今天的分享就到这里，希望大家能多多支持，点赞、收藏、转发、关注，大家支持是我持续更新的动力！