Tuesday, February 28, 2012

Gather Statistics using DBMS_SCHEDULER



Schedule Job as below.

BEGIN  
DBMS_SCHEDULER.create_job (  
job_name    => 'GATHER_TABLE_STATS_EMP',  
job_type    => 'PLSQL_BLOCK',  
job_action   => 'begin dbms_stats.gather_table_stats(ownname =>''USERNAME'',tabname =>''EMP'',estimate_percent => 35,degree => 8,CASCADE=>TRUE,force => TRUE); END;',  
start_date   => '29-NOV-11 11.00.00AM US/eastern',  
repeat_interval => 'FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYHOUR=11;BYMINUTE=0',  
end_date    => NULL,  
enabled     => TRUE,  
comments    => 'GATHER STATS ON EMPTABLE');  
END;  
/

BEGIN  
 DBMS_SCHEDULER.run_job (job_name => 'GATHER_TABLE_STATS_EMP',  
 use_current_session => FALSE);  
END;  
/  

You can define repeat intervals of jobs, windows or schedules using the Scheduler's calendar syntax.

SET SERVEROUTPUT ON;
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MIS'; 

DECLARE                                
start_date    TIMESTAMP;                      
return_date_after TIMESTAMP;                      
next_run_date   TIMESTAMP;                      
BEGIN                                 
start_date := to_timestamp_tz('29-NOV-11 09.00.00','DD-MON-YYYY HH24:MIS');  
return_date_after := start_date;                    
FOR i IN 1..5 LOOP                           
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=WEEKLY;INTERVAL=2;BYDAY=SAT;BYHOUR=11;BYMINUTE=0',start_date, return_date_after, next_run_date);           
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);       
return_date_after := next_run_date;                  
END LOOP;                               
END;
/


You can change frequency as you like to Test.

No comments:

Post a Comment