Enable or Disable Gather_stats_job

Convertir en PDF Version imprimable Suggérer par mail
Écrit par Sidibe   

How to disable automatic statistics gathering?

 

 

Oracle 10g:

 

Use this procedure to disable the job GATHER_STATS_JOB :

Exec dbms_scheduler.disable(’GATHER_STATS_JOB’);

And you can enable the job using the dbms_scheduler.enable procedure as below :

Exec dbms_scheduler.enable(’GATHER_STATS_JOB’);

 

 

 

 

Oracle 11g

 

Enabling Automatic Optimizer Statistics Collection

 

Optimizer statistics are automatically gathered by automatic optimizer statistics collection, which gathers statistics on all objects in the database which have stale or missing statistics.

Automatic optimizer statistics collection runs as part of the automated maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined maintenance windows.

If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

 

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

END;

/

 

In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

 

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL);

END;

/

 

Automatic optimizer statistics collection relies on the modification monitoring feature, described in "Determining Stale Statistics". If this feature is disabled, then the automatic optimizer statistics collection job will not be able to detect stale statistics. This feature is enabled when the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. TYPICAL is the default value.

 

 

 

 
< Précédent   Suivant >