[zt] Oracle10g 自动收集收集CBO统计信息设置

从Oracle Database 10g开始，Oracle在建库后就默认创建了一个名为GATHER_STATS_JOB的定时任务，用于自动收集CBO的统计信息。

这个自动任务默认情况下在工作日晚上10：00-6：00和周末全天开启(工作日晚10点运行,  周六,日早6点跑,  时间不是很长 )。调用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

然而这个自动化功能已经影响了很多系统的正常运行，晚上10点对于大部分生产系统也并非空闲时段。
而自动分析可能导致极为严重的闩锁竞争，进而可能导致数据库Hang或者Crash。

所以建议最好关闭这个自动统计信息收集功能：
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

自动化永远与严重的隐患相伴随！

对于易变对象的变化，可以人工收集统计信息(DBMS_STATS)外 主要两种处理方式：

一种就是删除统计信息。使它的统计信息为空，对于任何统计信息缺失的表,oracle会用动态取样特性自动产生统计信息。如果使用久的统计信息 就可能产生错误的执行计划。
需要设置optimizer_dynamic_sampling为2（ORACLE10G默认值）或以上都可以启动此特性。

optimizer_dynamic_sampling ,提供在SQL分析的时候，自动根据不同的Level（0-10）以不同的准确度分析SQL中未被analyze过的表，意在为CBO提供更多的统计信 息。在Oracle9iR2中引入，默认为Level 1，10g默认为2 。

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1135.htm#REFRN10140

Level 0: Do not dynamically sample the table(s)

Level 1：Sample tables that have not been analyzed if there is more than one table in the query,the table in question has not been analyzed and it has no indexes,and the optimizer determines that the query plan would be affected based on the size of this objects

Level 2:Sample all unanalyzed tables referenced in the query using default sampling amounts(small sample)

Level 3 -- Level 10 ........更详细的sample而已。

例如：dbms_stats.delete_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)

另一种就是设置为表进行锁定,这样就可以不更新统计信息（注意LOCK表以后，表就不能修改了）。

dbms_stats.lock_table_stats(‘table_name’ ,‘VOLATILE_TABLE’)；  

 

http://hi.baidu.com/dbatao/blog/item/f3effdae2791cccb7dd92a4a.html   

 

 

 

Metalink 上的解释：

Subject:  How to check what automatic statistics collection is scheduled on 10g
  Doc ID:  Note:377143.1 Type:  BULLETIN
  Last Revision Date:  16-FEB-2007 Status:  PUBLISHED
***
This article is being delivered in Draft form. and may contain errors.
Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article.
PURPOSE

This Note provides information on the How to check what automatic statistics collection is scheduled on 10g

SCOPE AND APPLICATION

Users collecting statistics on database objects for use by the Cost Based Optimizer (CBO)


How to check what automatic statistics collection is scheduled on 10g

With Oracle 10g, the gathering of statistics has become automated.
The GATHER_STATS_JOB that is built in the database creation process schedules automatic statistics collection.
The job initiates a 'program' of statistics gathering appropriate for the database in question.

The job details can be viewed by querying the DBA_SCHEDULER_JOBS view:
  select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB';

  JOB_NAME                       JOB_TYPE         PROGRAM_NAME        SCHEDULE_NAME             JOB_CLASS
  ------------------------------ ---------------- ------------------- ------------------------- ------------------------------
  GATHER_STATS_JOB                                GATHER_STATS_PROG   MAINTENANCE_WINDOW_GROUP  AUTO_TASKS_JOB_CLASS

The output shows that the 'GATHER_STATS_JOB' schedules a program 'GATHER_STATS_PROG' in the 'MAINTENANCE_WINDOW_GROUP' time schedule.
The PROGRAM_NAME 'GATHER_STATS_PROG' starts the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC stored procedure:
  select  PROGRAM_ACTION
  from dba_scheduler_programs
  where PROGRAM_NAME = 'GATHER_STATS_PROG';

  PROGRAM_ACTION
  ----------------------------------------------
  dbms_stats.gather_database_stats_job_proc

The job is scheduled according to the value of the SCHEDULE_NAME field.
In this example, the schedule being used is: 'MAINTENANCE_WINDOW_GROUP'.
This schedule is defined in the DBA_SCHEDULER_WINGROUP_MEMBERS view:
  select *
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

  WINDOW_GROUP_NAME         WINDOW_NAME
  ------------------------- ------------------------------
  MAINTENANCE_WINDOW_GROUP  WEEKNIGHT_WINDOW
  MAINTENANCE_WINDOW_GROUP  WEEKEND_WINDOW

The meaning of these 'windows' can be found in 'DBA_SCHEDULER_WINDOWS':
  select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')

  WINDOW_NAME       REPEAT_INTERVAL                                                        DURATION
  ----------------- ---------------------------------------------------------------------- -------------
  WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00
  WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00


The meaning of these entries is as follows:

The WEEKNIGHT_WINDOW is scheduled each week day at 10PM. and should last a maximum of 8 hours.
The WEEKEND_WINDOW is scheduled each Saturday at 0AM and should last 2 days max

If the START_DATE and END_DATE columns (Not shown) are NULL, then this job will run continuously.

All these definitions can be found in the $ORACLE_HOME/rdbms/admin/catmwin.sql script.

 

 

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

转载于:http://blog.itpub.net/35489/viewspace-592316/