Wednesday 1 February 2012

DB Stats Gather

As you might know, there is an automatically created SCHEDULER JOB in every 10g database which runs every night and checks for object which have either no statistics at all or for which the statistics have becomeSTALE (which means stat at least 10% of the values have changed). This job is call GATHER_STATS_JOB and belongs to the autotask job class. It uses a program which again call a procedure from built in package DBMS_STATS which does the statistics collection. This feature only works if the initialization parameterSTATISTICS_LEVEL is set to TYPICAL at least (which is the DEFAULT in 10g) and it utilizes the TABLE MONITORINGfeature.
But here we creating separate Job for the database statistics collection.
Disable the default database stats collection job:
BEGIN
      sys.dbms_scheduler.disable(name=>'"SYS"."GATHER_STATS_JOB"', force => TRUE);
END;

Ensure default DB stats job has been disabled by running following query. The value of Enabled column should be FALSE.

select
owner,
job_name,
job_creator,
job_action,
repeat_interval,
last_start_date,
last_run_duration,
enabled
from dba_scheduler_jobs


Create new job for the Database stats gather.


BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"SYS"."JOB_GATHER_DB_STATS"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent => NULL, block_sample => FALSE, method_opt => ''''FOR ALL COLUMNS SIZE 1'''', cascade => TRUE, options =>''''GATHER'''', gather_sys => TRUE);
end;
',
            number_of_arguments => 0,
            start_date =>  trunc(sysdate)+22/24,
            repeat_interval => 'FREQ=DAILY;BYHOUR=22;BYMINUTE=0;BYSECOND=0',
            end_date => NULL,
            job_class => '"SYS"."DEFAULT_JOB_CLASS"',
            enabled => FALSE,
            auto_drop => TRUE,
            comments => 'Daily Database Stats Gathering Job');

     
    SYS.DBMS_SCHEDULER.enable(
             name => '"SYS"."JOB_GATHER_DB_STATS"');
END;
/




No comments: