Search This Blog

Sunday, August 26, 2007

Oracle Database 11g Release 1 (11.1) New Features

Simplified and improved automatic memory management

You can now set a single initialization parameter (MEMORY_TARGET) to indicate the total amount of memory that is to be allocated to the database (the SGA and instance PGA). The system then automatically and dynamically tunes all SGA and PGA components for optimal performance. You can still designate minimum sizes individually for the SGA and instance PGA.

New fault diagnosability infrastructure to prevent, detect, diagnose, and help resolve critical database errors

The goals of the fault diagnosability infrastructure are preventing and detecting problems (critical errors) proactively, limiting damage and interruptions after a problem is detected, reducing problem diagnostic time, reducing problem resolution time, and simplifying customer interaction with Oracle Support. The framework includes technologies such as health checks that run when a critical error occurs; proactive in-memory tracing for many database components to permit first-failure data capture; an Incident Packaging Service that packages all diagnostic data for a problem into a zip file for transmission to Oracle Support; and Enterprise Manager Support Workbench, which provides a graphical environment for investigating, reporting, and resolving problems. Also included is integration with the new SQL Repair Advisor, for diagnosing and repairing SQL-related problems, the SQL Test Case Builder, which gathers all required schema and environment information to enable a SQL problem to be reproduced on another Oracle database, and the Data Recovery Advisor, which helps diagnose, evaluate the impact of, and repair data corruptions and other data failures.

Invisible Indexes

Making an index invisible is an alternative to making it unusable or dropping it if you want to test whether overall performance will improve by removing an index. An invisible index is by default ignored by the optimizer, but unlike an unusable index, is maintained during DML statements. You have the option to change an initialization parameter at the system or session level to cause the optimizer to use invisible indexes.

Virtual columns

Tables can now include virtual columns. The value of a virtual column in a row is derived by evaluating an expression. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions. In some cases, a virtual column eliminates the need to create a separate view. You can create an index on a virtual column, and you can use a virtual column as a partition or subpartition key.

Enhanced security for password-based authentication by enabling use of mixed case in passwords.

Beginning with Oracle Database 11g Release 1, passwords in the password file are case sensitive unless you include the IGNORECASE = Y command-line argument.

Database resident connection pooling

Database resident connection pooling (DRCP) provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers, which are the equivalent of a server foreground process and a database session combined. DRCP enables sharing of database connections across middle-tier processes on the same middle-tier host and across middle-tier hosts. This results in significant reduction in database resources needed to support a large number of client connections, thereby boosting the scalability of both middle-tier and database tiers.

Tablespace-level encryption

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications. When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs, table and index partitions, and so on.

Finer-grained schema object dependencies for increased availability

Invalidation of dependent schema objects in response to changes in the objects they depend upon is greatly reduced in Oracle Database 11g, increasing application availability during maintenance, upgrades, and online table redefinition. Between a referenced object and each of its dependent objects, the database tracks the elements of the referenced object that are involved in the dependency. For example, if a single-table view selects only a subset of columns in a table, only those columns are involved in the dependency. For each dependent of an object, if a change is made to the definition of any element involved in the dependency (including dropping the element), the dependent object is invalidated. Conversely, if changes are made only to definitions of elements that are not involved in the dependency, the dependent object remains valid.

Table compression now supported in OLTP environments

Compressed tables now support the following operations:

DML statements

Add and drop column

Result cache in the system global area

Results of queries and query fragments can be cached in memory in the result cache. The database can then use cached results to answer future executions of these queries and query fragments. Because retrieving results from the result cache is faster than rerunning a query, frequently run queries experience a significant performance improvement when their results are cached.

The result cache occupies memory in the shared pool.

Default automatic undo management mode

A newly installed 11g instance defaults to automatic undo management mode, and if the database is created with Database Configuration Assistant, an undo tablespace is automatically created. A null value for the UNDO_MANAGEMENT initialization parameter now defaults to automatic undo management.

Enhanced online index creation and rebuild

Online index creation and rebuild prior to this release required a DML-blocking lock at the beginning and at the end of the rebuild for a short period of time. This lock could delay other DML statements and therefore cause a performance spike. This lock is no longer required, making these online index operations fully transparent.

Ability to online redefine tables that have materialized view logs

Tables with materialized view logs can now be redefined online. Materialized view logs are now one of the dependent objects that can be copied to the interim table with the DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS package procedure.

Read-only tables

You can set any table to read-only mode with the ALTER TABLE statement. This provides an alternative to placing a table's containing tablespace in read-only mode.

Transportable tablespace enhancements

Data Pump now supports the transportable tablespace function for tablespaces with XMLType tables and with schema objects with XMLTypes.


For certain types of tables, when adding a column that has both a NOT NULL constraint and a default value, the database can optimize the resource usage and storage requirements for the operation. It does so by storing the default value for the new column as table metadata, avoiding the need to store the value in all existing records.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:

Add a NOT NULL column with a default value

Add a nullable column without a default value

Add a virtual column

Enhancements to initialization parameter management

The following enhancements are made to the handling of initialization parameters:

The server parameter file (SPFILE) has a new format for compliance with Oracle's HARD initiative. This initiative helps to prevent writing corrupted data to disk, and is implemented at the software and storage hardware levels.

New commands enable you to create a text initialization parameter file (PFILE) or server parameter file (SPFILE) from the current values of initialization parameters in memory.

Upon startup, values of initialization parameters are written to the alert log in such a way as to make it easy to copy and paste them to create a new PFILE.

The name and path of the PFILE or SPFILE used to start the instance is written to the alert log.

Oracle Database automatically resilvers a mirrored copy of the SPFILE when needed.

Data Definition Language (DDL) commands can wait for locks

You can now set a single initialization parameter, DDL_LOCK_TIMEOUT, to specify how long a DDL command waits for the exclusive locks that it requires on internal structures before it fails.

No comments: