Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines


Mark Rittman

Oracle 10g - A Self-Managing Database?

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.

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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational