As Published In September/October 2003

Oracle Database 10g: Part 2

By Kelli Wiseth

Keeping Businesses Running 24/7

These days, no system can afford downtime. Fortunately, many of the new automated management features in Oracle Database 10g are designed to anticipate and prevent downtime. For example, the optimizer now automatically gathers its own statistics and intelligently refreshes them as needed, when significant changes have been made to the database tables. This is important, because stale statistics can lead to poor performance. Keeping statistics up to date automatically should help a lot of performance problems, says SunGard Treasury Systems' Hewitt. "A common problem at customer sites without database administrators on staff is that people didn't realize they should create and update database statistics," he says. "Customers would complain about bad performance and tell us our application had become a lot slower, especially after major changes. But they weren't keeping their statistics up to date.' So, with that aspect automated, performance of the database and our application will be better and more consistent."

Another feature that will ensure better performance and minimize out-of-memory errors and memory fragmentation problems, says Hewitt, is the new automated system global area (SGA) sizing capability. Rather than manually configuring the amount of memory allocated to the database buffer cache, shared pool, Java pool, and large pool, as in the past, DBAs should use the automatic shared memory-management capability and let Oracle Database 10g manage the memory itself. And because the memory is reallocated across the pools automatically as processing loads change, there "is no longer a requirement to set each SGA component to its maximum required value to handle peak loads," says Hewitt. "Our software will just keep working, and no one will even know that, internally, Oracle has allocated more memory to buffers or the shared pool as required, to improve performance levels and avoid errors. This is a huge benefit for us: Applications will run without memory errors and customers don't have to spend time tuning significant memory parameters, because Oracle Database will use all the allocated memory efficiently."

And of course, the new server-generated alerts help prevent failures from occurring by providing administrators accurate and timely notifications, and guiding them to resolutions proactively.

Performance Art

The automated tuning mechanisms in Oracle Database 10g, particularly the automatic statistics refresh in the optimizer, ADDM, SQL Tuning Advisor, and SQL Access Advisor, not only make the database more manageable and available but also directly affect performance by putting the tuning process within the grasp of any DBA. There's no point to having a database that can perform fast if it requires much effort to achieve that performance, says Vineet Buch, director of Performance Product Management at Oracle: "It's like having a Ferrari you can't drive faster than 65 miles per hour."

SunGard Treasury Systems Calabasa, California www.sungardtreasury.com SunGard Treasury Systems is a leading provider of treasury solutions to government and Fortune 100 clients. SunGard Treasury Systems is an operating unit of SunGard, a global leader in integrated IT solutions for financial services. Aventis Pharma Paris, France www.aventis.com Aventis is a global pharmaceutical company that develops prescription drugs for therapeutic areas such as oncology, cardiology, diabetes, respiratory/allergy, and anti-infectives; it also does research and development for human vaccines.

In addition, there are some strictly performance-focused improvements, says Buch. Several dozen of these enhancements are completely transparent, and you need do nothing (other than upgrade) to benefit from them. For example, PL/SQL performance is significantly improved, says Buch, because the PL/SQL compiler has been rewritten to generate less, more-optimal code. "We're seeing double-digit performance improvements—and everything is transparent in terms of existing PL/SQL code and applications—no code needs to be rewritten to gain the benefit of this improvement." In short, all PL/SQL code will run faster and use less memory, says Buch.

"Many of the transparent and explicit performance improvements were made for the Windows platform, including 64-bit Oracle on Itanium and Oracle on .NET," says Alex Keh, principal product manager, Windows Technologies group in Oracle's Server Technologies division. In addition, there are several significant enhancements for Windows developers.

Besides transparent performance improvements, such as faster PL/SQL, says Buch, there are several additional new performance-related capabilities DBAs and developers can take advantage of explicitly, many of which fall into the realm of decision-support-system or data warehousing capabilities. For example, applications that require very high data insertion and retrieval rates for first-in, first-out processing—such as the systems used by telecommunications companies to capture telephone call data as it's placed and then generate bills later by retrieving the data in the order received—can gain tremendous performance gains by using Oracle Database 10g's new sorted hash clusters table structure, says Buch.

Beyond the improvements that translate into faster processing speed in the database is another type of "performance" improvement: the way DBAs do their jobs. Bob Shirley of Schlumberger Information Solutions thinks the manageability improvements in Oracle Database 10g are "tremendous in what they can provide the enterprise-level DBA."

The Grid

