One of the most interesting new features of Oracle Database 10g is the new 'self-managing' framework outlined in this white paper by Sushil Kumar, available on the Oracleworld site. Automatic maintenance and tuning has always been one of the key product differentiators for Microsoft SQL Server and 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.
All of the above components address the issue of performance tuning for the Oracle database instance. Of course, in real life, tuning the database is only one aspect of getting the most 'bang for your buck' with an Oracle database. Just as important is the process of tuning and optimizing the SQL and PL/SQL that applications use when running against your database server, as these often have far more impact on database performance than tinkering with SGA sizes and init.ora parameters. Oracle 10g's self-managing framework addresses the issue of poorly performing SQL and PL/SQL with the 'SQL Tuning Adviser' and the 'Automatic Tuning Optimizer', a set of components that analyzes candidate SQL statements, produces optimal execution plans and provides recommendations for tuning the statement. Coupled with the SQL Access Adviser, outlined in Lillian Hobbs' paper at the Oracleworld site, DBAs should find it easier with Oracle Database 10g to identify costly SQL statements and provide advice to developers on how to rectify the situation.
Of course the worry for all DBAs is that, if Oracle deliver on their promises and truly deliver a self-managing database, their jobs will no longer be needed as everything will be done automatically. However, as Tom Kyte points out on the AskTom site, this development is really aimed at two main areas; firstly, smaller-scale Oracle installations where in practice no database tuning and maintenance takes place, and secondly, where DBAs now have to manage hundreds of separate servers and instances, and they could do with some help in carrying out the routine maintenance and tuning tasks that with previous Oracle versions were pretty time consuming. There's always going to be a need for someone who understands how the database server actually works, especially where organizations push the database to its limits, and hopefully with many of these mundane tasks now taken care of automatically, DBAs can get more pro-actively involved in the design and architecture of new systems rather than panicking about existing ones that have stopped working properly.
7:17:39 AM 
|
|
