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;