2011-12-13 gather statistics

巩固知识点1：收集统计信息
包dbms_stats, ORACLE建议用这个代替analyze table

Oracle在文档Doc ID: 433240.1中描述了统计信息被锁定的可能性：
手工执行DBMS_STATS包的LOCK_TABLE_STATS过程；
使用imp或impdp，导入表的时候不加载数据，会锁住表的统计信息；
在升级过程中，队列表的统计信息可能被锁定。

11G的自动收集统计信息JOB: BSLN_MAINTAIN_STATS_JOB   替换了 10G 的 GATHER_STATS

以下是一些视图：
DBA_OPTSTAT_OPERATIONS   dba_scheduler_job_run_details 
dba_autotask_*

select * from dba_scheduler_jobs dsj,Dba_Scheduler_Schedules dss
where dsj.schedule_name=dss.schedule_name and dsj.job_name = 'BSLN_MAINTAIN_STATS_JOB' ;

http://www.oracledatabase12g.com/archives/does-gather_stats_job-gather-all-object-stats-every-time.html

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.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_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挑选分析对象的条件，即:

1. 对象之前从未收集过统计信息，或由于某些原因没有统计信息
2. 对象的统计信息相对陈旧(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视图来了解这些信息

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24383181/viewspace-713206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24383181/viewspace-713206/