Does GATHER_STATS_JOB gather all objects’ stats every time?

Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4 – Release: 10.2 to 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
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.)

Applies to:
Oracle Server – Enterprise Edition – Version: 11.1.0.6 and later [Release: 11.1 and later ]
Information in this document applies to any platform.
***Checked for relevance on 13-Oct-2010***
Goal
Question: Why Has the GATHER_STATS_JOB been removed in 11g?
Solution
Answer:

Oracle Database 10g introduced the concept automated maintenance task execution during maintenance windows implemented via a WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedule. This included statistics collection by means of the GATHER_STATS_JOB.

In Oracle Database 11g, the WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedules (defined in Oracle Database 10g) are replaced with daily maintenance windows (such as SUNDAY_WINDOW, MONDAY_WINDOW etc). These were replaced in order to add increased flexibility and manageability. One can query dba_scheduler_windows to check the window definitions.

Automatic Maintenance Tasks (including the automated Optimizer Statistics Gathering task) are defined to execute within these daily windows. Using Enterprise Manager is the preferred way to control Automatic Maintenance Tasks, although the DBMS_AUTO_TASK_ADMIN package can also be used.

‘Automatic Maintenance Tasks Management ‘ is an 11g new feature and was implemented to increase the flexibility of statistics collection and to avoid potential resourcing issue when maintenance jobs run alongside user operations. Maintenance operations can potentially use a lot of resource which may, in extreme cases, affect other jobs. To address this, in 11g, maintenance operations are closely linked to resource manager to manage the resources that are used and share them more efficiently.

From the

Oracle� Database New Features Guide
11g Release 1 (11.1)
Part Number B28279-03

“This feature ensures that work during maintenance operations is not affected and that user activity gets the necessary resources to complete.”

By spreading the workload over multiple weeknights and managing the resource usage, this feature provides the flexibility to allow maintenance jobs to complete in a timely fashion and for user operations to be unaffected.

Users are still completely free to define other maintenance windows and change start times and durations for the daily maintenance windows.

Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.3 – Release: 10.1 to 10.2
Information in this document applies to any platform.
Symptoms

GATHER_STATS_JOB scheduler job is not running in your database.
It could be that you recently applied a DST patch or upgraded the database by applying 10.2.0.3 patch set ,etc

SQL> select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name=
2 ‘GATHER_STATS_JOB’;

OWNER JOB_NAME STATE SCHEDULE_NAME
—– ——————– ————— ———————

SYS GATHER_STATS_JOB SCHEDULED MAINTENANCE_WINDOW_GROUP

To check the members under this group , use

SQL> SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

WINDOW_GROUP_NAME WINDOW_NAME
—————————— ——————–
MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

clear col
set lines 150 pages 99 trimspool on echo on
col window_name for a16
col resource_plan for a13
col repeat_interval for a43
col duration for a15
col enabled for a10
col active for a10

select window_name, resource_plan,
repeat_interval, duration, enabled, active
from dba_scheduler_windows;

1 select WINDOW_NAME,RESOURCE_PLAN, START_DATE,REPEAT_INTERVAL,END_DATE,DURATION,ENABLED,ACTIVE
2* from dba_scheduler_windows
SQL> /

WINDOW_NAME RESOURCE_PLAN START_DATE REPEAT_INTERVAL END_DATE
——————– ——————– ——————– —————————— ——————–
DURATION ENABL ACTIV
——————– —– —–
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,T
HU,FRI;byhour=22;byminute=0; b
ysecond=0
+000 08:00:00 TRUE TRUE

WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;
byminute=0;bysecond=0
+002 00:00:00 TRUE FALSE

The job status is shown as scheduled and the windows under the maintenance window group are also enabled

In case the job is disabled (STATE under dba_scheduler_jobs) , then use

EXEC DBMS_SCHEDULER.ENABLE (‘GATHER_STATS_JOB’);

In case the window is disabled (ENABLED under dba_scheduler_windows) , then use

EXEC DBMS_SCHEDULER.ENABLE (‘SYS.WINDOWNAME’);

Here the WINDOWNAME has to be replaced with the window which is disabled

Also, remember that for GATHER_STATS_JOB to properly work, you would have to set the STATISTICS_LEVEL initialization parameter to at least TYPICAL.

Cause
Bug 5688806 has been filed for one of such cases, but the bug was suspended as required information was not available.
Solution
As a workaround for this issue,run the following scripts which would recreate the windows and stats job

10.2
=====
SQL> exec dbms_scheduler.drop_job(‘auto_space_advisor_job’);

Windows
SQL> @%ORACLE_HOME%/rdbms/admin/catnomwn.sql

OR

Unix
SQL>@$ORACLE_HOME/rdbms/admin/catnomwn.sql
Above script will drop the window group, windows, job and job class

Windows
SQL> @%ORACLE_HOME%/rdbms/admin/catmwin.sql

OR

Unix
SQL>@$ORACLE_HOME/rdbms/admin/catmwin.sql

Above script will create all the required objects including auto_space_advisor_job

10.1
=====

Windows
SQL> @%ORACLE_HOME%/rdbms/admin/catnomwn.sql

OR

Unix
SQL>@$ORACLE_HOME/rdbms/admin/catnomwn.sql
Above script will drop the window group, windows, job and job class

