Better stats job

Oracle script for the Oracle DBA. A better stats job in 10g than the native job.

-- ################################################
-- # Creator: Cyrille MODIANO (DBA Oracle in Montreal)
-- # Created: 2013/02/06
-- # Name: create better gather stats job
-- ################################################
-- #
-- # Compatible: 10g
-- #
-- ################################################

--disable the automatic statistics collection default job :

--10g

exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');

--11g 

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection', 
    operation => NULL, 
    window_name => NULL);
END;
/
 
-- Create a new job to calculate statistics with histogram :
 
BEGIN
  dbms_scheduler.create_job(
                job_name          =>'GATHER_DATABASE_STATS',
                job_type             =>'PLSQL_BLOCK',
                job_action          =>'BEGIN DBMS_STATS.GATHER_DATABASE_STATS(cascade=>TRUE,degree=>4,estimate_percent=>8,method_opt=>''FOR ALL INDEXED COLUMNS SIZE 251'');end;',
                repeat_interval               =>'FREQ=WEEKLY;BYHOUR=22;BYMINUTE=30',
                enabled                              =>TRUE,
                comments          =>'job to collect statistics with histogram');
END;
/
 
-- Verify what statistics exists for your database using this query :
 
select COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
from user_tab_col_statistics
where table_name='your_table';

To manually calculate stats for a specific table or index:

For a TABLE:

begin
dbms_stats.gather_table_stats( 
ownname=> 'SCOTT', 
tabname=> 'ORDER' , 
estimate_percent=> null, 
cascade=> FALSE,              -- TRUE to calculate stats for indexes
degree=> DBMS_STATS.AUTO_DEGREE, 
no_invalidate=> FALSE, 
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
END; 

For an INDEX:

BEGIN
dbms_stats.gather_index_stats( 
ownname=> 'SCOTT', 
indname=> 'ORDER_INDX1' , 
estimate_percent=> null, 
degree=> null, 
no_invalidate=> FALSE, 
granularity=> 'AUTO');
END;