SCHEDULE JOB FOR GATHER STATISTIC

create a Sched. job for specific schemas that you want to GATHER STATISTIC
oraclesqlperformance-tuningv1.0.0
0 stars3 downloads41 views0 comments
By Mahmoud • Created 3/11/2026
Code

(78 lines)
BEGIN
  SYS.DBMS_SCHEDULER.DROP_JOB
    (job_name  => '<schemaname>.SCHEMA_GATHER_STATSTIC');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => '<schemaname>.SCHEMA_GATHER_STATSTIC'
      ,start_date      => TO_TIMESTAMP_TZ('2026/03/11 03:00:00.000000 +02:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=WEEKLY;INTERVAL=1' -- or 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN
--    DBMS_STATS.gather_schema_stats (''<schemaname>'',
--                                    estimate_percent   => 20,
--                                    cascade            => TRUE);
--    DBMS_STATS.gather_schema_stats (''<schemaname>'',
--                                    estimate_percent   => 20,
--                                    cascade            => TRUE);
--    DBMS_STATS.gather_schema_stats (''<schemaname>'',
--                                    estimate_percent   => 20,
--                                    cascade            => TRUE);
--    DBMS_STATS.gather_schema_stats (''<schemaname>'',
--                                    estimate_percent   => 20,
--                                    cascade            => TRUE);
DBMS_STATS.gather_database_stats(estimate_percent=> 100 ,cascade=> TRUE);
END;'
      ,comments        => NULL
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'MAX_RUNS');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'STOP_ON_WINDOW_CLOSE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'RESTART_ON_RECOVERY'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'RESTART_ON_FAILURE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => '<schemaname>.SCHEMA_GATHER_STATSTIC'
     ,attribute => 'STORE_OUTPUT'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => '<schemaname>.SCHEMA_GATHER_STATSTIC');
END;
/
SCHEDULE JOB FOR GATHER STATISTIC

Code

General Comments(0)
