Wednesday, October 10, 2012

STATISTICS ( STATS) collection job in oracle

10G:
job name - GATHER_STATS_JOB
 SELECT JOB_NAME, SCHEDULE_NAME, SCHEDULE_TYPE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';


interface - scheduler



11G:
job is changed to Auto TASK  using DBMS_AUTO_TASK_ADMIN

dba_auto_task_client

select client_name, status from dba_autotask_client;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED



"auto optimizer stats collection" is the stats collection task.

 interface - dbms_auto_task_admin

DBMS_AUTO_TASK_ADMIN.DISABLE;
DBMS_AUTO_TASK_ADMIN.DISABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2);
DBMS_AUTO_TASK_ADMIN.ENABLE;
DBMS_AUTO_TASK_ADMIN.ENABLE (
   client_name       IN    VARCHAR2,
   operation         IN    VARCHAR2,
   window_name       IN    VARCHAR2); 


reference:  
http://askdba.org/weblog/2009/08/gather_stats_job-is-it-enabled/
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1133388300346992024
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_autotaskadm.htm
http://psoug.org/reference/dbms_auto_task_admin.html

No comments:

Post a Comment