Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In some cases, automatic maintenance jobs are closed in databases. In this case, the schema statistics are not collected and cause serious performance problems.
It is possible to collect individual statistics of the schemas via a job. You can use the following job for this job.
Necessary job to gather schema statistics:
You can replace the USER1, USER2, USER3 schema names in the Job with schema names in your own database, or adding another schema names to the corresponding block if more schemes are required.
The job will start every Saturday at 03:30. It would be useful to create it under the SYS schema. By increasing the degree parameter you can finish it in less time.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,start_date => TO_TIMESTAMP_TZ('2016/11/12 00:00:00.000000 Europe/Istanbul','yyyy/mm/dd hh24:mi:ss.ff tzr') ,repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=3;BYMINUTE=30' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'begin dbms_stats.gather_schema_stats( ownname=> ''"USER1"'' , cascade=> DBMS_STATS.AUTO_CASCADE, estimate_percent=> null, degree=> 1, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> ''AUTO'', method_opt=> ''FOR ALL COLUMNS SIZE AUTO'', options=> ''GATHER''); dbms_stats.gather_schema_stats( ownname=> ''"USER2"'' , cascade=> DBMS_STATS.AUTO_CASCADE, estimate_percent=> null, degree=> 1, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> ''AUTO'', method_opt=> ''FOR ALL COLUMNS SIZE AUTO'', options=> ''GATHER''); dbms_stats.gather_schema_stats( ownname=> ''"USER3"'' , cascade=> DBMS_STATS.AUTO_CASCADE, estimate_percent=> null, degree=> 1, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> ''AUTO'', method_opt=> ''FOR ALL COLUMNS SIZE AUTO'', options=> ''GATHER''); end;' ,comments => NULL ); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'RESTARTABLE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'LOGGING_LEVEL' ,value => SYS.DBMS_SCHEDULER.LOGGING_RUNS); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'MAX_FAILURES'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'MAX_RUNS'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'STOP_ON_WINDOW_CLOSE' ,value => FALSE); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'JOB_PRIORITY' ,value => 3); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'SCHEDULE_LIMIT'); SYS.DBMS_SCHEDULER.SET_ATTRIBUTE ( name => 'SYS.GATHER_SCHEMA_STATS_JOB' ,attribute => 'AUTO_DROP' ,value => FALSE); SYS.DBMS_SCHEDULER.ENABLE (name => 'SYS.GATHER_SCHEMA_STATS_JOB'); END; / |
