1. Check auto stats gather task assigned which group and check if that window-group is exists or not.

SELECT client_name, window_group FROM   dba_autotask_client;

2. Created Window Group (ORA$AT_WGRP_OS) as it was not exists

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('ORA$AT_WGRP_OS');
end;
/

3. Now we need to a window to that window-group. Thus created a new window and added.

BEGIN
dbms_scheduler.create_window(window_name=> 'MONDAY_WINDOW', duration =>  numtodsinterval(2, 'hour'),resource_plan=> 'DEFAULT_MAINTENANCE_PLAN',repeat_interval => 'FREQ=DAILY;byday=MON;byhour=22;byminute=0; bysecond=0');
end;
/

BEGIN
dbms_scheduler.add_window_group_member(group_name  => 'ORA$AT_WGRP_OS', window_list => ''MONDAY_WINDOW');
end;
/


Similarly created all windows and added to a group.

select * from dba_scheduler_wingroup_members;

4. Now enable the auto-task for gather statistics.

BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/

5. Query to check window timings and duration.

SELECT wgm.window_name, w.repeat_interval
FROM   dba_scheduler_wingroup_members wgm
       JOIN dba_scheduler_windows w ON wgm.window_name = w.window_name
WHERE  wgm.window_group_name = 'ORA$AT_WGRP_OS';

6. Query to check the client history

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed
FROM dba_autotask_client_history
WHERE client_name like '%stats%';

7. Query to check auto task history

SELECT client_name, job_status, job_start_time, job_duration
FROM dba_autotask_job_history
WHERE client_name like '%stats%'
ORDER BY job_start_time;

8. Query to check the task-name of the auto-task clients

SELECT client_name, task_name, task_target_type
FROM dba_autotask_task;

9. Program Name

SELECT program_name
FROM dba_scheduler_programs
WHERE program_name='GATHER_STATS_PROG';

References Sites:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/scheduse007.htm#i1011367
http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tasks004.htm#CIHCDBEC

http://www.oracle-base.com/articles/11g/AutomatedDatabaseMaintenanceTaskManagement_11gR1.php
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OAUTO1/Default.aspx
http://www.oracle-base.com/forums/viewtopic.php?f=1&t=11890

References Views:

dba_scheduler_wingroup_members
dba_scheduler_windows
dba_scheduler_window_groups

dba_autotask_client
dba_autotask_client_history
dba_autotask_job_history
DBA_AUTOTASK_CLIENT_JOB
dba_autotask_operation
dba_autotask_task
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY
DBA_AUTOTASK_SCHEDULE

There are five ways to recompile invalid objects in schema.

1. DBMS_DDL2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile

DBMS_DDL.ALTER_COMPILE

Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.

DBMS_UTILITY.COMPILE_SCHEMA

Definition

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed.

UTL_RECOMP

Definition

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.

Note: Required SYS user to run this package.

UTLRP.SQL scripts

Definition

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21

PL/SQL procedure successfully completed.


TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26

PL/SQL procedure successfully completed.

Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.

Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql

Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER

Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql

Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql

Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql

Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql

Objects need to recompile are:
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY