Looking at the New Edition

Our technologist takes a first look at Oracle Database 11g Release 2.

November/December 2009

In this issue, instead of using the usual question-and-answer format of the Ask Tom column, I’m going to point out some of the many new features of Oracle Database 11g Release 2 that have caught my eye. These are also the features I’ll be looking at in more depth over the next few months.

In Oracle Database 11g Release 1, there were substantial modifications and enhancements to Oracle Data Guard, including the Oracle Active Data Guard feature. The two Oracle Data Guard features that caught my attention in Release 1 were

1. The ability to have a physical standby instance open for read-only operations while it was in managed recovery mode.

2. The ability to use a physical standby as a realistic testing instance. A physical standby is the perfect instance to use for testing, because it is a bit-for-bit, byte-for-byte copy of the production instance, and a test of a change in the physical standby instance would very closely mimic what will happen in production when you roll out the change.

Well, Oracle Data Guard has done it again. The first thing that jumped out at me about Oracle Data Guard in Oracle Database 11g Release 2 is its ability to perform automatic block-level recovery of corrupt blocks in a production—or standby—instance. If a bad block is discovered on either instance, Oracle Data Guard will be able to automatically transmit a good copy of the block in either direction (standby to production or production to standby) and restore/recover the block—perhaps before anyone knows it has gone bad.

The second new capability is standby query offloading. In Oracle Database 11g Release 1, the physical standby database could be queried in real time as redo was applied, but the physical standby could have diverged from the production instance by many seconds or minutes, due to network outages or latencies. This would mean that queries against the standby database could return stale (out of date) information to applications. Starting in Oracle Database 11g Release 2, administrators and applications can configure a STANDBY_MAX_DATA_DELAY session parameter that controls how far behind the production instance the standby is permitted to lag. If the standby falls behind the production instance by a longer time than this setting permits, the application will receive a notification about that so it can reissue a query to the production instance to get current information, if necessary. This capability enables you to safely offload read-only and read-mostly workloads from your production instance to your standby instance, as needed.

Edition-Based Redefinition, in my opinion, is the killer new feature of Oracle Database 11g Release 2. It will enable us to remove the last bit of downtime we have to plan.

Past releases of Oracle Database have enabled us to perform online operations, such as changing most initialization parameters, creating indexes, redefining and reorganizing virtually any structure online, and even performing database upgrades. The only time we really needed to shut down an instance—to cut off access to the database for any protracted length of time—was during an application upgrade. That was because we had to take time out to recompile our PL/SQL units, re-create our views, change our grants, and so on. All of those changes used to require that no one be executing our PL/SQL, using our views, and so on—but not anymore. With Oracle Database 11g Release 2, we now have the ability to create editions—new namespaces—in the database.

A new edition is created as a child of an existing edition and inherits from it all the “state” of its parent edition—all the PL/SQL code, views, synonyms, and so on. When it is first created, a child edition is a mirror image of the parent but a mirror image that can be altered—added to or modified. For example, you can create a new edition based on your current production code base and, in that new edition, issue CREATE OR REPLACE PROCEDURE P , overwriting the copy of procedure P in that new edition. The current edition, the one your end users see, has the old copy of procedure P, and your users are not affected by this new code. You can install the new copy of procedure P in the new edition and recompile any invalidated code in the new edition—all without any impact on the current edition.

When the change(s) you make in the new edition is deemed “correct and complete,” you can release the new edition for public use, and the new code will be in place. Rather than having to kick out the users who would execute procedure P in order to install the new copy of procedure P and validate any dependent code, we can perform all that work while the old code is in use. This makes introducing change into a production environment—which can be a long, involved, and offline process—something that is apparently instantaneous to the end users: the people who matter.

If you need one reason to consider upgrading to Oracle Database 11g Release 2, Edition-Based Redefinition would be it. I’ve only just touched on its capabilities in this short description, but I’ll be covering it in much more detail in the next column. (In the meantime, see Oracle Database Advanced Application Developer’s Guide 11g Release 2 , Chapter 19, “Edition-Based Redefinition” for more information.)

In my book Expert Oracle Database Architecture , I spent quite a few pages describing how to perform batch operations “in parallel,” using a do-it-yourself parallelism approach. The approach was to break up a table into ranges, using rowids (see www.bit.ly/jRysl for an overview) or primary key ranges (see “Splitting Up a Large Table”). Although the approach I described was rather straightforward, it was always also rather manual. You had to take my “technique” and tweak it for your specific circumstances.

Well, no more tweaking is needed. In Oracle Database 11g Release 2, we have a simple way to accomplish exactly what I tried to demonstrate in the past. The new DBMS_PARALLEL_EXECUTE package can split up a large table by rowid ranges, by key value, or by a user-defined method. The table is broken up logically, and the database processes each of the ranges in the background, using the scheduler, with error logging, retries, and more included.

