Optimizing Oracle Database Performance with GATHER_SYSTEM_STATS

Optimizing Oracle Database Performance with GATHER_SYSTEM_STATS

Oracle Database's DBMS_STATS package is a powerful tool for gathering and managing database statistics. This post focuses on the GATHER_SYSTEM_STATS procedure, which is crucial for optimizing query performance by collecting system-level statistics.

Understanding GATHER_SYSTEM_STATS

The GATHER_SYSTEM_STATS procedure collects system statistics that reflect the performance characteristics of your hardware. These statistics help the Oracle optimizer make more accurate cost estimates for query execution plans.Let's break down the code:

Sample SQL Command

1execute dbms_stats.gather_system_stats('Start');
2-- Wait for a while - ideally with the database under a typical workload
3execute dbms_stats.gather_system_stats('Stop');

This code initiates a statistics gathering session, allows the database to run under typical workload, and then stops the gathering process.

Key Points and Insights

  1. Workload Window: The period between 'Start' and 'Stop' should represent your typical database workload for accurate statistics.
  2. Timing: Ideally, run this during peak usage times to capture representative system performance.
  3. Frequency: System statistics don't change often unless there are significant hardware or workload changes. Gathering them once every few months or after major system changes is usually sufficient.
  4. Impact: Gathering system statistics has minimal impact on database performance.
  5. Automatic Alternative: Oracle can also gather system statistics automatically during maintenance windows.

Viewing System Statistics

After gathering, you can view the collected statistics using:

1select pname, pval1 from sys.aux_stats$ where sname = 'SYSSTATS_MAIN';

This query displays the system statistics stored in the SYS.AUX_STATS$ table.

Important Statistics

Some key statistics you'll see include:

  • CPUSPEEDNW: CPU speed (in millions of cycles per second)
  • IOSEEKTIM: I/O seek time (in milliseconds)
  • IOTFRSPEED: I/O transfer speed (in bytes per millisecond)
  • SREADTIM: Single block read time (in milliseconds)
  • MREADTIM: Multiblock read time (in milliseconds)

Best Practices

  1. Gather statistics during representative workload periods.
  2. Re-gather after significant hardware changes or workload shifts.
  3. Monitor query performance before and after gathering new statistics.
  4. Consider using the NOWORKLOAD option if you can't run during peak times.

By properly utilizing GATHER_SYSTEM_STATS, database administrators can ensure that the Oracle optimizer has accurate system-level information, leading to better query execution plans and improved overall database performance.

Posts in this series