Friday, October 1, 2010

Stat Enable/Disable

To Disable/Enable Job
=======================

DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB_XXX');

DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB_XXX');


To Drop Job
=========================
DBMS_SCHEDULER.DROP_JOB('GATHER_STATS_JOB_XXX');


To run Stat Job Manually
=============================
exec dbms_stats.gather_database_stats_job_proc


10g
=======
SELECT job_name, state
FROM dba_scheduler_jobs
WHERE job_name='GATHER_STATS_JOB'; 

select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB';

select  PROGRAM_ACTION 
  from dba_scheduler_programs 
  where PROGRAM_NAME = 'GATHER_STATS_PROG';

select * 
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'; 
 
select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW') ;

11g
====
select client_name,status from Dba_Autotask_Client; 

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



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

Run Stat Manually

To run Stat Job Manually(Exactly same as AUTO stat)
=============================
exec dbms_stats.gather_database_stats_job_proc

Stat Collection Options

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;


select dbms_stats.get_stats_history_availability from dual;


col value for a30
select 'CASCADE' setting,dbms_stats.get_param('CASCADE') value from dual
union
select 'DEGREE',dbms_stats.get_param('DEGREE') from dual
union
select 'ESTIMATE_PERCENT',dbms_stats.get_param('ESTIMATE_PERCENT') from dual
union
select 'METHOD_OPT',dbms_stats.get_param('METHOD_OPT') from dual
union
select 'NO_INVALIDATE',dbms_stats.get_param('NO_INVALIDATE') from dual
union
select 'GRANULARITY',dbms_stats.get_param('GRANULARITY') from dual
union
select 'AUTOSTATS_TARGET',dbms_stats.get_param('AUTOSTATS_TARGET') from dual;