Windows
SQL> @%ORACLE_HOME%/rdbms/admin/catmwin.sql

OR

Unix
SQL>@$ORACLE_HOME/rdbms/admin/catmwin.sql

Note: – AUTO_SPACE_ADVISOR_JOB runs the Automatic Segment Advisor, which identifies segments that have space available for reclamation, and then makes recommendations that you can view with Enterprise Manager or a set of PL/SQL package procedures. This job was introduced in 10gR2 and is not present in 10gR1

In case the job still does not run, then please enable the cjq tracing by setting the following event. This needs to be set before the scheduled run i.e 10 P.M so maybe you can start it at 9:45 PM

connect / as sysdba
oradebug setospid <--This is OS process id for Cjq process (You can also use SETORAPID ) where orapid is Oracle process id for Cjq process)
oradebug tracefile_name
oradebug Event 27402 trace name context forever, level 37

Wait for 1 hour. Then turn it off

oradebug Event 27402 trace name context off
oradebug tracefile_name

Upload the trace file.

Also upload following query results to the SR.

Rem Pls run as sys i.e “connect / as sysdba” on each instance
set pagesize 2000
set echo on
column OBJECT_NAME format a30
spool job

select object_id, OBJECT_NAME from dba_objects
where OBJECT_TYPE = ‘JOB’;
select * from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB’;
select * from DBA_SCHEDULER_JOB_LOG where job_name =’GATHER_STATS_JOB’;
select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name =’GATHER_STATS_JOB’;
spool off

Rem Internal Job Queues Summary report.
Rem Pls run as sys i.e “connect / as sysdba” on each instance

column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column window_name format a10
column job_queue_name format a18
column job_type format 9999
column flag format 9999
column status format a6
column “SEQUENCE#” format 9999
column id format 99999

set pagesize 100
set echo on
spool scheduler_qs

— Report Internal Queues
–select job_queue_name, JOB_TYPE,
— (select object_name from dba_objects where object_id = job_oid union select name from
— scheduler$_lwjob_obj where obj# = job_oid) OBJ_NAME, FLAG, start_time from x$jskjobq;
— scheduler$_lwjob_obj is not available

select * from x$jskjobq;
— Report current time in scheduler format
select dbms_scheduler.stime from dual;

— Report all global attributes
SELECT o.name, a.*
FROM sys.obj$ o, sys.scheduler$_global_attribute a
WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;

— Report resource manager info
select * from v$rsrc_plan;
select * from v$rsrc_plan_history;
select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
select * from v$rsrc_consumer_group;
select state from v$rsrc_session_info where state != ‘NOT MANAGED’ ;
select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;

Rem Scheduler job and window logs
column additional_info format a40
set pagesize 1000
set linesize 150
column REQ_START_DATE format a37
column ACTUAL_START_DATE format a37
column log_date format a37
column window_name format a10
column operation format a9
column error# format 99999
column status format a9
column CPU_USED format a16
column job_name format a18
column run_duration format a15
connect / as sysdba
select log_date, owner, job_name, operation, status from dba_scheduler_job_log
union
select log_date, ‘SYS’, window_name, operation, status from
dba_scheduler_window_log
order by 1;

Rem select log_date, actual_start_date-req_start_date, job_name, error#, status, run_duration,
Rem cpu_used, additional_info
Rem from dba_scheduler_job_run_details order by log_id;

select job_name, CAST (actual_start_date-req_start_date AS INTERVAL DAY(2) TO SECOND(2)) DELAY
from dba_scheduler_job_run_details order by log_id;

Rem select additional_info from dba_scheduler_job_run_details where additional_info is not null;

spool off;

Rem Internal Job Queues Summary report for 10.2.
Rem Pls run as sys i.e “connect / as sysdba” on each instance
set long 400
column log_date format a37
column operation format a8
column status format a6
column additional_info format a400

set pagesize 1000
set linesize 120
column systimestamp format a37
column next_start_date format a40
column start_date format a40
column manual_open_time format a40
column manual_duration format a40
column duration format a40
column end_date format a40
column last_start_date format a40
column window_name format a26
column enabled format a6
column active format a6
column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column job_queue_name format a18
column job_type format 9999
column flag format 9999
column status format a6
column “SEQUENCE#” format 9999
column id format 99999

spool wintest
select * from dba_scheduler_windows ;

select log_date, window_name, operation, status, substr(additional_info,1,350) from
dba_scheduler_window_log
where operation != ‘CREATE’
order by 1;
select log_date, window_name, operation,substr(additional_info,1,350) from
dba_scheduler_window_log order by 1 ;
select window_name, substr(additional_info,1,350) x from
dba_scheduler_window_log where additional_info is not null;

— Report Internal Queues v10.2
select job_queue_name, JOB_TYPE,
(select object_name from dba_objects where object_id = job_oid
) OBJ_NAME, FLAG, start_time from x$jskjobq;

— Report current time in scheduler format
select dbms_scheduler.stime from dual;

— Report all global attributes
SELECT o.name, a.*
FROM sys.obj$ o, sys.scheduler$_global_attribute a
WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;

— Report resource manager info
select * from v$rsrc_plan;
select * from v$rsrc_plan_history;
select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
select * from v$rsrc_consumer_group;
select state from v$rsrc_session_info where state != ‘NOT MANAGED’ ;
select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;
spool off;