Posted by: Shayna Garlick
Oracle 11g R2, Oracle database administration
While analysts have noted that only about 10% to 15% of Oracle database users have upgraded to Release 1 of Oracle 11g, which was first shipped two years ago, Oracle OpenWorld attendees seemed very interested in learning about 11g Release 2.
In a packed session room at the end of the day on Tuesday, Oracle Architect Tom Kyte spoke to the crowd about 11 features he thinks are important in Oracle 11g R2. He joked that while they weren’t being presented in any order, he did “save the best one for last.”
Here’s a quick look at Kyte’s top 11 things about 11g R2 — some you may have heard of already and others are probably new:
1) Do it yourself Parallelism: Kyte says that in 11g R2 there is a ‘do- it- yourself’ parallel package that performs processes that used to be done manually. DBMS_PARALLEL_EXECUTE now automates the process of splitting up large tables using rowid or primary key ranges.
2) Analytics are the coolest thing to happen to SQL since the keyword SELECT: In Oracle 9i R2, Kyte introduced the STRAGG function, which helps perform string concatenation with user defined aggregates. But Kyte says 11g R2 makes this easier with the long-awaited LISTAGG analytic function.
3) EXECUTE on a directory: Directory objects can now be granted EXECUTE privileges in addition to READ and WRITE. EXECUTE can be granted to run a PREPROCESSOR program on an OS file.
4) Recursive subquery factoring: This is a new ANSI SQL statement that can be used as a replacement for CONNECT BY and is easier to understand, Kyte said, “unless you have been using CONNECT by for 22 years, in that case it’s confusing.”
5) Improved Time Travel: The usability of the flashback data archive, which can be found in previous versions of the Oracle database, is greatly enhanced in 11g R2, accommodating complex DDL changes such as table splits.
6) You’ve got mail: When a new file arrives in the system, a feature called file watchers creates an event that includes complex metadata about the incoming file. After a file watcher is created, you can create event-based jobs or schedules that reference it.
7) Deferred segment creation: When creating a table, this new 11g R2 feature allows you to put off the initial extent allocation until the first row is put into a segment. Deferred segment creation is useful for many third party apps that often create thousands of tables but only end up using 100 of them.
8.) Flash Cache: The flash cache works not only on Oracle databases, but on Enterprise Linux, Sparc Solaris or Solaris s86. It’s a transparent extension of the database buffer cache that uses solid state disk technology, allowing you to choose how buffers for a schema object are cached. Flash allows you to use fewer drives, enabling I/O throughput and completing large jobs faster.
9) Parallel Improved: A data warehouse with over 1,000 users can easily become overwhelmed as processes continue to pile on. In 11g R2, the DBA can set up the maximum number of parallel operations that can be happening at any time. When that number is reached, the processes go into a queue until the resources become available to perform them, giving all processes better response times.
10 and 11) Edition-based redefinition: “This thing is so cool, it’s both (number 10 and 11),” Kyte said about the “killer feature” of 11g R2 that comes built into the database. Edition-based redefinition allows you to upgrade an application while it’s in use, making data changes safely by writing only to new columns and tables not being used in an old edition.
At another OpenWorld session this week, which focused on best practices for Oracle backup and recovery, Oracle’s Timothy Chien acknowledged that what everyone was really waiting for was to learn about the new features in 11g R2. When talking about the new database version, Chien highlighted RMAN features such as improved automatic block repair, which allows corrupt clocks on the primary database to be automatically repaired from the physical standby database, as they are detected. Chien also discussed how in 11g R2, there is an Advanced Compression Option which offers multiple RMAN backup compression levels (high, medium and low) and you can choose your compression levels and backup throughput.
What do you think is the best new feature in Oracle 11g R2?