Tuesday, May 1, 2012

GATHER_STATS_JOB

To check the job status in 10g:
select * from dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_run_details where job_name = 'GATHER_STATS_JOB';
select * from dba_scheduler_job_log where job_name = 'GATHER_STATS_JOB';

To Check the scheduler for this job:
select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS; 
If the state is not "Scheduled":
EXEC DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB');
if the window is not enabled:
EXEC DBMS_SCHEDULER.ENABLE ('SYS.WINDOWNAME');

You can also create the job on different user and execute it:
BEGIN
dbms_scheduler.create_Job (job_name=>'Gather_Stats_Job',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN Dbms_Stats.gather_database_stats(cascade=>true); end;',
start_date => sysdate,
repeat_interval => 'freq=daily',
enabled=>true);
end;
/

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

To disable gather_stats_job in 10g:
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
To drop the job, login as creator and run:
EXEC DBMS_SCHEDULER.DROP_JOB (JOB_NAME=>'GATHER_STATS_JOB', force => true);

In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor.
To enable the job
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
END;
/
 
To disable gather_stats_job (also known as optimizer stats) in 11g:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
To check the status:
select client_name, JOB_SCHEDULER_STATUS
from DBA_AUTOTASK_CLIENT_JOB
where client_name='auto optimizer stats collection';

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


To manually execute:
exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;  

No comments:

Post a Comment