One is All You Need

I have only abbreviated notes from this second Oracle partner 11g review session and several topics were in areas where I have limited knowledge. Again, these are just notes and I have not verified this functionality. There may be omissions or errors:

Virtual Columns

Virtual columns are defined using a function, but the data (like a view) is not actually stored physically. Virtual columns need to be based solely upon base columns (non-virtual):

CREATE TABLE foo
(
 bar NUMBER,
 v1 GENERATED ALWAYS AS (bar+10) VIRTUAL,
 v2 GENERATED ALWAYS AS (
                         CASE WHEN bar < 10 THEN 1
                              WHEN bar >=10 AND bar <100 THEN 2
                              ELSE 3
                         END
                        ) VIRTUAL
);

Virtual columns can exist in partition keys, have indexes (these become function-based indexes) and be included when statistics are gathered. The presenter at our session also indicated that Oracle primarily included virtual columns to aid in partitioning strategies rather than to migrate application logic into the database. It was also indicated that the overhead of using virtual columns was extremely low, even with indexes.

It is important to note that virtual columns must use deterministic functions only; the function must return the same value each time that it is executed with the same input parameters. For example, a virtual column cannot be defined to return the SYSDATE.

New Partitioning Options

Table partitioning continues to be improved with the following functionality:

• Virtual columns can be used in the partitioning key.

• Full automation of equi-sized range partitions. Partitions and Local indexes created automatically as needed.

• Child tables inherit partitioning strategy of parent through PK/FK (REF partitioning). This eliminates the need to store the partitioning key for the child tables and more rows per block can be stored.

• Composite partition options include list-range (ex. geog-time), range-range (ex. ship date - order date), list-hash (ex. geog - hash) and list-list (ex. geog-product).

Invisible Indexes

This slick little feature allows indexes to be created on tables that are invisible to the CBO. To use an invisible index, a query must be directed to do so using a hint. This allows the addition of new indexes into a schema without affecting the execution plans of existing queries. This powerful feature will be a great new query and application tuning tool.

SecureFiles: A new generation of LOBs

These are the next generation of LOBs. They promise significant performance increases, more capabilities, transparent deduplication, and since they are a super-set of LOB interface, easy migration from existing LOBs is expected.

SecureFiles are a major re-architecture of how the DB handles unstructured data rather than an upgrade to existing LOB technology. The following areas are entirely new:

• Disk format
• Network protocol
• Versioning and sharing
• Caching/locking
• Redo/undo
• Space management
• Cluster consistency algorithms

And the following functionality is included with SecureFiles:
• Transactional, read consistent and flashback enabled
• Advanced search capabilities
• XML indexing
• Fine grained auditing
• Label security

The presenter was very excited about SecureFiles and ended with the following statement: “SecureFile capabilities go far beyond any other database or file system.”

XML-DB Overview

• Large node handling
• Stream-based replication support for LOB-based XML storage
• Trigger support in the XML-DB repository
• XML Index
• “Binary” XML – a single format for on-disk, in-memory and on-the-wire XML transmission
• Reduced CPU and memory overhead
• Asynchronous operations possible

PL/SQL Enhancements

An exciting feature is the ability to automatically expose PL/SQL Procedures as web services. Using a C-based engine, the secure PL/SQL web services are implemented with no coding! Wow, that is pretty neat. I unfortunately saw no examples of this in the presentation.

Program dependencies are tracked at the column level so that changes to underlying data structures will not invalidate PL/SQL procedures unless the specific columns used in the procedure are altered.

Real Native Compilation - Through 10.2, PL/SQL compiled to a native dll was significantly faster than PL/SQL compiled by the PVM. Oracle translated PL/SQL into C code. In 11g, Oracle translates PL/SQL source DIRECTLY to the dll for the current hardware (no C compiler necessary!). Oracle does the linking and loading. The final result is PL/SQL that is twice as fast as C native. In actual tests, Oracle realized PL/SQL runtime improvements 2.5 – 20X! Wow!

The old “SELECT a.nextval into v_id” syntax is no longer necessary. Now you can do a “v_id := a.nextval”.

