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

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

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles