As Published In September/October 2003

Oracle Database 10g
The World's First Self-Managing, Grid-Ready Database Arrives

By Kelli Wiseth

Oracle's new self-managing database increases performance and availability while enabling commercial grid computing.

There are trends. And there are trendsetters. In the early to mid-1990s, Oracle foretold the internet computing paradigm that organizations of every stripe have now woven into the fabric of their businesses. In the process, IT infrastructure has become extremely critical to the enterprise. "Businesses have become more dependent than ever on their IT systems for everything from day-to-day operations to providing service to their customers and clients," says Sushil Kumar, director of product management for Database Manageability at Oracle. "And many new-generation businesses, such as eBay and Amazon, rely completely on their IT infrastructure's being available—if the system goes away, their entire business is in jeopardy."

In short, says Kumar, IT systems have truly become strategic to the enterprise. And that has had a profound impact on the need for availability, scalability, and high performance of IT systems for organizations of all kinds. Downtime, even for much-needed maintenance, is not an option when a global business must run 24/7.

At the same time, says Kumar, there's growing pressure to maintain profitability amid ever-growing competition in a global economy that continues to tighten its belt. The result, is that "organizations must minimize operating expenses across the board—and IT is no exception," says Kumar.

But as IT systems have become more strategic and integral to the core business, they have also become more complex, more difficult to manage, and more costly. Complexity adds to costs across the board, in terms of time, labor, potential failures, and inability to recover from failure effectively. According to Kumar, these are all reasons why "one of the biggest challenges facing most organizations today is managing a strategic part of the business, its IT systems, more effectively than ever—ensuring the highest performance, scalability, and availability—but at a significantly lower cost than before." These are also some of the reasons that commercial grid computing, enabled in part by cost-effective blade servers, is getting so much attention today. For small incremental costs, organizations can gain more processing power to be used by all data center resources, delivering faster performance and high availability and scaling as needed—but only if the software can effectively take advantage of that architecture.

