ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 诊断 GATHER_STATS_JOB问题

诊断 GATHER_STATS_JOB问题

原创 Linux操作系统 作者:renjixinchina 时间:2012-06-20 12:15:57 0 删除 编辑

Step by Step Checklist

1. Check if the GATHER_STATS_JOB exists in the database:

SELECT object_id, object_name FROM dba_objects WHERE  object_type = 'JOB';

The output should look like:
OBJECT_ID            OBJECT_NAME
.....                ................ 
54901                GATHER_STATS_JOB
.....                ................  

If it is not appearing in the output of the query, then it does not exist and can be recreated
by running following scripts:
conn as sysdba
@ $ORACLE_HOME/rdbms/admin/catnomwn.sql
@ $ORACLE_HOME/rdbms/admin/catmwin.sql
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP', 'WEEKNIGHT_WINDOW');
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','WEEKEND_WINDOW');

(Though above two statements are included in catmwin script. but they are needed because of the reason given in check 6.)
   

2. Check the properties of the job GATHER_STATS_JOB:

SELECT owner, job_name, job_creator, schedule_owner, schedule_name, schedule_type, start_date, end_date, job_class, enabled, auto_drop, restartable, state,run_count, retry_count, last_start_date, last_run_duration, last_run_duration, next_run_date, logging_level FROM dba_scheduler_jobs WHERE job_name ='GATHER_STATS_JOB';

Ensure the following properties are set:

Attribute Name
Attribute Value

SCHEDULE_ NAME
MAINTENANCE_WINDOW_GROUP

SCHEDULE_TYPE
WINDOW_GROUP

ENABLED
TRUE

STATE
SCHEDULED

LOGGING_LEVEL
RUNS (This is not needed to run the job but to capture the logging information)


If any of the properties are not set or set incorrectly then the following method can be used to set it to a correct value:
SQL> exec sys.dbms_scheduler.disable( '"SYS"."GATHER_STATS_JOB"' );

SQL> exec sys.dbms_scheduler.set_attribute( name =>'"SYS"."GATHER_STATS_JOB"', attribute => '', value => 'ATTRIBUTE VALUE');

For example:
SQL> exec sys.dbms_scheduler.set_attribute( name =>'"SYS"."GATHER_STATS_JOB"', attribute => 'SCHEDULE_NAME', value => 'MAINTENANCE_WINDOW_GROUP');

SQL> exec sys.dbms_scheduler.enable( '"SYS"."GATHER_STATS_JOB"' );

3. Check the log details from the following query:

SQL> SELECT * from DBA_SCHEDULER_JOB_LOG where job_name ='GATHER_STATS_JOB';

4. Check the run details from the following query:

SQL> select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name ='GATHER_STATS_JOB';

5. Check the status of the scheduler windows:

select * from dba_scheduler_windows ;
-- Make sure that this query returns two rows; one for each WEEKEND_WINDOW and WEEKNIGHT_WINDOW.

If windows are not created, then recreate them using the script. given in check 1 above.

-- Check the REPEAT_INTERVAL
WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

The following command can be used to set/change the value of the repeat_interval: EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE( 'WEEKNIGHT_WINDOW','repeat_interval','freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=8;byminute=0;bysecond=0');

Above are the default values. They can be changed according to the need.

-- Check the NEXT_START_DATE to see when they will run next and check if this is the expected
date as given in the REPEAT_INTERVAL.

-- Check if both the windows are enabled:
ENABLED should be TRUE for both the windows.

Following command can be used to enable WINDOWS:
EXEC DBMS_SCHEDULER.ENABLE ('SYS.WEEKEND_WINDOW');


Please note that if ACTIVE is FALSE in this query then this should not be an issue. It indicates that windows are not active currently. This column will show as TRUE in their respective window time as specified in the REPEAT_INTERVAL attribute.

6. Check if the WINDOWS are part of MAINTENANCE_WINDOW_GROUP

SQL> SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;
The output should like following:
WINDOW_GROUP_NAME WINDOW_NAME
===================== ==================
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

If above two windows are not listed then add them using following commands:

exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP', 'WEEKNIGHT_WINDOW');
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','WEEKEND_WINDOW');


Please note that running the scripts catnomwn.sql and catmwin.sql may not add these windows to the MAINTENANCE_WINDOW_GROUP group for the following reason:


catnomwn.sql
===========
...........
...........
execute dbms_scheduler.drop_window_group('MAINTENANCE_WINDOW_GROUP');
...........
...........

When you run this script, the above stat will fail with the error: ORA-27479: Cannot drop "SYS.MAINTENANCE_WINDOW_GROUP" because other objects This error is reported because there are other jobs in the database which are attached with MAINTENANCE_WINDOW_GROUP. For example: AUTO_SPACE_ADVISOR_JOB


catmwin.sql
==========
BEGIN
 BEGIN
    dbms_scheduler.create_window_group('MAINTENANCE_WINDOW_GROUP');
    dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP', 'WEEKNIGHT_WINDOW');
    dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP', 'WEEKEND_WINDOW');
    EXCEPTION
     when others then if sqlcode = -27477 then NULL;
                      else raise;
                      end if;
  END;
dbms_scheduler.set_attribute('MAINTENANCE_WINDOW_GROUP','SYSTEM',TRUE);
EXCEPTION when others then if sqlcode = -27477 then NULL;
                      else raise;
                      end if;
END; /

 The above block will fail since the first statement will fail because MAINTENANCE_WINDOW_GROUP is already existing and because of exception it will come out. And next stats will not be executed.

7. Check STATISTICS_LEVEL parameter:

To run the 'GATHER_STATS_JOB' it is essential that the STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.

Check this with the following commands:
SQL> connect as sysdba
SQL> show parameters STATISTICS_LEVEL

In case this parameter is not set correctly, set it and check again if the job is then working automatically in the expected window.

8. Check for other equal or higher priority overlapping WINDOWS:

> If there is any other window with equal or higher priority already running then these windows will not be ACTIVE.

> If these windows are active and any other window with higher priority becomes active then these windows will be stopped.

Refer to following note for more details:
<742683.1> : Scheduled Job Works Does Not Start In Window.

9. Manually open the WINDOW (only for NON PRODUCTION databases):

Please note that check 9 should be performed only if it is a test database. Since enabling a WINDOW at the wrong time may create a huge load on the database, since all the JOBS associated with this WINDOW will start executing.

There may be two reasons why GATHER_STATS_JOB is still not running:
-- Even if WINDOWS are active, job does not run
-- WINDOWS are not coming to an ACTIVE state at the expected time and that is why JOB is not running.

We can check whether the job is getting executed or not by manually activating the window using following command:

execute dbms_scheduler.open_window('WEEKEND_WINDOW',null);


Check the WINDOW status in 'DBA_SCHEDULER_WINDOWS' and it will be shown as ACTIVE.
Check the DBA_SCHEDULER_JOB_RUN_DETAILS to see if the job has been run or not.

After running the above command and if the job is getting executed, then this means the issue is that the WINDOW is not getting activated at the expected time. We will need to diagnose the WINDOW further instead of the JOB.

The WINDOW can be manually closed using the following command:
execute dbms_scheduler.close_window('WEEKEND_WINDOW');

10. Recreate the GATHER_STATS_JOB and WINDOWS:

Please note that following this step will drop and recreate GATHER_STATS_JOB and WEEKEND_WINDOWS and WEEKNIGHT_WINDOWS. If you had made any changes to these window timings according to your business requirement, then you need to do those changes again.

Follow the steps listed below to recreate these objects:

conn as sysdba
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','WEEKNIGHT_WINDOW');
exec dbms_scheduler.add_window_group_member('MAINTENANCE_WINDOW_GROUP','WEEKEND_WINDOW');
(Though above two statements are included in catmwin script. but they are needed because of the reason given in check 6.)

11. Restart the database if possible:

If after confirming all the above checks if the job is still not running as expected, then sometimes restarting the database may be helpful to resolve the issue.

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

下一篇: telnet 关闭和启动
请登录后发表评论 登录
全部评论

注册时间:2011-01-30

  • 博文量
    373
  • 访问量
    2047809