10G 自动收集统计信息

最新推荐文章于 2021-04-03 17:09:15 发布

天高菜鸟飞

最新推荐文章于 2021-04-03 17:09:15 发布

阅读量840

点赞数

分类专栏： -----SQL优化文章标签： oracle

-----SQL优化专栏收录该内容

3 篇文章 0 订阅

订阅专栏

统计信息从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任

务，用于自动收集CBO的统计信息。

这个特性减少了由于sql语句统计失效或陈旧而导致性能很差的可能性,通过提高查询优化器的最佳的输出

提高sql执行的性能.

gather_stats_job

默认的,在数据库创建的时候就创建了gather_stats_job,执行

dbms_stats.gather_database_stats_job_proc过程,使用schedular.

默认的定义了两个窗口.

weekenight_window定义在下午10点到上午六点.从周一到周五.

weekend_window定义从上午12点到周一12点.

调用DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC收集统计信息。

该过程首先检测统计信息缺失和陈旧的对象。然后确定优先级，再开始进行统计信息。

说明：当做完统计信息后，如果对对象的行数修改达到10%，DBMS_STATS就认为是统计信息过旧。

可以通过以下查询这个JOB的运行情况：

SQL> select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB'

其实同在10点运行的Job还有一个AUTO_SPACE_ADVISOR_JOB：

 SQL> select JOB_NAME,LAST_START_DATE from dba_scheduler_jobs; 

 JOB_NAME               LAST_START_DATE 

-------------------    ---------------------------------------- 

AUTO_SPACE_ADVISOR_JOB    04-DEC-07 10.00.00.692269 PM +08:00 

GATHER_STATS_JOB      04-DEC-07 10.00.00.701152 PM +08:00 

FGR$AUTOPURGE_JOB 

PURGE_LOG            05-DEC-07 03.00.00.169059 AM PRC

默认的maintenance_window_group组包含这两个窗口.

gather_stats_job使用特定的计划任务叫auto_tasks_job_class.这个类是自动创建的,而且跟特定的资源组

联系,这个组叫做auto_task_consumer_group.这样确保任务使用auto_task_consumer_group.如果想控制 gather_stats_job的资源使用,之需要为maintenance_window_group定义一个资源管理者组来为

auto_task_cousumer_group分配资源.

为了让gather_stats_job工作正常,必须确定statistics_level初始化参数设置为typical.

改变gather_stats_job的计划任务

可以为定义的管理窗口来自定义开放时间.例如,可以改变他们的时间间隔和重复的频度.也可以添加资源计

划到这些窗口中,来控制 gather_stats_job使用的资源.

在oracle10g,可以看到特定表的统计,通过新的dbms_stats包的lock_table_stats过程.可以锁定一个表的

统计,来阻止自动统计收集,这样就可以使用动态的取样.也可以在某个时间点锁定可变的表的统计.

可以使用lock_schema_stats运行在sechma基本锁定统计.

可以查看user/all/dba_tab_statistics视图来看stattype_locked列来看一个表是不是被锁定.
然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。

关闭及开启自动搜集功能，有两种方法，分别如下：

方法一：

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');

方法二：

alter system set "_optimizer_autostats_job"=false scope=spfile;

alter system set "_optimizer_autostats_job"=true scope=spfile;

Pfile可以直接修改初始化参数文件，重新启动数据库。

转载一篇文章：http://www.oracledatabase12g.com/archives/does-gather_stats_job-gather-all-object-stats-every-time.html

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题，这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB；当问及应用程序开发商为何要禁用自动统计信息收集时，开发商的一位工程师说因为该库的数据量较大，考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。

这里就存在一个问题，GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗？细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的，同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。

10g的官方文档中对该GATHER_STATS_JOB描述为”The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.Thestop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for thestop_on_window_close attribute isTRUE, causing Scheduler to terminateGATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.TheGATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).”

以上这段描述还是比较清晰的，MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束，在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时（你不难发现这2个窗口在周六0点到6点之间存在overlay，实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点，具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列，这里不再赘述)。在数据库一直打开的情况下，GATHER_STATS_JOB会伴随维护窗口一起被启动，默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close)，剩下的有待收集信息的对象将在下一个维护窗口中得到处理；如果数据库一直处于关闭的状态，并在某维护窗口的时间范围内该DB被打开，那么相应的维护窗口会被立即激活(ACTIVE)，同时GATHER_STATS_JOB自动作业也会被启动，但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了GATHER_STATS_JOB的运行周期，和我们要介绍的问题没有直接的联系。我们这里要谈的是，GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍；以上引用的文字中介绍了该JOB挑选分析对象的条件，即:

对象之前从未收集过统计信息，或由于某些原因没有统计信息
对象的统计信息相对陈旧(stale)，是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然，剔除一些复杂的情况，一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中，并且用户没有手动地去收集过统计信息，那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升，为了遏制这种势头有必要再次统计这些对象。

让我们来看看GATHER_STATS_JOB针对”陈旧”(stale)统计信息的实际表现:

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------
www.oracledatabase12g.com

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> conn maclean/maclean
Connected.

SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001;
Table created. 

SQL> select count(*)  from need_analyze;

  COUNT(*)
----------
     10000

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------

/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */

/* 手动调用GATHER_STATS_JOB自动作业 */

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

/* 删除999条记录，即不到10%的数据 */

SQL> delete need_analyze where rownum<1000;
999 rows deleted. 

SQL> commit;
Commit complete.

/* 再次调用GATHER_STATS_JOB */

begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

/* 可以看到统计信息并未被更新 */

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL> delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL>  delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
      8999	   20

/* 可以看到修改的行数必须超过10%后才会被收集 */

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作，而DML操作又涉及到了多少行数据呢？这都是通过表监控特性(a table monitoring facility)来实现的，当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断，并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

SQL> delete need_analyze;
8999 rows deleted.

SQL> commit;
Commit complete.

SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE';
no rows selected

/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */

/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
   将这些监控数据刷新到字典中  */

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed

SQL> col table_name for a20

SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';

TABLE_NAME		INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE		      0 	 0	 8999 26-MAR-11

/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
   NEED_ANALYZE表上删除过8999条记录，因为测试时仅用少量的串行DML,所以这里十分精确 */

SQL> set autotrace on;

/* 通过以上执行计划可以猜测，monitoring监控数据来源于MON_MODS_ALL$基表上 */

SQL> desc sys.MON_MODS_ALL$;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#						    NUMBER
 INSERTS					    NUMBER
 UPDATES					    NUMBER
 DELETES					    NUMBER
 TIMESTAMP					    DATE
 FLAGS						    NUMBER
 DROP_SEGMENTS					    NUMBER

SQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');

      OBJ#    INSERTS	 UPDATES    DELETES TIMESTAMP	   FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
     52565	    0	       0       8999 26-MAR-11	       0	     0

/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(modify)情况，
   并不能做为某张表的实际修改历史记录来利用 */

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了，但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗？这似乎有悖于我们的常识，试看下例子:

/ * NEED_ANALYZE现在扮演一张静态表，它上次被分析是在2011年3月26日 */

SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

SQL> select sysdate from dual;
SYSDATE
---------
26-MAR-11

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password: 

/* 我们把时钟调快到2012年的12月30日，希望我们能安全度过2012! */

[root@rh8 ~]# date -s "2012-12-30 00:00:00"
Sun Dec 30 00:00:00 CST 2012
[root@rh8 ~]# date
Sun Dec 30 00:00:01 CST 2012

[maclean@rh8 ~]$ exit
exit

SQL> startup;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size		    1218292 bytes
Variable Size		   75499788 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7168000 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;

SYSDATE
---------
30-DEC-12

/* 再次手动调用GATHER_STATS_JOB自动作业 */

SQL> set timing on;
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
  2    3    4
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.33

/* :-)，运行结束不到1s */

SQL>  select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

是的，默认情况下GATHER_STATS_JOB不会反复去分析那些静态表，无论过去”多久”。

好了，我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识，他们远没有我们想象的那么2，实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC，虽然这是一个内部存储过程(interal procedure)；但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致，主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象，这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制，而不论优先级。