Blades Everywhere The blade servers that have become the de facto platform for grid computing aren't just for Linux; they ship in many sizes and flavors. For example, Fujitsu's PRIMERGY blade-server line supports Microsoft Windows platforms as well as Red Hat Linux Advanced Server and SuSE ES-7 and ES-8, to name just a few. Sun Microsystems is shipping both UltraSPARC-based blades running Solaris 8 or Solaris 9 and x86-based blade servers for 32-bit applications running on either Solaris or Linux. Nor are blade servers just for grid deployments. Many organizations want to gain the benefits of server consolidation—centralization, reduction in complexity, greater control by use of blade servers and enclosures. Blades help because they reduce the number of power units, cables, and sundry other components, which are provided in a backplane that supports the blades (blades are typically just the CPU or CPUs, buses, memory units, and a local hard-disk drive—(in a small form factor—for the OS and system BIOS).

Clearly, the time is right for software that monitors and manages itself: software that eases management complexity in a cost-effective manner.

Without self-managing software, companies will be held hostage by increasingly complex applications and heterogeneous systems that currently require scores of highly trained administrators. Management costs are only part of the equation: These same companies will be unable to implement increasingly sophisticated applications that might otherwise offer great business benefits.

Fortunately, Oracle Database 10g has been designed to meet these technology challenges head-on. Delivering faster performance and higher availability while reducing management costs, Oracle Database 10g lets data centers for global enterprises as well as enterprises with resources scattered around the globe corral those resources for optimal utility and usher in a new era of available, powerful, and manageable enterprise computing.

Oracle Database 10g is the realization of Oracle's manageability vision, an overarching strategy across the company that began to take shape many years ago. The manageability strategy has two goals—simplify the management of the database itself, by automating much of the monitoring and maintenance, and provide data centers with a broadly focused, rich management tool that will allow them to manage all the components—not just the database—that the data center must manage, regardless of where the components are (in the data center, deployed across a grid, scattered worldwide) and what they are (storage, clusters, application servers, and so on). It all starts with a self-managing database.

The Key to Manageability: The Self-Managing Database

"Oracle's long-term vision for manageability is nothing short of complete automation of the tasks associated with managing the Oracle database," says Kumar. For the past three years, a cross-functional team of more than 200 Oracle developers and architects has been involved in creating the infrastructure and tools to support this vision of proactive, automatic management of the database. Automating the daily, routine tasks associated with managing the Oracle database will free IT professionals to put their talents to use in better, more strategic ways for their business. There's little value-add to organizations or data centers in having a highly paid technologist performing basic monitoring tasks. "The database needs to automatically maintain itself—it shouldn't require any administrative intervention, unless absolutely necessary," says Kumar. The more the database can do to maintain itself, the fewer the required interventions and the lower the total cost of ownership (TCO) over the life of the system. "It's important," adds Oracle's Kumar, "to lose the complexity yet retain the flexibility."

Part of the TCO is the cost of highly skilled technical staff members—who aren't always easy to find. Bob Shirley, a lead developer for Schlumberger Information Solutions (SIS), which provides a suite of software tools for use in oil and gas exploration, drilling, and production, states: "The availability, worldwide, of highly trained DBAs is a serious concern to us. And the amount of time a DBA has to spend babysitting the database is also a concern." SIS is busy developing "the next-generation database for oil-field services and new applications that will run against that database." So far, Shirley is favorably impressed by the steps taken in Oracle Database 10g to lower maintenance costs and build in more automation. "Oracle is taking big strides in that direction with the first release of Oracle Database 10g," he says.

Many Oracle Database 10g beta customers echo that sentiment. "Losing complexity while getting more-manageable software pieces in place is one of our top goals," says Rob Leaman, head of the Database department in the Business Information Management System division at Deutsche Post IT Solutions GmbH (DPITS), the IT service provider for Deutsche Post World Net, spun off from that company as a separate entity in 2002. Leaman knows a lot about complexity. DPITS—with some 400 projects currently under way—puts the people in place to develop the vast array of custom applications that directly support DPITS' key client, DPWN, which comprises Deutsche Post, DHL, and Postbank. DPWN's core competency is essentially supply chain management logistics, providing mail communication, parcel, express, logistics, and financial services worldwide. The applications DPITS is involved in developing do everything from sorting mail and bar-coding to monitoring movements of container cargo and air freight; the running of the applications is outsourced to service provider T-Systems International.

However, says Leaman, "we don't just 'fire and forget' the applications." Because DPITS doesn't run the applications in-house, it's especially important that the database be easy to manage: If T-Systems has a problem with an application that it can't solve within a certain time frame, DPITS is the next level of support. With management tools that are more automatic and intelligent, Leaman expects, "it will be a lot faster to solve a problem or, if something's gone wrong, we'll be able to get the system up and running a lot faster than before."

New Intelligent Self-Management Infrastructure

So how does all this magic happen? A self-tuning, self-managing database requires the capability to automatically "learn" about how it is being used. "In Oracle Database 10g, we've implemented an entirely new infrastructure that allows the database to capture workload information and use that information to make numerous self-management decisions," says Oracle's Kumar. Instrumentation has been built into every layer of the technology stack, capturing vital metadata that will be used to diagnose problems and storing the information in the database itself in the Automatic Workload Repository (AWR)—a fundamental component of the new management infrastructure that plays a central role as the "data warehouse of the database."

Tapping into these mechanisms is a full suite of advisors that provide guidance on how the database operation could be further optimized. SQL Tuning and SQL Access Advisor, for example, provide recommendations for running SQL statements faster. Then there are memory advisors that let you size various memory components without resorting to trial-and-error techniques. There's also a Segment Advisor, which handles all space-related issues, such as recommending wasted-space reclamation, predicting the sizes of new tables and indexes, and analyzing growth trends, and an Undo Advisor, which lets you size the undo tablespace.

A complete alert infrastructure is integrated with these components to notify administrators of any current or impending problems; all of these components are available through the Web-based Enterprise Manager console. Most alerts also contain recommended corrective actions for the problem being reported, which may include invoking one of the advisors—either through Enterprise Manager (EM) or from a command line—to obtain detailed advice. All the necessary infrastructure and related components, such as EM, are installed automatically when you install Oracle Database 10g, whether on a single node or in a Real Application Clusters (RAC) configuration, says Kumar.

Holistic Self-Management

The most revolutionary aspect of the new self-managing Oracle database is its ability to diagnose its own performance problems. Oracle Database 10g includes a self-diagnostic engine built right into the database kernel called Automatic Database Diagnostic Monitor (ADDM). ADDM automatically monitors the state of the database at short, regular intervals (30 minutes by default), providing ongoing database performance diagnostics. Oracle's Kumar likens ADDM to "a genie in your database—if you have a performance problem, you just ask the database what the problem is and it automatically analyzes the complete database system and comes up with recommendations. You get a full list of all the problems ADDM finds and possible solutions to them all." Much of the data in ADDM (and in the other advisors, for that matter) is presented in graphical form—line graphs over time, bar charts, pie charts—as appropriate for the type of data, making it easy to get a sense of things at a glance. Kumar considers ADDM to be a revolutionary breakthrough, with a sophisticated performance diagnostic engine that encapsulates decades of knowledge and experience from Oracle's own performance tuning experts.

In addition to looking at the results of proactive ADDM analysis, you can also run ADDM manually from Enterprise Manager or from the command line, using its PL/SQL interface. ADDM does a top-down analysis of potential bottlenecks, coming up with a set of findings that includes root causes and recommendations with rationale. In addition to identifying problems, ADDM also reports how much impact each of the problems is having on overall system performance and how much benefit can be gained by resolving it. This impact-benefit analysis will help DBAs focus on problems whose resolutions result in the biggest performance gains.

DPITS's Leaman sees many opportunities to leverage ADDM's diagnostic capabilities, even during the development phase of a project lifecycle, allowing developers to "diagnose in advance," before putting any application into production. "We'll be able to system-test even more efficiently before we actually pass it on. We can play if-then-else with the application code—'what happens if we do this?'—a lot more easily than we could before. So we diagnose the system before we pass it on, which I'm expecting will reduce problems." Nonetheless, Leaman says, the major benefit will be to reduce downtime and fix any problems. "Because we've got a history of things going on in the database as well, we can actually look back if there's a problem to be solved and say, 'What was happening at that time; what actually caused the problem?' It all comes down to reduction in downtime or problem-fixing time," says Leaman.

Schlumberger Information Solutions Houston, Texas www.sis.slb.com Schlumberger Information Solutions is a product group within Schlumberger Oilfield Services, the world's premier oil-field services company, supplying a wide range of technology services and solutions to the international oil and gas industry. Deutsche Post IT Solutions GmbH Berlin, Germany www.dp-itsolutions.de/itsolutions Deutsche Post IT Solutions (DPITS) is the IT service provider for the Deutsche Post World Net group. An independent company, DPITS creates the conditions for high-quality IT solutions.

Wayne Hewitt, senior database administrator with SunGard Treasury Systems, agrees. Hewitt works in SunGard's Christchurch, New Zealand, office, a major development shop for SunGard's AvanteGard Quantum product. (AvanteGard Quantum is a treasury management system that provides integrated treasury, risk, accounting, cash management, and other capabilities and is used by many of the world's largest enterprises.) Hewitt keeps more than 500 production, development, support, and QA databases running smoothly, and he helps solve database issues SunGard's AvanteGard Quantum customers might have—if something seems to be a database-related problem, it is likely to land on his desk. According to Hewitt, "the ability to look back in time at the system and specific SQL to identify what bottlenecks there have been is a giant step forward. For example, a common request heard either at a customer site or internally at a development site is, 'Hey, the system or program is running slower than normal.' By the time you get in and look at the server basics (CPU, memory, disk, and network), and the database (current activity, system tables/views, and run SQL queries) the problem SQL might have finished and you're left without enough information for tuning or preventive measures, and you hope the problem was a one-off query or report, but you can't be sure. Many problems are also hard to re-create. For example, 'why did the automated overnight QA scripts take a lot longer than normal last Monday night?' With these new features, we can properly investigate both problems by going back and taking a look at what took the most resources over that period. We can identify what might have caused the problem and then get expert recommendations from Oracle Database on how to fix it."

The ability to get better diagnostic information is invaluable in both development and production environments. "We just had a customer with an intermittent network issue that affected our application," but the initial diagnosis showed that it was a connection problem with the database, says Hewitt. "If we can go in, see the accurate timings, and examine the history, then this will help us better identify the actual source of the problem. That's a huge benefit for the customer and us."

The advice given on SQL access and SQL tuning cuts across two application types: custom applications you write yourself, so you can fix the code, and packaged applications, whose code you can't fix, in which case the SQL Tuning Advisor calls the optimizer to generate a profile, which is stored in the data dictionary and can be used at runtime. This is how the SQL Tuning Advisor can help speed up packaged applications.

Peter Deusser and Dharmendra Patel of Aventis Pharma see a possibility to get better performance out of vendor applications by using the SQL Access Advisor and the SQL Tuning Advisor. Aventis is a global pharmaceutical company headquartered in Strasbourg, France, and Bridgewater, New Jersey, with research-and-development sites located in Paris, France; Bridgewater, New Jersey; Frankfurt, Germany; and Tokyo, Japan. Deusser is team leader, global strategic services, in the Drug Innovation & Approval (DI&A) division of Aventis; he heads a globally distributed team that includes Patel, a project leader in the New Jersey research-and-development center. Deusser's team participates in development and integration projects involving different vendor applications, which are all critical to ensuring that Aventis can meet the varied regulatory requirements for releasing drugs around the globe, much of which involves creating extensive documentation that tracks clinical studies, adverse events, and other such information.

Deusser's group is responsible for the databases that manage the later phases of clinical studies, for adverse-event tracking, and for the regulatory databases. According to Deusser, "Because we rely mostly on vendor applications, we can't change or influence the SQL statements they send to the database, if we have performance problems. So we want to be able to get better performance without waiting for a fix from the vendors. If we are able to improve the performance and availability while in production, that's a good choice for us."

DPIT's Leaman also wants to be able to use the SQL Tuning Advisor with packaged applications. "The automatic tuning optimizer takes SQL statements it's getting, notices they're not so good, and optimizes them—without changing the statements themselves. You can actually pick up performance without anyone having to change the application," he explains.