oracle

Few days back , Martin had posted a series of post on a issue where GATHER_STATS_JOB was failing silently for a large object. If you have missed it, you can check following links

Automated statistics gathering silently fails

Automated Statistics Gathering Silently Fails #2

Automatic Statistics Gathering Fails #3

This post is based on some of the discussions on the thread. Please note that this is not intended to discuss bugs (if any) associated with the job

Gather_stats_job was introduced with Oracle 10g to gather statistics for database objects which has stale statistics (10% of data has changed, you can query dba_tab_modifications) or the tables for which the statistics has not been gathered (new tables created/truncated). This job runs during the maintenance window i.e every night from 10 P.M. to 6 A.M. and all day on weekends. This schedule can be though changed and process is documented in Metalink aka My Oracle Support Note 579007.1 –

This feature brought relief to lot of DBA’s as they did not have to write shell scripts to gather stats and could rely on this job to do the work. But slowly people realized that it does not fit in their environment and slowly recommendation turned from “Enabled ” to “Disabled “

Issues/Misconceptions

1) Should I schedule job run for every night?

As documented this job gathers stats on the tables which have got 10% of data changes since last run. So this will not touch the tables for which the data changes are less then 10%. Also due to rolling invalidation feature , sql cursors will not be immediately invalidated (Refer to oracle forums discussion and Fairlie Rego’s post)

If you are still not happy , you can change the maintenance window timings to suit the schedule (say on weekends)

In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all maintenance windows. New maintenance windows introduced with 11g are
You can check the name and state of this job using following query

SQL> SELECT client_name, status FROM dba_autotask_operation;

CLIENT_NAME							 STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection 				 ENABLED
auto space advisor						 ENABLED
sql tuning advisor						 ENABLED

Window – Description

MONDAY_WINDOW – Starts at 10 p.m. on Monday and ends at 2 a.m.
TUESDAY_WINDOW – Starts at 10 p.m. on Tuesday and ends at 2 a.m.
WEDNESDAY_WINDOW -Starts at 10 p.m. on Wednesday and ends at 2 a.m.
THURSDAY_WINDOW – Starts at 10 p.m. on Thursday and ends at 2 a.m.
FRIDAY_WINDOW -Starts at 10 p.m. on Friday and ends at 2 a.m.
SATURDAY_WINDOW – Starts at 6 a.m. on Saturday and is 20 hours long.
SUNDAY_WINDOW -Starts at 6 a.m. on Sunday and is 20 hours long.

To enable the job (in case it is disabled)

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

You can check history/run duration of these jobs by using following query

select client_name,JOB_NAME,JOB_STATUS,JOB_START_TIME,JOB_DURATION from DBA_AUTOTASK_JOB_HISTORY where JOB_START_TIME >systimestamp -7 and client_name='sql tuning advisor'

CLIENT_NAME		       JOB_NAME 		      JOB_STATUS      JOB_START_TIME					 JOB_DURATION
------------------------------ ------------------------------ --------------- -------------------------------------------------- --------------------
sql tuning advisor	       ORA$AT_SQ_SQL_SW_521	      SUCCEEDED       30-MAR-12 04.00.01.698038 PM AMERICA/LOS_ANGELES	 +000 00:22:13
sql tuning advisor	       ORA$AT_SQ_SQL_SW_493	      SUCCEEDED       01-APR-12 04.00.02.701398 PM AMERICA/LOS_ANGELES	 +000 00:42:26
sql tuning advisor	       ORA$AT_SQ_SQL_SW_522	      SUCCEEDED       31-MAR-12 04.00.07.642613 PM AMERICA/LOS_ANGELES	 +000 00:00:37

2) I do not want gather_stats_job to gather stats on some of my tables.

This requirement can arise due to following points

a)There are tables for which you have set the stats manually
b) There are queries for which you know your old stats will work fine
c) Tables are big and gather_stats_job is silently failing ( Again refer to Martin’s Post)
d) Tables for which histograms cannot not be gathered or vice versa
e) Tables for which you would like to estimate fixed percent of blocks

For all these situations. you can use DBMS_STATS.LOCK_TABLE_STATS and gather stats manually with force =>true to override locked statistics. For big partitioned tables you can use COPY_TABLE_STATS and APPROX_GLOBAL AND PARTITION feature. 

Oracle 11g also has enhancement to gathering stats on partitioned tables where in you can gather INCREMENTAL stats for partitions and oracle will automatically update global stats for table.This approach has advantage as we don’t scan table twice and reduces the time to gather stats drastically. You need to use DBMS_STATS.set_table_prefs procedure to set Incremental stats gathering to true. 

EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT','EMP_PART','INCREMENTAL','TRUE');

Note that first time, stats gathering will take more time as oracle will create object called synposes for each paritition. Subsequent runs of gather_stats_job will be faster. Refer to Optimizer group post for more info on copy_table_stats and 11g incremental stats feature. ( Due to bug copy_table_stats does not alter low/high value. Details can be found here )

To fix histogram issue, DBMS_STATS.SET_PARAM can be used to modify the default attributes e.g By default, GATHER_STATS_JOB will gather histograms, which can be confirmed by running below query

select dbms_stats.get_param('method_opt') method_opt from dual;

METHOD_OPT
--------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

To disable histogram capture, use

exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE 1');

In case you wish to capture histograms for some of the tables, then you can use

exec dbms_stats.set_param('method_opt', 'FOR ALL COLUMNS SIZE REPEAT')

i.e Collects histograms only on the columns that already have histograms

Starting Oracle 11g database you can use DBMS_STATS.SET_*_PREFS to take care of point (d) and (e) i.e you can change the default gather options for particular table. Details can be found here

This is not exhaustive list and I hope that this will grow so that we can have Recommendation for GATHER_STATS_JOB status to be set to  “SCHEDULED” 🙂