Oracle 11g New Features Peek
by Don Burleson
Every new release of Oracle is highly
anticipated, especially when the release introduced major new
functionality. I remember when Oracle7 was in beta and
wondering about the new “cost based optimizer”, and we all
wondered about these new “hash joins” and pondered the mysteries
of bitmap indexes. In Oracle8, we replaced EBU with RMAN and
earned about partitioning and star transformations, and so on,
high anticipation with every release.
Oracle is remarkably predictable when they
release major enhancements as we can use linear regression to
predict when each new release will be offered, about every 3.2
years:
Release
|
Year
|
Years between
releases
|
|
|
|
Oracle6 |
1988 |
|
Oracle7 |
1992 |
4 |
Oracle8 |
1997 |
5 |
Oracle8i |
1998 |
1 |
Oracle9i |
2001 |
3 |
Oracle10g |
2004 |
3 |
|
avg. years
|
3.2
|
|

|
Let’s take a look at the latest release of Oracle 11g and see
what’s important.
Note: Oracle 11g is still in
beta testing and all beta participants are sworn to absolute
secrecy. Beta participants are not even allowed to confirm or
deny if they are in the Oracle 11g beta, so we must reply on
what’s been published about Oracle 11g by Oracle Corporation.
Most of the following information about Oracle 11g was gleaned
from announcements by Oracle Corporation at Oracle OpenWorld
2006.
Inside Oracle 11g
At Oracle Openworld 2006, Oracle announced
some exciting new features of Oracle 11g and they promise a
mind-boggling 482 new features. Many of these enhancements are
“minor” (e.g. the new “SIMPLE INTEGER” column datatype) while
others are profound.
The trick to understand the 11g new
features is to categorize the features as they apply to your own
environment. For the purpose of this short article, I’ll group
the 11g new features into these categories:
- RAC/Grid enhancements
- Oracle DBA enhancements
- Oracle App Server enhancements
- Oracle developer enhancements
11g RAC/Grid enhancements
Oracle continues to enhance Real
Application Clusters in Oracle11g and we see some exciting new
features in RAC manageability and enhanced performance:
-
RAC instance load balancing -
Staring in Oracle 10g release 2, Oracle JDBC and ODP.NET provide
connection pool load balancing facilities through integration
with the new “load balancing advisory” tool. This replaces the
more-cumbersome listener-based load balancing technique.
Starting in 10gr2 we see a RAC load balancing advisor utility.
-
Oracle 11g RAC parallel upgrades -
Oracle 11g promises to have a rolling upgrade features whereby
RAC database can be upgraded without any downtime. Ellison
first promised this feature in 2002, and it is a very
challenging and complex 11g new feature.
-
Faster Grid provisioning – Every
server blade in Oracle Grid n=must have Oracle RAC pre-installed
and Oracle 11g OEM has “provisioning” installs where a binary
footprint is tar'ed to the server blade and configured, without
a cumbersome install process.
-
ADDM for RAC
- The Automatic
Database Diagnostic Monitor (ADDM) in 11g can give advice on the
whole RAC (database level), not just at the instance level.
Directives have been added to ADDM so it can ignore issues you
are not concerned about. For example, if you know you need more
memory and are sick of being told it, you can ask ADDM not to
report those messages anymore.
Oracle 11g DBA enhancements
One major marketing for 11g related to the
new feature dubbed “scalable execution”. Scalable execution
consists of a number of features:
- Feature Based Patching - All one-off
patches will be classified as to which feature they affect. This
allows you to easily identify which patches are necessary for
the features you are using. EM will allow you to subscribe to a
feature based patching service, so EM automatically scans for
available patches for the features you are using.
- Automatic Memory Tuning - Automatic
PGA tuning was introduced in Oracle 9i. Automatic SGA tuning was
introduced in Oracle 10g. In 11g, all memory can be tuned
automatically by setting one parameter. You literally tell
Oracle how much memory it has and it determines how much to use
for PGA, SGA and OS Processes. Maximum and minimum thresholds
can be set.
- Faster sorting - Starting in 10gr2
we see an improved sort algorithm, “Oracle10gRw introduced a new
sort algorithm which is using less memory and CPU resources. A
hidden parameter _newsort_enabled = {TRUE|FALSE} governs whether
the new sort algorithm will be used.”
- Adaptive Metric Baselines -
Notification thresholds in 10g were based on a fixed point. In
11g, notification thresholds can be associated with a baseline,
so the notification thresholds vary throughout the day in line
with the baseline. The AWR baselines from 10g have been
extended in 11g to allow automatic creation of baselines for use
in other features. A rolling week baseline is created by
default.
- Automatic Diagnostic Repository (ADR)
- When critical database errors are detected (based on the DBA’s
definitions), they automatically create an “incident” object and
the DBA is notified with details on their “incident”. The new
Incident Packaging Service (IPS) encapsulates all information
about the incident, requests further tests and information if
necessary, and allows you to send the whole package to Oracle
Technical Support. Source: Dr. Tim Hall
- Function-based data columns (FBC) -
Oracle 11g has “virtual table columns”, which are columns that
are functions of other column values. For example, here we
create column C3, which contains no data, just a derivation of
the values of other columns. This is related to virtual
indexes, indexes that are based on functions:
create
table t1
(
c1 number,
c2 number,
c3 as (c1+c2) virtual
);
- Query results caching - This feature
automatically caches the results of a SQL query (as opposed to
the traditional caching of data blocks). Oracle described this
as "buffer cache taken to the next level". The DBA turns-on this
new feature with the command "alter table xxx cache results"
command and the resulting per-process cache is shared across
multiple session and at the client level. We also see the
related /*+ result_cache */ SQL hint, which suggests that
the result data will be cached in the data buffers, and not the
intermediate data blocks that were accessed to obtain the query
results. Oracle claims that you can cache both SQL and PL/SQL
results for super-fast subsequent retrieval.
- Improved table/index compression –
Oracle segment compression now works for all DML, not just
direct-path loads, so you can create tables as compressed and
use them for regular OLTP work. The compression syntax also
supports “add column” syntax.
- Interval partitioning for tables -
This is a new 11g partitioning scheme that automatically creates
time-based partitions as new data is added.
- Capture and replay database workloads
- You can capture a production workload (with
dbms_stats.gather_system_statistics) and apply it in a test
and development instance to make the test environments more like
production.
- Automated Storage Load balancing -
Oracle’s Automatic Storage Management (ASM) now enables a single
storage pool to be shared by multiple databases for optimal load
balancing. Shared disk storage resources can alternatively be
assigned to individual databases and easily moved from one
database to another as processing requirements change.
- Data Guard Load Balancing – Oracle
Data Guard allows for load balancing between standby databases.
- Listener Load Balancing - If
advanced features such as load balancing and automatic failover
are desired, there are optional sections of the listener.ora
file that must be present.
- Improved optimizer statistics collection
speed - Oracle 11g has improved the dbms_stats performance,
allowing for an order of magnitude faster CBO statistics
creation. Source: Mark Rittman
- Oracle SecureFiles - replacement for
LOBs that are faster than Unix files to read/write. Lots of
potential benefit for OLAP analytic workspaces, as the LOBs used
to hold AWs have historically been slower to write to than the
old Express .db files. Securefiles are said to be a huge
improvement to BLOB data types and they are faster with
compression and encryption.
- Oracle 11g audit vault - Oracle
Audit Vault is a new feature that will provide a solution to
help customers address the most difficult security problems
remaining today, protecting against insider threat and meeting
regulatory compliance requirements.
Oracle 11g developer enhancements
- Oracle 11g XML Enhancements - Oracle
11g will also support Content Repository API for Java Technology
(JSR 170). Oracle 11g has XML "duality", meaning that you can
also embed XML directives inside PL/SQL and embed PL/SQL inside
XML code. Oracle 11g XML will also support schema-based
document Type Definitions (DTD's), to describe internal
structure of the XML document.
- Fully Automatic SQL Tuning - The 10g
automatic tuning advisor makes tuning suggestions in the form of
SQL profiles that will improve performance. You can tell 11g to
automatically apply SQL profiles for statements where the
suggested profile give 3-times better performance that the
existing statement. The performance comparisons are done by a
new administrative task during a user-specified maintenance
window.
- Improved SQL Access Advisor - The
11g SQL Access Advisor gives partitioning advice, including
advice on the new interval partitioning. Interval partitioning
is an automated version of range partitioning, where new
equally-sized partitions are automatically created when needed.
Both range and interval partitions can exist for a single table,
and range partitioned tables can be converted to interval
partitioned tables.
- Scalable Java - The next scalable
execution feature is automatic creation of "native" Java code,
with just one parameter for each type with an "on/off" value.
This apparently provides a 100% performance boost for pure Java
code, and a 10%-30% boost for code containing SQL.
PL/SQL New Features
-
PL/SQL intra-unit inlining - In C
programming, you can write a macro that gets dynamically
included “inlined” whenever it’s called, a great way to provide
reusable code snippets. In Oracle 11g, PL/SQL stored procedure
are eligible for inlining if Oracle thinks it will improve
performance. No change to your code is required. Now you have no
reason for not making everything a subroutine! Source: Lewis
Cunningham
-
Scalable PL/SQL - The next scalable
execution feature is automatic creation of "native" PL/SQL (and
Java code), with just one parameter for each type with an
"on/off" value. This apparently provides a 100% performance
boost for pure PL/SQL code, and a 10%-30% performance boost for
code containing SQL. Source: Mark Rittman
-
PL/SQL "continue" keyword - This
will allow a C-Like continue in a loop, to bypass any "else"
Boolean conditions. A nasty PL/SQL GOTO is no longer required
to exit a Boolean within a loop.
-
Disabled state for PL/SQL - Another
11g new feature is a "disabled" state for PL/SQL (as opposed to
"enabled" and "invalid" in dba_objects).
-
Easy PL/SQL compiling - Native
Compilation no longer requires a C compiler to compile your
PL/SQL. Your code goes directly to a shared library. Source:
Lewis Cunningham
-
Improved PL/SQL stored procedure
invalidation mechanism - A new 11g features will be fine
grained dependency tracking, reducing the number of objects
which become invalid as a result of DDL.
-
Enhanced PL/SQL warnings - The 11g
PL/SQL compiler will issue a warning for a "when others" with no
raise.
-
Stored Procedure named notation -
Named notation is now supported when calling a stored procedure
from SQL.
-
Proxy connect for SQL*Plus - New
with 10r2 proxy identification in SQL*Plus, the "connect"
command has been enhanced to allow for a "proxy", to aid
applications that always connect with the same user ID:
connect sapr3[scott]/tiger
Again, this is just an incomplete preview
of Oracle11g new features and there will be continuously changes
as Oracle releases more information. I track upcoming 11g
changes, so visit my site at
www.dba-oracle.com for updates.
NOTE: Rampant author Laurent Schneider has some additional
insight into
creating
an Oracle Automatic Diagnostic Repository (ADR).
Author Bio:
Don Burleson has been a full-time DBA for
25 years and he has authored 34 books on Oracle database
management including five of the officially authorized Oracle
Press books.
|