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:

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.