Many of Oracle Database 10g's new groundbreaking self-management features will directly facilitate the deployment and full-scale operation of data center management, server consolidation initiatives, and the evolution to commercial grid computing. As Oracle's Sushil Kumar says, "The 'self-managing database' is just one part of the solution. More often than not, an application stack comprises not only the database server but also an application server, application code, and so on—and all these components must be managed as well."

That's where the second major prong in this release of the database—grid management—comes in. According to Stefan Petry, Oracle senior director of product management, System Management Products, EM provides "the management tools and utilities to support the entire system lifecycle. It starts with automated provisioning, includes management and monitoring during system operations, and allows changes and updates while the overall system keeps running." Automating the entire lifecycle is even more critical in a grid, because "you need to do all this efficiently and reliably across a very large number of components," says Petry. Furthermore, you want a single system to monitor and manage all types of elements—the database, applications, hosts, and storage and network elements. In a grid, says Petry, you'll find all of these elements, and you want to be able to do any of these operations on all elements, gridwide.

Next Steps READ more about Oracle Database10g oracle.com/database LEARN about Oracle Database 10g manageability /products/manageability/database DOWNLOAD Oracle Database /software/products/oracle9i

Using EM, administrators not only have access to key Oracle Database 10g features, such as ADDM, but they can also manage third-party grid components such as load balancers and storage systems, says Petry. The Web-based EM manages any number of Oracle databases; Oracle application server farms; and their hosts, such as Linux or UNIX hosts. "Furthermore, EM Application Performance Management (APM) monitors the real-time performance of your Web applications," explains Petry. "APM does that from an end-user perspective for all your users and your critical transactions and allows you to drill down into individual components for detailed root-cause analysis."

Managing Complexity with Power to Spare

The desire to gain the benefits of better management of systems and lower costs has been the impetus for the "strong trend toward more server consolidation since 1997," according to Gartner Group. In "Server Consolidation: An Updated Look" (May 23, 2003), Gartner analysts John Phelps and Mike Chuba write, "The magnitude of the server management problem can be seen in the typical enterprise data center, which probably contains hundreds of Unix and Intel servers. Adding a single application typically adds three to five servers to the data center for such things as production, development, testing, and backup. Many of the distributed servers run at low utilizations. No wonder, then, that enterprises are seeking to consolidate their servers."

The self-management capabilities of Oracle Database 10g help maximize the use of existing resources, helping to eliminate the need to purchase additional hardware frequently. For example, the Automatic Shared Memory Management feature allows the most optimal use of available SGA memory, and the ability to reclaim space from existing tables can eliminate the need to buy additional storage. Those are some of the benefits Aventis' Deusser hopes to achieve over the long term with consolidation projects. With more than 100 databases to manage, Deusser says, "We are looking at getting better manageability. It should be easier if we consolidate all our different databases together in only a few boxes."

Minimizing the complexity, containing the cost, and getting more value from existing technology resources is a key focus for organizations worldwide, making Oracle Database 10g a trendsetter indeed.

Kelli Wiseth ( kelli@alameda-tech-lab.com) is technology director at Alameda Tech Lab and Research Center.

Oracle Database 10g Removes Size Barriers With increasingly larger hard-disk-drive sizes commonplace and support for large files (defined as greater than 2GB) readily available in 64-bit operating systems, what's considered very large this year could well be on its way to being considered petite in years to come. With Oracle Database 10g's new bigfile tablespace feature, the ceiling on your ultralarge database just got lifted to a whopping (and theoretical, depending on the underlying OS, what it can support, and the block size you choose) 8 exabytes. That's 8 million terabytes, or three orders of magnitude over the current limit of 8 petabytes. Bigfile tablespaces simplifies database management by allowing you to create single-file tablespaces and letting you perform operations at the tablespace level rather than on the underlying datafile. (To support this capability, the SQL syntax for ALTER TABLESPACE has been extended.) Creating a bigfile tablespace is not much different than creating tablespaces (now called smallfile tablespaces to distinguish the two types) used to be; one difference is that bigfile tablespace size can be specified not only in kilobytes and megabytes but in gigabytes and terabytes as well. Here's an example: CREATE BIGFILE TABLESPACE datahuge /DATA FILE'/u02/oracle/data/ bigtbs01.dbf' SIZE 2T. It doesn't make sense to use bigfile tablespaces on systems that don't support large files. Doing so can significantly limit tablespace capacity. An Oracle Database 10g instance can support both bigfile and smallfile tablespaces concurrently; the SYSTEM and SYSAUX tablespaces continue to use the smallfile tablespaces and can't be created otherwise. Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (the default setting since Oracle9i Database). Finally, bigfile tablespaces should be used with Automated Storage Management, or other logical volume managers that support dynamically extensible logical volumes, striping, and RAID.