Automatic Tuning And Maintenance

Automatic maintenance and tuning has always been one of the key product differentiators for Microsoft SQL Server. With Oracle 10g, features that meet and match those found in competitor products are being introduced to the server technology stack.

Surveys show that over 50% of a DBAs time is spend tuning and monitoring the database server, a task that whilst important is often complex and difficult to get exactly right. With Oracle Database 10g, Oracle have introduced a number of components that together make it possible for the database server to monitor itself, make intelligent changes to configuration, and alert DBAs when situations arise that need manual intervention.

The first component in this framework is the Automatic Workload Repository, which uses an enhanced version of STATSPACK to collect instance statistics every thirty minutes and stores these for a rolling seven day period. This enhanced version of STATSPACK now collects a broader range of statistics and has a number of optimizations to streamline the way high-cost SQL statements are captured, ensuring that only SQL activity that has significantly affected performance since the last snapshot are collected. The usage information stored in the Automatic Workload Repository is then used as the basis for all the self-management functionality in Oracle Database 10g.

Next up is the Automatic Maintenance Tasks feature, which acts on the statistics gathered by the Automatic Workload Repository, and carries out tasks such as index rebuilding, refreshing statistics, and so on, where such tasks don't require any manual intervention by the DBA. A new scheduling feature known as 'Unified Scheduler' runs these tasks during a predefined 'maintenance window', set by default to be between 10pm and 6am the next day, although these times can be customized to reduce impact on other tasks (such as batch loads) that might be taking place.

The third component of the self-managing framework is 'Server Generated Alerts', a method where the database server sends notifications via email to the DBA - including a recommendation as to how best to deal with the situation. Alerts will normally be raised where the database itself cannot deal with the situation that has arisen, such as when there is insufficient space on a disk unit to extend a datafile.

Lastly, and perhaps the most exiting of all the self-managing component frameworks, is the Automatic Database Diagnostic Monitor. This component analyzes the data captured in the Automatic Workload Repository and uses an artificial intelligence algorithm, similar to that found in Oracle Expert, to analyze areas such as lock contention, CPU bottlenecks, I/O usage and contention, issues with checkpointing and so on, in much the same way that a DBA would currently do by analyzing STATSPACK reports.

More details on the self-tuning and maintenance features in Oracle 10g can be found in this OracleWorld white paper by Sushil Kumar.