, by Mike Ault, Daniel Liu and Madhu Tumma.
On one hand, Oracle10g is more complex and robust than previous database versions with its wealth of new tuning options, sophisticated tuning tools and enhanced tuning parameters. On the other hand, it is simpler than any Oracle database before it. At your option, you can disable much of the flexibility (and complexity) of Oracle and create an easy to maintain system that requires little experienced human intervention.
This dichotomy is an amazing new feature and allows Oracle10g to be either robust or simple. Much of the new 10g features address this issue, and our experts will explore the important details of every important 10g enhancement for the DBA.
10g provides a wealth of features that can be used to automate almost every aspect of its database administration. It is important to note that these automation features are optional, and they are not intended to replace standard DBA activities. Rather, the Oracle10g automation features are aimed at shops that do not have the manpower or expertise to manually perform the tasks.
The automation features appear to be aimed at bundling two products into a single package. According to Oracle's Tom Kyte: "There is a huge market out there for "small self-running databases." Following the 80/20 rule -- many things should be self managing since people are in fact not managing them. It is harder to break into this smaller market than to remove anyone at the high end. Think about it: Would you just set a single parameter -- the SGA size -- on your high-end OLTP system and let it be? Or might you tune that? You could just set the SGA size on your small-end systems and let them run "better than good enough."
So, if your 10g database does not require detailed, expert tuning, then the automated features might be a good choice. They are targeted at these market segments:
- Small shops: Small installations that can't afford a trained Oracle DBA.
- Shops with over-worked DBAs: Large shops with hundreds of instances where the DBA does not have time to properly tune each system.
Remember, the automated features are not for every shop, and a human DBA will almost perform better than the automated Oracle features.
Let's take a closer look at the 10g automation features.
Automatic Workload Repository
Automatic Workload Repository (AWR) defaults to a collection interval every 30 minutes and collects data that is the foundation for all of the other self-tuning features. AWR is very much like STATSPACK, especially the level-5 STATSPACK collection mechanism where top SQL is collected every hour, based on your rolling thresholds for high-use SQL. In addition to the SQL, AWR collects detailed run-time statistics on the top SQL (disk reads, executions, consistent gets) and uses this information to adjust the rolling collection threshold. This technique ensures that AWR always collects the most resource-intensive SQL.
Automatic Maintenance Tasks
The Automatic Maintenance Tasks (AMT) automate the routine tasks of refreshing statistics when they become stale, and rebuilding indexes when they become sub-optimal. The AMTs are scheduled for execution inside the new Oracle10g Unified Scheduler (US), providing a primitive DBA functionality.
Automatic Database Diagnostic Monitor
The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data, much the same as a human DBA would analyze a STATSPACK report. ADDM searches for lock-and-latch contention, file I/O bottlenecks and SGA shortages just like a human DBA. The ADDM then triggers automatic reconfiguration using the Automatic Storage Management (ASM) and Automatic Memory Management (AMM) components.
Automatic Memory Management
The Automated Memory Management (AMM) component manages the RAM inside the System Global Area, much the same way as the automatic PGA management feature in Oracle9i (pga_aggregate_target) automates the sort and hash areas with PGA RAM. The ASM uses real-time workload data from AWR and changes the sizes of the shared pool and data buffers according to the current workload.
Automatic Storage Management
The Automatic Storage Management (ASM) feature allows for the automatic stripe-and-mirror everywhere approach to be used to automatically load balance the disk I/O subsystem and remove the need for the DBA to specify physical file locations when allocating a tablespace.
Automated features aside, there are other new 10g features that I find especially intriguing.
SQL Tuning Adviser
The SQL Tuning Advisor (STA) works with the Automatic Tuning Optimizer (ATO) to analyze historical SQL workload (using data from the AWR), and generates recommendations for new indexes and materialized views that will reduce the disk I/O associated with troublesome SQL statements.
Server Generated Alerts
Server Generated Alerts (SGA) interfaces with the US to send e-mail messages when an external problem is impeding Oracle performance. External problems might include a UNIX mount point that is full, causing a failure of ASM files to extend or a RAM shortage with the System Global Area.
Oracle10g is one of the most important new releases of Oracle in many years. The advent of Oracle10g will definitely broaden the footprint of Oracle in the small to mid-sized market, plus provide the Oracle DBA with a wealth of highly-intelligent new features to simplify database administration.
About the author: Donald K. Burleson is one of the world's top Oracle database experts with more than 20 years of full-time DBA experience. He specializes in creating database architectures for very large online databases and he has worked with some of the world's most powerful and complex systems. A former adjunct professor, Don Burleson has written 32 books, published more than 100 articles in national magazines and serves as editor-in-chief of Rampant TechPress. Don is a popular lecturer and teacher and is a frequent speaker at Oracle OpenWorld and other international database conferences. Don's Web sites include www.dba-oracle.com and www.remote-dba.net.
This was first published in December 2003