oracle收集统计信息job停止

缺乏、安全感 2022-06-18 04:46 293阅读 0赞

author：skate  
time：2011/09/26

Oracle10g增加了一个新的任务，用来自动分析数据库，用于增加CBO执行的正确性。这个任务就是GATHER\_STATS\_JOB，  
任务定义周一到周五的夜晚执行，和周六日全天执行。在oracle11g中job的名称有些不同。

oracle这个自动收集信息的出发点很好，但是，大部分系统晚上的负载也很高，或者执行计划改变，会引起新的性能问题。  
而且oracle这个自动收集信息的job非常耗性能。

oracle10g的系统自动job  
SQL> select job\_name,comments from dba\_scheduler\_jobs;  
   
JOB\_NAME                       COMMENTS  
\------------------------------ --------------------------------------------------------------------------------  
AUTO\_SPACE\_ADVISOR\_JOB         auto space advisor maintenance job  
GATHER\_STATS\_JOB               Oracle defined automatic optimizer statistics collection job  
FGR$AUTOPURGE\_JOB              file group auto-purge job  
PURGE\_LOG                      purge log job  
MGMT\_STATS\_CONFIG\_JOB          OCM Statistics collection job.  
MGMT\_CONFIG\_JOB                Configuration collection job.  
RLM$SCHDNEGACTION               
RLM$EVTCLEANUP                  
   
8 rows selected  
   
SQL>

oracle11g的自动job  
SQL> select job\_name,comments from dba\_scheduler\_jobs;  
   
JOB\_NAME                       COMMENTS  
\------------------------------ --------------------------------------------------------------------------------  
SM$CLEAN\_AUTO\_SPLIT\_MERGE      auto clean job for auto split merge  
RSE$CLEAN\_RECOVERABLE\_SCRIPT   auto clean job for recoverable script  
FGR$AUTOPURGE\_JOB              file group auto-purge job  
BSLN\_MAINTAIN\_STATS\_JOB        Oracle defined automatic moving window baseline statistics computation job  
DRA\_REEVALUATE\_OPEN\_FAILURES   Reevaluate open failures for DRA  
HM\_CREATE\_OFFLINE\_DICTIONARY   Create offline dictionary in ADR for DRA name translation  
ORA$AUTOTASK\_CLEAN             Delete obsolete AUTOTASK repository data  
FILE\_WATCHER                   File watcher job  
PURGE\_LOG                      purge log job  
MGMT\_STATS\_CONFIG\_JOB          OCM Statistics collection job.  
MGMT\_CONFIG\_JOB                Configuration collection job.  
   
11 rows selected  
   
SQL>

**11g中的几个job说明：**

1. ORA$AUTOTASK\_CLEAN  
The job is created by the 11g script catmwin.sql which mentions that this job is an autotask repository data ageing job.  
It runs the procedure ora$age\_autotask\_data.

2. HM\_CREATE\_OFFLINE\_DICTIONARY  
The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary  
for Database Repair Advisor.

The system job SYS.HM\_CREATE\_OFFLINE\_DICTIONARY executes the dbms\_hm.create\_offline\_dictionary package which creates a LogMiner offline dictionary in the ADR for DRA name translation service. The job for generating the logminer dictionary is scheduled during the maintenance window. This job can be disabled. ‘

3. DRA\_REEVALUATE\_OPEN\_FAILURES  
The job is created by the 11g script catmwin.sql which mentions that this is a job for reevaluate open failures for  
Database Repair Advisor. The job executes the procedure dbms\_ir.reevaluateopenfailures.

4. MGMT\_CONFIG\_JOB -

comes with the OCM(Oracle Configuration Manager) installation - This is a configuration collection job.  
The job is created by the script ocmjb10.sql by running procedure ‘ORACLE\_OCM.MGMT\_CONFIG.collect\_config’.

5. MGMT\_STATS\_CONFIG\_JOB  
This is an OCM Statistics collection job created in ocmjb10.sql by running ‘ORACLE\_OCM.MGMT\_CONFIG.collect\_stats’.

6. BSLN\_MAINTAIN\_STATS\_JOB (替换了10G的GATHER\_STATS job)  
This job replaces the old GATHER\_STATS job. It is a compute statistics job. This job runs the  BSLN\_MAINTAIN\_STATS\_PROG program on the BSLN\_MAINTAIN\_STATS\_SCHED schedule. The program BSLN\_MAINTAIN\_STATS\_PROG will keep the default baseline’s statistics up-to-date.

7. XMLDB\_NFS\_CLEANUP\_JOB  
The job is created in xdbu102.sql and runs the procedure dbms\_xdbutil\_int.cleanup\_expired\_nfsclients.

oracle10g，11gjob的工作原理

oracle是通过维护窗口来完成系统的自动job的，系统的维护窗口通过视图dba\_scheduler\_windows

SQL> select window\_name,resource\_plan,comments from dba\_scheduler\_windows;  
   
WINDOW\_NAME                    RESOURCE\_PLAN                  COMMENTS  
\------------------------------ ------------------------------ --------------------------------------------------------------------------------  
MONDAY\_WINDOW                  DEFAULT\_MAINTENANCE\_PLAN       Monday window for maintenance tasks  
TUESDAY\_WINDOW                 DEFAULT\_MAINTENANCE\_PLAN       Tuesday window for maintenance tasks  
WEDNESDAY\_WINDOW               DEFAULT\_MAINTENANCE\_PLAN       Wednesday window for maintenance tasks  
THURSDAY\_WINDOW                DEFAULT\_MAINTENANCE\_PLAN       Thursday window for maintenance tasks  
FRIDAY\_WINDOW                  DEFAULT\_MAINTENANCE\_PLAN       Friday window for maintenance tasks  
SATURDAY\_WINDOW                DEFAULT\_MAINTENANCE\_PLAN       Saturday window for maintenance tasks  
SUNDAY\_WINDOW                  DEFAULT\_MAINTENANCE\_PLAN       Sunday window for maintenance tasks  
WEEKNIGHT\_WINDOW                                              Weeknight window - for compatibility only  
WEEKEND\_WINDOW                                                Weekend window - for compatibility only  
   
9 rows selected  
   
SQL>

维护窗口组有哪些维护组  
select \* from dba\_scheduler\_wingroup\_members;

select t1.window\_name,  
       t1.repeat\_interval,  
       t1.duration,  
       t2.window\_group\_name  
  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\_MAINTAIN\_STATS\_SCHED');

常用视图：  
select  \* from dba\_scheduler\_programs  
select \* from dba\_scheduler\_jobs  
select \* from dba\_scheduler\_running\_jobs  
select \* from dba\_scheduler\_job\_run\_details  
select \* from dba\_scheduler\_schedules  
select \* from dba\_scheduler\_wingroup\_members

select \* from DBA\_AUTOTASK\_CLIENT\_JOB;  
select \* from DBA\_AUTOTASK\_CLIENT;  
select \* from DBA\_AUTOTASK\_JOB\_HISTORY;  
select \* from DBA\_AUTOTASK\_WINDOW\_CLIENTS;  
select \* from DBA\_AUTOTASK\_CLIENT\_HISTORY;

10g关闭自动收集job

select \* from Dba\_Scheduler\_Jobs where JOB\_NAME ='GATHER\_STATS\_JOB'

所以建议最好关闭自动统计信息收集功能：  
exec DBMS\_SCHEDULER.DISABLE('GATHER\_STATS\_JOB');

启动自动统计信息收集功能  
exec DBMS\_SCHEDULER.ENABLE('GATHER\_STATS\_JOB');

手工收集统计信息：  
SQL> exec dbms\_stats.gather\_schema\_stats('detail',options=>'gather stale',estimate\_percent =>10);

\--收集没有分析过的表的统计信息  
begin  
dbms\_stats.gather\_schema\_stats(ownname=>scott,options=>'gather empty');  
end;

\--重新分析修改量超过10%的表（这些修改包括插入、更新和删除）  
begin  
dbms\_stats.gather\_schema\_stats(ownname=>scott,options=>'gather stale');  
end;

11g关闭自动收集job  
The automated maintenance tasks infrastructure (known as AutoTask) schedules tasks to  
run automatically in Oracle Scheduler windows known as maintenance windows. By default,  
one window is scheduled for each day of the week. Automatic optimizer statistics collection  
runs as part of AutoTask and is enabled by default to run in all predefined maintenance windows.

If for some reason automatic optimizer statistics collection is disabled, then you can enable  
it using the ENABLE procedure in the DBMS\_AUTO\_TASK\_ADMIN package:

BEGIN  
  DBMS\_AUTO\_TASK\_ADMIN.ENABLE(  
    client\_name => 'auto optimizer stats collection',  
    operation => NULL,  
    window\_name => NULL);  
END;  
/  
When you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS\_AUTO\_TASK\_ADMIN package:

BEGIN  
  DBMS\_AUTO\_TASK\_ADMIN.DISABLE(  
    client\_name => 'auto optimizer stats collection',  
    operation => NULL,  
    window\_name => NULL);  
END;  
/

手工收集统计信息：  
SQL> exec dbms\_stats.gather\_schema\_stats('detail',options=>'gather stale',estimate\_percent =>10);

参考文档  
[http://download.oracle.com/docs/cd/E11882\_01/server.112/e16638/stats.htm\#PFGRF94713][http_download.oracle.com_docs_cd_E11882_01_server.112_e16638_stats.htm_PFGRF94713]

\-----end-----

[http_download.oracle.com_docs_cd_E11882_01_server.112_e16638_stats.htm_PFGRF94713]: http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94713