What used to be a rather manual, sometimes tedious process is now easy and as straightforward as can be. It’s always nice to see something that used to be hard made easy.

See Oracle Database PL/SQL Packages and Types Reference 11g Release 2 , Chapter 98, “DBMS_PARALLEL_EXECUTE” for more information.

Analytic functions—first introduced in Oracle8i Database Release 1—received a couple of upgrades in Oracle Database 11g Release 2.

The first new capability that jumped out at me was the introduction of the LISTAGG analytic function. LISTAGG, in short, creates a delimited list of column values for aggregating—a function that has long been wished for.

Oracle9i Database Release 1 introduced user-defined aggregate functions, and the first user-defined aggregate I wrote, called STRAGG, created a delimited list within a group (an aggregate—see www.bit.ly/ozjv6). It was about 100 lines of code and required an object type and an aggregate mapping function—lots of stuff.

When Oracle Database 10g Release 1 came out, we could use the new SYS_CONNECT_BY_PATH function to accomplish the same capability in pure SQL without having to resort to lots of procedural code. It was “pure SQL,” but it was complex.

Now, in Oracle Database 11g Release 2, we can accomplish the “stringing up of a list” very easily:

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from emp
  6    group by deptno
  7    order by deptno
  8   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

Compare this with the approach in Oracle Database 11g Release 1 and earlier that used SYS_CONNECT_BY_PATH, as shown in Listing 1, and I’m sure you’ll agree that LISTAGG makes the complex simple.

Code Listing 1: Creating a list, aggregating with SYS_CONNECT_BY_PATH

SQL> select deptno,
  2         substr(
  3         max(sys_connect_by_path(ename, '; ')),
  4         3) enames
  5      from (
  6    select deptno,
  7        ename,
  8        row_number()
  9        over
 10        (partition by deptno
 11         order by ename) rn
 12     from emp
 13         )
 14    start with rn = 1
 15   connect by prior deptno = deptno
 16      and prior rn+1 = rn
 17    group by deptno
 18    order by deptno
 19   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

Another handy new analytic function in Oracle Database 11g Release 2 is NTH_VALUE. As its name implies, the function enables you to pick out the Nth entry from a window, as shown in Listing 2.

Code Listing 2: Using the new NTH_VALUE analytic function

SQL> break on deptno skip 1

SQL> select deptno,
  2         ename,
  3         row_number()
  4         over (partition by deptno
  5               order by ename) rn,
  6         first_value(ename)
  7         over (partition by deptno
  8               order by ename) "1st ename”,
  9         nth_value(ename,3)
 10         over (partition by deptno
 11               order by ename) "3rd ename”,
 12         last_value(ename)
 13         over (partition by deptno
 14               order by ename
 15               rows between current row
 16               and unbounded following) "last ename”
 17    from emp
 18   order by deptno, ename
 19  /

 DEPTNO  ENAME     RN    1st ename   3rd ename    last ename
-------  ------    ---   ---------   ---------    ----------
 10      CLARK     1     CLARK                    MILLER
         KING      2     CLARK                    MILLER
         MILLER    3     CLARK       MILLER       MILLER
 20      ADAMS     1     ADAMS                    SMITH
         FORD      2     ADAMS                    SMITH
         JONES     3     ADAMS       JONES        SMITH
         SCOTT     4     ADAMS       JONES        SMITH
         SMITH     5     ADAMS       JONES        SMITH
 30      ALLEN     1     ALLEN                    WARD
         BLAKE     2     ALLEN                    WARD
         JAMES     3     ALLEN       JAMES        WARD
         MARTIN    4     ALLEN       JAMES        WARD
         TURNER    5     ALLEN       JAMES        WARD
         WARD      6     ALLEN       JAMES        WARD
14 rows selected.

This is just a sample of the new analytics capabilities in Oracle Database 11g Release 2. Other new functions have been added, and some existing analytic functions have been enhanced with support for IGNORE NULLS. See Oracle Database Data Warehousing Guide 11 g Release 2, Chapter 21, “SQL for Analysis and Reporting” for more information.

Directory objects, a mapping from Oracle Database to the OS file system, has a new privilege: EXECUTE. (Before Oracle Database 11g Release 2, directory objects could be granted only READ and WRITE privileges, which was sufficient, because the only thing we did with directory objects was to read from or write to them.)

Oracle Database 11g Release 2 also introduces the ability to run a “preprocessor” on an OS file. You could, for example, specify as a preprocessor program a command-line decompression utility, so that you could directly query a compressed file in the file system without having to store it in an uncompressed format.

To perform this operation safely—to enable Oracle Database to invoke this command-line program safely—we need to grant EXECUTE on the directory containing the program we want to invoke. This allows us to control precisely which program will be executed by Oracle Database and avoid executing a “Trojan horse” program by accident.