PL/Scope in SQL Developer 2.0 allows the developer to follow references around in code.

The presenter briefly alluded to the PL/SQL Hierarchical Profiler which is engaged with dbms_start_profiling(). Apparently, this allows for dynamic execution profilers that treat SQL and PL/SQl separately. More on this after I get a chance to use it.

Java Enhancements

These were covered pretty quickly:

• Prefetch in First Round-trip (JDBC – thin).
• Native AQ protocol. – AQ/OJMS Operations 40-300% faster.
• JSBD 4.0 support.
• Java VM upgrade disconnected from DB upgrades.
• New JIT compiler compiles Java in the DB natively and transparently (on the fly) without need for a C compiler leading to an “order of magnitude” speed increase for pure Java out of the box!

Database Resident Connection Pooling

This functionality was specifically designed for PHP and allows many external sessions to connect to a single database session (as long as they use the same user ID). The presenters did not know if this functionality would work for non-PHP applications.

SQL Tuning

A fully automatic tuning SQL Tuning process now examines queries and evaluates/implements new execution plans:
• Runs under Autotask framework.
• Identifies, ranks and tunes candidate SQL with parallel queries, DML/DDL, recursive and ad-hocs excluded.
• Leverages SQL Tuning Advisor.
• Only implements significantly improved plans (3x faster).
• Time budgeted to avoid run-away plans.

This part of the session included a number of questions and concerns about the database automatically implementing its own changes to query execution plans. The presentation team seemed very confident in its capabilities. Also included was a discussion of how to migrate applications from previous versions of the database without fear of dramatic changes to the plans by using this functionality. But who knows?

Real Application Testing

Now this really cool feature, called Database Replay facilitates testing by:
• Capturing workload in production
• Replaying workload in test
• Analyzing and reporting

Its designed to provide stress testing IN DEV based upon real transactions that occur in the production environment. At least an hour was devoted to this topic, but this is not the proper forum to describe the functionality in detail.

And so there you have it. A little taste of what’s coming with Oracle 11g! I’ll be providing more details on these features as I use them in the upcoming months. Happy coding!

Greg Pike

I just got back from the beginning of a Oracle Partner 1.5 day review of 11g new features and thought I’d share some very preliminary information. I should be able to post the actual power point presentations in a couple of days, but here’s some quick notes (in no particular order). I will try to post Day 2 tomorrow…

I was late for this session, but the “Flashback Archive” will allow almost unlimited database flashback functionality by allowing so-called “flashback tablespaces”.

Data Guard’s standby database now allows concurrent, transaction-consistent real-time query capabilities. Also, all data types are now supported. You can even add additional schemas to the standby DB even if not found in the master DB, but these will also be read-only. “Snapshot Standby” allows the use of the standby DB for testing purposes and then returns the standby to a production copy with the commands:

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


DBMS_FLASHBACK.TRANSACTION_BACKOUT will automatically back out a transaction (and all of its dependent transactions). Very nice!

Columns can be added to a table with a default value included. Saves some time and effort!

“Invisible” indexes can be added to tables without affecting existing execution plans since they are invisible to the optimizer unless specifically identified by a hint. This is a powerful feature for increasing the response time of problem queries without impacting others.

RAC claims a 70% improvement over the internal, read-only benchmark.

Some database sizing numbers:

11g Grid control will not be available for “a while.”

11g includes several new caches and I will give more explanations soon:

Optimizer Changes: Guaranteed Plan Stability and SQL Plan Baseline: The database will now evaluate alternate execution plans and impose only those plans that are verified as an improvement. This feature is great for upgrading from previous versions of the database and I will provide more detail later.

AUTO_SAMPLE_SIZE - “Groundbreaking” improvement on stats gathering.

Passwords are finally case sensitive and all Oracle clients are changed to support this requirement.

For years, developers have struggled with transforming long and narrow results sets into short and wide result sets. Enter the new SQL clauses PIVOT and UNPIVOT. More on this after I give it a try.

I realize this information is a a little raw and unstructured, its just my notes. More to come!

Greg

Greg Pike