Automating Statistics Collection for the test Table in Oracle Database

31 May 2025 0 Comment

Accurate statistics are essential for Oracle database performance, enabling the query optimizer to create efficient execution plans. For a table like test, which may experience frequent data changes, automating statistics collection ensures consistent query performance without manual effort. This article provides a corrected PL/SQL script to create an Oracle Scheduler job for gathering statistics on the test table, along with practical guidance for implementation and maintenance.

Importance of Automating Statistics for the test Table

Statistics describe the data distribution in tables and indexes, directly influencing Oracle’s query optimizer. For the test table, frequent updates or inserts can make statistics outdated, leading to inefficient query plans and slower performance. Automating statistics collection offers:

Consistent Performance: Keeps the optimizer informed about data changes.
Reduced Manual Work: Eliminates the need for DBAs to manually run statistics jobs.
Reliability: Ensures regular updates align with the table’s data volatility.

Using Oracle Scheduler with DBMS_STATS, DBAs can streamline this process for the test table.

PL/SQL Script: Automating Statistics for the test Table

The following PL/SQL script creates a scheduler job to collect statistics for the test table weekly, optimized for performance and reliability.

Corrected PL/SQL Script

sql

BEGIN
  -- Create the scheduler job
  SYS.DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'BUGRA.JOB_GATHER_TEST_STATS',
    start_date      => TO_TIMESTAMP_TZ('2025/06/06 23:00:00.000000 Europe/Istanbul', 'YYYY/MM/DD HH24:MI:SS.FF TZR'),
    repeat_interval => 'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
    end_date        => NULL,
    job_class       => 'DEFAULT_JOB_CLASS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => ''BUGRA'', tabname => ''test'', degree => 8, granularity => ''AUTO'', cascade => TRUE); END;',
    comments        => 'Weekly statistics collection for the test table'
  );

  -- Set job attributes
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'RESTARTABLE',
    value     => TRUE
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'LOGGING_LEVEL',
    value     => DBMS_SCHEDULER.LOGGING_FULL
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'MAX_FAILURES'
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'MAX_RUNS'
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'STOP_ON_WINDOW_CLOSE',
    value     => FALSE
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'JOB_PRIORITY',
    value     => 3
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'SCHEDULE_LIMIT'
  );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'BUGRA.JOB_GATHER_TEST_STATS',
    attribute => 'AUTO_DROP',
    value     => FALSE
  );

  -- Enable the job
  SYS.DBMS_SCHEDULER.ENABLE(
    name => 'BUGRA.JOB_GATHER_TEST_STATS'
  );
END;
/

Key Corrections and Improvements

Table-Specific Focus:
- Changed DBMS_STATS.GATHER_DATABASE_STATS to DBMS_STATS.GATHER_TABLE_STATS to target only the test table, reducing resource usage.
- Specified ownname => ‘BUGRA’ and tabname => ‘test’ for precise statistics collection.
Job Configuration:
- Updated job_name to BUGRA.JOB_GATHER_TEST_STATS for clarity.
- Set start_date to June 6, 2025, 23:00 (Europe/Istanbul), aligned with the current date (May 31, 2025).
- Standardized repeat_interval with BYSECOND=0 for precision.
Logging:
- Changed LOGGING_LEVEL to DBMS_SCHEDULER.LOGGING_FULL for detailed job execution logs, aiding troubleshooting.
Comments:
- Updated comments to “Weekly statistics collection for the test table” for clarity.
Syntax:
- Added consistent formatting and a trailing / for execution in SQL*Plus or similar tools.

How to Use the Script

Purpose: Schedules weekly statistics collection for the test table every Friday at 23:00, using a parallelism degree of 8 for efficiency.
Key Settings: The job includes indexes (cascade => TRUE) and uses granularity => ‘AUTO’ to let Oracle determine the appropriate statistics level (e.g., table or partition).
Monitoring: Check job status in DBA_SCHEDULER_JOB_LOG to confirm successful execution.
Use Case: Ideal for a test table with frequent data changes, ensuring the optimizer uses current statistics for query planning.

Best Practices for Statistics Automation

Target High-Activity Tables: Focus on tables like test with frequent updates to avoid database-wide statistics overhead.
Run During Off-Peak Hours: Schedule jobs at low-traffic times (e.g., 23:00) to minimize performance impact.
Enable Logging: Use LOGGING_FULL to track job execution details for debugging.
Optimize Parallelism: Adjust the degree parameter (e.g., degree => 4 for smaller tables) based on system resources.
Review Job Performance: Monitor execution times in DBA_SCHEDULER_JOB_RUN_DETAILS and adjust granularity or frequency as needed.

Conclusion

Automating statistics collection for the test table using Oracle Scheduler and DBMS_STATS ensures optimal query performance with minimal manual effort. The provided PL/SQL script offers a reliable solution for weekly statistics updates, tailored to the test table’s needs. By implementing this automation and following best practices, DBAs can maintain efficient query execution and reduce performance bottlenecks.

Additional Notes

Permissions: Requires SYS privileges or the SCHEDULE_JOB role. For non-privileged users, replace SYS.DBMS_SCHEDULER with DBMS_SCHEDULER and adjust the schema.
Performance: For large partitioned tables, consider setting granularity => ‘PARTITION’ to reduce collection time.
Maintenance: Regularly check DBA_SCHEDULER_JOBS to ensure the job is enabled and running as expected.