oracle gather_stats_job,诊断 GATHER_STATS_JOB问题

', 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_GROUPSQL> 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.