Home

Scheduling a Job to Gather Statistics in Oracle Database Weekly for Stale Statistics

Scheduling a Job to Gather Statistics in Oracle Database Weekly for Stale Statistics
Gathering statistics in Oracle Database is crucial for ensuring the optimizer has up-to-date information to generate efficient execution plans. Stale or outdated statistics can lead to suboptimal query performance. Oracle provides the DBMS_SCHEDULER and DBMS_STATS packages to automate this task effectively.

In this article, we’ll cover how to schedule a job that gathers statistics for tables with stale statistics on a weekly basis.

Why Gather Statistics?
Oracle’s Cost-Based Optimizer (CBO) uses table and index statistics to determine the most efficient execution plans for SQL queries. Regularly gathering statistics is essential to:

Maintain query performance.
Avoid suboptimal execution plans due to outdated stats.
Reduce troubleshooting time caused by performance issues.
Step-by-Step Guide

1. Verify Current Statistics Settings
   Before creating a new job, confirm your database’s current automatic statistics gathering settings:

SELECT dbms_stats.get_prefs('AUTOSTATS_TARGET') AS autostats_target
FROM dual;

If automatic statistics gathering is already enabled, Oracle runs the GATHER_STATS_JOB during maintenance windows.

2. Identify Stale Statistics
   Oracle tracks stale statistics. You can find tables with stale stats using the following query:

SELECT table_name, stale_stats
FROM dba_tab_statistics
WHERE owner = 'SCHEMA_NAME' AND stale_stats = 'YES';

3. Create a Custom Job to Gather Stats for Stale Objects
   To gather statistics on a weekly schedule, you can use DBMS_SCHEDULER and DBMS_STATS.

Create a PL/SQL Procedure
First, create a procedure to gather stats only for stale objects:

CREATE OR REPLACE PROCEDURE gather_stale_stats AS
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(
OPTIONS => 'GATHER STALE',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
CASCADE => TRUE
);
END;
/
OPTIONS => 'GATHER STALE': Only gathers stats for objects marked as stale.
AUTO_SAMPLE_SIZE: Automatically determines an optimal sample size.
CASCADE => TRUE: Ensures index stats are also gathered.

Schedule the Job
Next, use DBMS_SCHEDULER to schedule the procedure to run weekly.

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'GATHER_STATS_WEEKLY',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN gather_stale_stats; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
enabled => TRUE,
comments => 'Weekly job to gather statistics for stale objects.'
);
END;
/
start_date: Specifies when the job begins (e.g., immediately).
repeat_interval: Uses a calendaring syntax for weekly execution. Here, the job runs every Sunday at 2:00 AM.

4. Monitor the Job
   You can monitor the status of your scheduled job using the DBA_SCHEDULER_JOBS and DBA_SCHEDULER_JOB_RUN_DETAILS views.

Check Job Status:

SELECT job_name, enabled, state
FROM dba_scheduler_jobs
WHERE job_name = 'GATHER_STATS_WEEKLY';
Check Job Run History:

SELECT log_id, job_name, status, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_STATS_WEEKLY'
ORDER BY log_date DESC;

5. Troubleshooting
   If the job does not execute as expected:

Verify the DBMS_SCHEDULER job is enabled.
Check for any errors in the DBA_SCHEDULER_JOB_LOG view:

SELECT job_name, status, error# AS error_code, additional_info
FROM dba_scheduler_job_log
WHERE job_name = 'GATHER_STATS_WEEKLY';

Ensure that the DBMS_STATS privileges are granted to the user running the job.

Conclusion:

Scheduling a job to gather stale statistics weekly ensures that your Oracle database performs optimally without manual intervention. By leveraging DBMS_SCHEDULER and DBMS_STATS, you can automate this critical maintenance task efficiently. Regularly monitoring the job’s execution ensures continued reliability.