Oracle 10g: Checklist To Diagnose Issues Related To Automatic Run Of Job GATHER_STATS_JOB (Doc ID 80

Oracle 10g: Checklist To Diagnose Issues Related To Automatic Run Of Job GATHER_STATS_JOB (Doc ID 803191.1)

In this Document Goal   Solution   Step by Step Checklist   1. Check if the GATHER_STATS_JOB exists in the database:   2. Check the properties of the job GATHER_STATS_JOB:   3. Check the log details from the following query:   4. Check the run details from the following query:   5. Check the status of the scheduler windows:   6. Check if the WINDOWS are part of MAINTENANCE_WINDOW_GROUP   7. Check STATISTICS_LEVEL parameter:   8. Check for other equal or higher priority overlapping WINDOWS:   9. Manually open the WINDOW (only for NON PRODUCTION databases):   10. Recreate the GATHER_STATS_JOB and WINDOWS:   11. Restart the database if possible: APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.5.0 [Release 10.2] Information in this document applies to any platform. Checked for relevance on 19-Nov-2010 GOAL How to diagnose the issues where GATHER_STATS_JOB job is not running automatically in the maintenance windows. (WEEKEND_WINDOW, WEEKNIGHT_WINDOW) SOLUTION Data Collection: =========== > Download and run the Data Collection script from here > Output file : job_diag.html.  This script will provide formatted output from the queries required to collect information for diagnosing  GATHER_STATS_JOB issues. Please upload the output file to the SR. You can copy paste the Data Collection Script from here as well. set markup html on spool on spool job_diag.html SET echo on SELECT object_id, object_name FROM DBA_OBJECTS WHERE object_type = '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'; SELECT * FROM DBA_SCHEDULER_JOB_LOG WHERE job_name ='GATHER_STATS_JOB' ORDER BY log_date DESC; SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE job_name ='GATHER_STATS_JOB' ORDER BY log_date DESC; SELECT * FROM DBA_SCHEDULER_WINDOWS; SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS; SELECT SYSTIMESTAMP  FROM dual; SELECT dbms_scheduler.stime FROM dual ; show parameter STATISTICS_LEVEL; spool off set markup html off spool on   Please note that since it collects output in html format , the on screen output may not be readable. So please execute the complete script and review the job_diag.html file.   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 => '<ATTRIBUTE NAME>', 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.