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.
