default gather stats job in Oracle

 

The default gather stats job in Oracle is an automatic background process that collects and updates statistics for database objects (tables, indexes, etc.). These statistics are crucial for the query optimizer to generate efficient execution plans.

---

Key Details:

1. Job Name: GATHER_STATS_JOB

   • This job is part of Oracle's Automatic Maintenance Tasks framework.

2. Purpose:

   • It collects object statistics (e.g., number of rows, distinct values, data distribution).
   • The query optimizer uses these statistics to decide the best way to execute SQL statements.

3. When It Runs:

   • By default, the job runs during the maintenance window, typically:
     • Nighttime hours: 10 PM to 2 AM (local time), when database activity is low.
     • Exact timing depends on the maintenance window schedule.

4. What It Does:

   • Gathers statistics for objects that are:
     • Stale (data significantly changed since the last statistics collection).
     • Missing (no statistics exist).
   • The job determines staleness based on the STALE_PERCENT threshold (default: 10% changes to rows).

5. How It Runs:

• The job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure.
• It adapts the sample size (AUTO_SAMPLE_SIZE) for better performance and accuracy.

---

Viewing and Managing the Default Stats Job:

Check the Status of the Job:

SELECT JOB_NAME, ENABLED FROM DBA_AUTOTASK_CLIENT 
WHERE CLIENT_NAME = 'Auto Optimizer Stats Collection';

• If ENABLED = 'YES', the job is active.

Enable/Disable the Job:

Enable:

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

Disable:

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

Check the Maintenance Window:

SELECT WINDOW_NAME, START_TIME, DURATION FROM DBA_SCHEDULER_WINDOWS;

---

Oracle's Auto Optimizer Stats Collection task, executed by the GATHER_STATS_JOB, operates during predefined maintenance windows. 

These windows run on a schedule from Monday to Sunday and typically occur during off-peak hours when database activity is minimal.

Behavior During Maintenance Windows:

1. Start Time:

   • On weekdays (Monday to Friday), the job starts at 10:00 PM.
   • On weekends (Saturday and Sunday), the job starts at 6:00 AM and has a much longer duration (up to 20 hours).

2. Job Actions:

   • Identifies stale or missing statistics in database objects.
   • Collects fresh statistics using the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure.

3. Priority on Stale Objects:

   • Objects where 10% or more rows have changed since the last stats collection are prioritized for update.

4. Impact on Database:

• The task is designed to run with low priority to minimize interference with user workloads.
• During the extended weekend windows, it may process more objects or larger tables.

---

Viewing Window Details in Oracle:

Check All Maintenance Windows:

SELECT WINDOW_NAME, ENABLED, START_TIME, DURATION FROM DBA_SCHEDULER_WINDOWS ORDER BY START_TIME;

Example Output:

WINDOW_NAME       ENABLED  START_TIME            DURATION
----------------- -------- --------------------- ---------
MONDAY_WINDOW     TRUE     13-NOV-24 10:00 PM   +000 04:00
TUESDAY_WINDOW    TRUE     14-NOV-24 10:00 PM   +000 04:00
WEDNESDAY_WINDOW  TRUE     15-NOV-24 10:00 PM   +000 04:00
THURSDAY_WINDOW   TRUE     16-NOV-24 10:00 PM   +000 04:00
FRIDAY_WINDOW     TRUE     17-NOV-24 10:00 PM   +000 04:00
SATURDAY_WINDOW   TRUE     18-NOV-24 06:00 AM   +000 20:00
SUNDAY_WINDOW     TRUE     19-NOV-24 06:00 AM   +000 20:00

Check Specific Window for Auto Optimizer Stats Collection:

SELECT CLIENT_NAME, STATUS, WINDOW_NAME FROM DBA_AUTOTASK_CLIENT;

---

Managing Maintenance Windows:

Modify a Window's Duration:

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'MONDAY_WINDOW',
    attribute => 'DURATION',
    value     => '6:00:00' -- Example: Extend duration to 6 hours
  );
END;
/

Enable/Disable a Window:

EXEC DBMS_SCHEDULER.ENABLE('MONDAY_WINDOW'); -- Enable
EXEC DBMS_SCHEDULER.DISABLE('MONDAY_WINDOW'); -- Disable





Summary:
Monday to Friday: Maintenance runs for 4 hours starting at 10 PM.
Saturday and Sunday: Maintenance runs for up to 20 hours starting at 6 AM.
These schedules ensure statistics are up-to-date with minimal disruption, particularly during weekends when longer maintenance is possible.

---

Example: Default Statistics Job in Action

1. A table named EMP has data changes:

   • Rows are added, deleted, or updated.
   • Statistics become stale.

2. During the next maintenance window:

• The GATHER_STATS_JOB identifies EMP as stale.
• It runs DBMS_STATS to collect up-to-date stats.

---

Benefits of the Default Stats Job:

1. Automates Maintenance:
   • No need to manually gather statistics.
2. Improves Query Performance:
   • Accurate stats allow the optimizer to make better decisions.
3. Reduces DBA Workload:
• Automatically handles large, complex environments.

---

Customizing the Behavior:

If the default behavior doesn’t suit your environment:

• You can gather statistics manually using DBMS_STATS with custom parameters.
• Example:

  
  EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMP');
  

In most cases, the default gather stats job works well for typical workloads, ensuring your database stays optimized without requiring manual intervention.

---

Understand Gather Stats job and default behavior in Oracle :-

Gather Stats job is the default job running in Oracle Database for collecting the most used tables stats automatically. 

Oracle monitor the most DML tables with help of DBA_TAB_MODIFICATIONS view which stores information about the inserts, deletes, and updates to a table. Gather Stats analyzed stats for those tables having more than 10% rows done DML Operations.

Check the Job Status


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




Check the job timing with following commands

select operation,target,start_time,end_time from dba_optstat_operations where operation='gather_database_stats(auto)';


Enable and disable the gather stats job

-- Disable
exec dbms_auto_task_admin.disable(client_name=> 'auto optimizer stats collection');

-- Enable
exec dbms_auto_task_admin.enable(client_name=>'auto optimizer stats collection');


Two ways to change the default preferences for Statistics Gather Job

1. Manually used the Parameter setting while executing the DBMS_STATS.GATHER_*_STATS procedure for gather stats.

Example

exec dbms_stats.gather_table_stats(
ownname=>'SCOTT',
tabname=>'EMP',
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade=> DBMS_STATS.AUTO_CASCADE,
degree=> null,
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,
granularity=> 'AUTO',
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');


2. Oracle defines default values for preferences, you can change it with procedure DBMS_STATS.SET_*_PREFS.
Following are the procedures to change preferences at different level:

DBMS_STATS.SET_*_PREFS Procedure is used to change the default values for of parameters for control statistics collection.
SET_TABLE_PREFS: Lets you specify default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for a specific table
SET_SCHEMA_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for all objects in a specific schema
SET_DATABASE_PREFS: Lets you change the default parameters to be used by the DBMS_STATS.GATHER_*_STATS procedures for the entire database, including all user schema’s and system schema’s such as SYS and SYSTEM
SET_GLOBAL_PREFS: Sets global statistics preferences; this procedure lets you change the default statistic collection parameters for any object in the database that doesn’t have an existing preference at the table level. If you don’t set table level preferences or you don’t set any parameter explicitly in the DBMS_STATS.GATHER_*_STATS procedure, the parameters default to their global settings.

Example for Check and Change the Default preferences:

--at the database level by using the SET_DATABASE_PREFS procedure:
SQL> execute dbms_stats.set_database_prefs('ESTIMATE_PERCENT','20');

–check default value set in database with get_prefs procedure:
select dbms_stats.get_prefs (‘STALE_PERCENT’,’SH’) stale_percent from dual;

Following are the list of preferences used by gather stats:

CASCADE:
Used for database may or may not collect index statistics along with the table statistics. By default,(cascade=true) it collect stats for all of a tables plus indexes.

DEGREE:
Means to degree of parallelism the database use for gather the statistics.The default setting is DBMS_STATS.AUTO_DEGREE.

ESTIMATE_PERCENT: specifies the percentage of rows the database must use to estimate for the Statistics. By Default DBMS_STATS.AUTO_SAMPLE_SIZE. It’s value from 0% to 100%. 100% means all rows scanned for statistics.

METHOD_OPT: Two values you can used
1. Columns for which the database will collect statistics.
2. Columns on which the database will create histograms.

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
Means Database must collect statistics for all columns or only for the indexed columns. If you used the FOR ALL INDEXED COLUMNS option, the database will collect statistics only for those columns that have an index on them.

FOR COLUMNS [size_clause] column [size_clause] [,COLUMN [size_clause]…]
Means one or more columns on which the database must gather statistics.
column stands for column_name or extension name or expression used for gather the stats on the specific column or expression for tables.

size_clause used in both cases which tell database to collect histograms for a column.
One option is to supply an integer value indicating the number of histogram buckets in range 1 through 254.
Value 254 : Create the histograms on all the column mentioned in for or all options above.
Value 1 : then it will not create histograms on the column because of single bucket. Setting value 1 remove the histogram if already present
REPEAT : Histogram is collected on those columns which already have histograms.
AUTO : Database determine for the columns for collect histograms based on column usage plus data distribution.
SKEWONLY : Database determine for which columns collect histograms based on column data distribution.
Default value for the METHOD_OPT parameter is FOR ALL COLUMNS SIZE AUTO.

For Example:

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEES',method_opt=> 'for columns size 254 job_id')
PL/SQL procedure successfully completed.
SQL>

NO_INVALIDATE:
Default value for the NO_INVALIDATE parameter.TRUE means database doesn’t invalidate the dependent cursors of the table.FALSE means database immediately invalidates the dependent cursors. DBMS_STATS.AUTO_INVALIDATE is auto.

GRANULARITY
Basically used for statistics gathering for partitioned tables. 

Having following options to set:ALL: Gathers subpartition-, partition-, and global-level statistics; this setting provides a very accurate set of table statistics but consuming lot of resources and time.
GLOBAL: Gathers global statistics for a table.
PARTITION: Gathers only partition statistics.
GLOBAL AND PARTITION: Gathers the global and partition statistics, but not the subpartition level statistics.
SUBPARTITION: Gathers only subpartition statistics.
AUTO: Default value for the GRANULARITY parameter and determines the granularity based on the partitioning type.