Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 29, 2007

Oracle Database 11g Release 1 New Features Summary, Part 1

By Jim Czuprynski

Synopsis. Oracle Database 11g Release 1 (11gR1) is due to be released in July 2007. Although not all new features have been fully documented yet, this article – the first in this series – takes some tantalizing glimpses into the upcoming improvements to database performance and database management that expand even further the revolutionary concepts introduced in Oracle Database 10g.

I’ve been participating in the Oracle Database 11g Release 1 beta evaluation program over the past few months. As the release of Oracle Database 11g draws ever nearer, I’ve been plumbing the depths of the most recent beta releases, perusing the release documentation, and deciding how best to present (and eventually implement!) the myriad new and improved features this new release has to offer. I must say that even though Oracle Database 10g impressed me with the breadth of its changes, I’m still trying to wrap my brain around the even more impressive upgrades in this next release.

So here’s the first group of my personal favorites among the plethora of Oracle Database 11g’s new features. Please be sure to visit Oracle 11g Central periodically over the next several months as I dive much more deeply into these and many other features.

#1: Result Caches

I’ve often wished that the Oracle database would provide a method to retain in memory the result set from a complex query that contains what I like to call reference information. These are data that hardly ever change, but must still be read and used across multiple applications - for example, a list of all country codes and their corresponding names for lookup when processing addresses for new international customers, or a list of all ZIP Codes in the Midwestern US.

Oracle Database 11g fills this gap with three new structures called result caches, and each structure has a different purpose:

  • The SQL query result cache is an area of memory in the Shared Global Area (SGA) that can retain the result sets that a query generates.
  • The PL/SQL function result cache can store the results from a PL/SQL function call.
  • Finally, the OCI client result cache can retain results from queries or functions on the application server from which the OCI call originated.

By retaining result sets in these in-memory caches, the results are immediately available for reuse by any user session. For user sessions that connect to the database through an application server, the OCI client cache permits those sessions to simply share the results that are already cached on the application server without having to reissue a query directly against the database. These result caches therefore hold great promise for eliminating unnecessary “round trips” to the database server to collect relatively static reference data that still needs to be shared across many application servers or user sessions – a potentially immense improvement in overall database throughput.

#2: Improved SQL Tuning

If you’ve already experienced the advice for SQL performance improvements that Oracle Database 10g’s SQL Tuning Advisor and SQL Access Advisor provide, you’ll be pleasantly surprised with Oracle 11g’s enhanced SQL tuning capabilities. Here’s a brief sample:

  • SQL statements can now tune themselves via an expansion to the automatic SQL tuning features that were introduced in Oracle Database 10g.
  • Statistics for the Cost-Based Optimizer (CBO) are now published separately from being gathered. This means that recomputed statistics for the CBO will not necessarily cause existing cursors to become invalidated.
  • Multi-column statistics can be collected for two or more columns in a table. This gives the CBO the ability to more accurately select rows based on common multi-column conditions or joins.
  • SQL Access Advisor can now make recommendations on how partitioning might be applied to existing tables, indexes, and materialized views to improve an application’s performance.
  • Oracle Database 11g now supports retention of historical execution plans for a SQL statement. This means that the CBO can compare a new execution plan against the original plan and, if the old plan still offers better performance than the new one, it can decide to continue to use the original execution plan.

#3: New System Testing Tools

As a DBA, one of the most bedeviling problems that I’ve regularly faced is to be able to predict accurately how the next set of changes to the database’s application code, database patch set, or hardware configuration will affect that database’s performance. That usually meant purchasing a relatively expensive third-party package (e.g. Mercury Interactive’s LoadRunner) to generate a sample workload against the database using the next version of the application code, and then comparing the results against baseline performance for the current application code version.

Fortunately, Oracle Database 11g has come to the rescue with two new utilities that offer monumental strides forward in system testing:

  • Database Replay. Database Replay can capture generated workloads from production systems at the database level. Therefore, it’s no longer necessary to run actual application code to duplicate the load on the database, and this also improves accuracy of the simulated workload because it limits or removes other factors like network latency. These captured workloads can then be replayed on a quality assurance database so that the impact of application changes, software patches, and even hardware upgrades can be measured accurately. This feature is especially valuable in detecting performance issues that could potentially hamstring a production database’s performance that might go otherwise undetected until well after changes have been deployed.
  • SQL Performance Analyzer. A robust complement to the Database Replay facility, the SQL Performance Analyzer (SPA) leverages existing Oracle Database 10g SQL tuning components. The SPA provides the ability to capture a specific SQL workload in a SQL Tuning Set, take a performance baseline before a major database or system change, make the desired change to the system, and then replay the SQL workload against the modified database or configuration. The before and after performance of the SQL workload can then be compared with just a few clicks of the mouse. The DBA only needs to isolate any SQL statements that are now performing poorly and tune them via the SQL Tuning Advisor.

#4: Repair Advisors and Fault Diagnostics

Oracle Database 10g introduced an impressive plethora of database performance advisors like the Segment Advisor, the Undo Advisor, the SQL Access Advisor, the SQL Tuning Advisor, the MTTR Advisor, and the ultimate expert system for tuning database performance: the Automatic Database Diagnostic Monitor (ADDM).

Oracle Database 11g expands this advisory framework with several new Database Repair Advisors. The chief goals of these new Advisors are to locate root causes of a failure, identify and present options for repairing these root causes, and even correct the identified problems with self-healing mechanisms. Oracle Database 11g also adds a series of improved fault diagnostics to make it extremely easy for even an inexperienced DBA to detect and quickly resolve problems with Oracle 11g databases. Here are the highlights of these new features:

  • Automatic Health Monitoring. When a problem within the database is detected, the new Health Monitor (HM) utility will automatically perform a series of integrity checks to determine if the problem can be traced to corruption within database blocks, redo log blocks, undo segments, or dictionary table blocks. HM can also be fired manually to perform checks against the database’s health on a periodic basis.
  • Automatic Diagnostic Repository. The Automatic Diagnostic Repository (ADR) is at the heart of Oracle Database 11g’s new fault diagnostic framework. The ADR is a central, file-based repository external to the database itself, and it’s composed of the diagnostic data – alert logs (in XML format!), core dumps, background process dumps, and user trace files - collected from individual database components from the first moment that a critical error is detected.
  • Support Workbench. Though it’s stored outside of the database itself, the ADR can be accessed via either Enterprise Manager or command-line utilities. Once the ADR has detected and reported a critical problem, the DBA can interrogate the ADR, report on the source of the problem, and in some cases even implement repairs through the Support Workbench, a new facility that’s part of Enterprise Manager.
  • Incident Packaging Service. If the problem can’t be solved using these tools, it may be time to ask for help from Oracle Support. The new Incident Packaging Service (IPS) facility provides tools for gathering and packaging all necessary logs that Oracle Support typically needs to resolve a Service Request.
  • Hang Manager. Oracle Database 10g introduced the Hang Analysis tool in Enterprise Manager, and Oracle Database 11g now expands this concept with the Hang Manager. Through a series of dynamic views, it allows the DBA to traverse what’s called a hang chain to determine exactly which processes and sessions are causing bottlenecks because they are blocking access to needed resources. And since it’s activated by default on all single-instance databases, RAC clustered databases, and ASM instances, it’s now possible to track down the source of a hang from one end of the system to the other.

#5: Flashback Enhancements

Oracle Database 10g dramatically expanded database recoverability with the ability to perform an incomplete recovery of the database with Flashback Database. Oracle Database 10g also provided four new logical database recovery features: Flashback Table, Flashback Drop, Flashback Version Query, and Flashback Transaction Query. Oracle Database 11g expands this arsenal of recovery tools with two new Flashback features:

  • Flashback Transaction. Essentially an extension of the Flashback Transaction Query functionality introduced in Oracle Database 10g, Flashback Transaction allows the DBA to back out of the database one or more transactions -- as well as any corresponding dependent transactions -- by applying the appropriate reciprocal UNDO statements for the affected transaction(s) to the corresponding affected rows in the database.
  • Flashback Data Archive. This new feature offers the ability to retain the reciprocal UNDO information for critical data significantly beyond the point in time that it would be flushed out of the UNDO tablespace. Therefore, it’s now possible to hold onto these reciprocal transactions essentially indefinitely. Once this feature is enabled, all retained transaction history can be viewed, and this eliminates the cumbersome task of creating corresponding history tracking tables for critical transactional tables. And as you might expect, Oracle Database 11g also provides methods to automatically purge data retained in the Flashback Data Archive once a specified retention period has been exceeded.

This was the first group of my personal favorites among Oracle Database 11g’s new features. Please be sure to visit Oracle 11g Central periodically over the next several months as I dive much more deeply into these and many other features.

» See All Articles by Columnist Jim Czuprynski

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM