Saturday, October 19, 2013

How to use the dbms_scheduler package to collect intermittent statistics on volatile tables

In the following example I am using the dbms_scheduler package to analyze certain volatile tables every hour:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'GATHERSTATS'
      ,start_date      => TO_TIMESTAMP_TZ('2009/05/13 12:17:57.508450 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=HOURLY;'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => '
                            begin
                            dbms_stats.gather_table_stats( 
                            ownname=> ''TBR'', 
                            tabname=> ''LOGONS'' , 
                            estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,  
                            cascade=> DBMS_STATS.AUTO_CASCADE, 
                            degree=> null,  
                            no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
                            granularity=> ''AUTO'', 
                            method_opt=> ''FOR ALL COLUMNS SIZE AUTO'');
                            comments=>'your comment here'
END;
/

No comments:

Post a Comment