Oracle Database has supported hierarchical queries since its inception some 30 years ago. Since the very first release, you could execute a query such as the following:

SQL> select level,
  2         lpad('*', 2*level, '*')||ename nm
  3      from emp
  4    start with mgr is null
  5    connect by prior empno = mgr
  6    order siblings by ename
  7    /

  LEVEL   NM
-------   -----------
  1       **KING
  2       ****BLAKE
  3       ******ALLEN
  3       ******JAMES
  3       ******MARTIN
  3       ******TURNER
  3       ******WARD
  2       ****CLARK
  3       ******MILLER
  2       ****JONES
  3       ******FORD
  4       ********SMITH
  3       ******SCOTT
  4       ********ADAMS
14 rows selected.

Oracle Database 11g Release 2 gives us another syntax for performing that same hierarchical query—and more. Listing 3 shows the new recursive subquery.

Code Listing 3: New recursive subquery

SQL> with emp_data(ename,empno,mgr,l)
  2    as
  3     (select ename, empno, mgr, 1 lvl from emp where mgr is null
  4      union all
  5      select emp.ename, emp.empno, emp.mgr, ed.l+1
  6        from emp, emp_data ed
  7       where emp.mgr = ed.empno
  8     )
  9    SEARCH DEPTH FIRST BY ename SET order_by
 10   select l,
 11         lpad('*' ,2*l, '*')||ename nm
 12     from emp_data
 13    order by order_by
 14   /

  L   NM
----  ---------------
  1   **KING
  2   ****BLAKE
  3   ******ALLEN
  3   ******JAMES
  3   ******MARTIN
  3   ******TURNER
  3   ******WARD
  2   ****CLARK
  3   ******MILLER
  2   ****JONES
  3   ******FORD
  4   ********SMITH
  3   ******SCOTT
  4   ********ADAMS
14 rows selected.

This new ANSI SQL-compliant syntax gives us the same capabilities as CONNECT BY queries did. The new syntax is part of the ANSI SQL implementation and therefore more “standard,” but it also gives us a neat way to generate data:

SQL> with data(r)
  2    as
  3      (select 1 r from dual
  4       union all
  5       select r+1 from data where r < 5
  6      )
  7    select r, sysdate+r
  8       from data;

  R   SYSDATE+R
----- ----------
  1   01-AUG-09
  2   02-AUG-09
  3   03-AUG-09
  4   04-AUG-09
  5   05-AUG-09

For more information on this new feature, see Oracle Database SQL Language Reference 11g Release 2, Chapter 19, “SQL Statements: SAVEPOINT to UPDATE.”

Oracle Database 11g Release 1 introduced a long-term flashback query capability, for going back months or years into the past (described in a previous Oracle Magazine article, “Managing History”). One of the limitations of the initial release of this capability was the inability to perform most data definition language (DDL) operations on the table(s) that permitted this long-term flashback capability. For example, dropping a column and truncating a table were not permitted with the flashback data archive feature.

In Oracle Database 11g Release 2, these and many more DDL operations are now supported, but the ability to flash back over time has not been compromised. This is important, because we never actually finish our applications—we are constantly changing them and the tables that support them.

Well, maybe you don’t have mail, but suppose you have a new file. A common requirement I’ve heard over the years has been, “When a new file appears in this directory, we need to be able to see it and then process it . . . and this should all be automatic.” Now, using the DBMS_SCHEDULER package in Oracle Database 11g Release 2, you can create a “file watcher”—a job that will watch for files to appear and then route them to the appropriate jobs for processing.

Sometimes it’s the little things that prompt change. With Oracle Database 11g Release 2, one of those little things is the data pump “legacy mode.”

We all have scripts, accumulated over the years, that perform various operations using the now deprecated EXP and IMP utilities. Although these utilities still exist in the latest database releases, we should be moving off of them, for performance and functionality reasons. Many of the new features of Oracle Database are not supported by the EXP and IMP tools, and the number of instances when they cannot work for us is increasing rapidly over time.

But you have all of those scripts that use the old-fashioned EXP and IMP command-line syntax, and you don’t have the time to change them all. So moving away from them is problematic.

Enter the data pump legacy mode. In Oracle Database 11g Release 2, Oracle has taught the data pump tools to speak the language of EXP and IMP. You can now start using the data pump utilities without having to rewrite every one of your EXP and IMP scripts—the mapping of the old command line to the new one is done for you.

The number of new capabilities in Oracle Database 11g Release 2 is large—too large to hit even all the ones I find most interesting in a single column. Over the next couple of issues, I’ll be exploring them in more detail. Next time I’ll take an in-depth look at Edition-Based Redefinition, discussed briefly above.