Oracle Database 9i(9.2) New Features

Oracle 9i(9.2) New Features

Automatic Server Parameter File Backup and Configuration

Recovery Manager can now automatically back up and restore the server parameter File (SPFILE) configuration file, simplifying the backup and restoration processes of the system.

Performance Tuning Intelligent Advisories

The current release of Oracle9i provides a number of built-in intelligent advisories for performance tuning. These advisories are set to go "out-of-the-box", and allow the administrator to simulate a variety of hypothetical scenarios. These advisories use minimal resources and are available though the standard SQL interface.

These advisories show Shared Pool usage to improve parse time and to minimize CPU usage. They also show SQL execution memory to shorten SQL execution time and to minimize unnecessary CPU and I/O usage.
An advisory for the Mean-Time-To-Recover setting makes it possible for the administrator to set time requirements to recover from a system failure without jeopardizing run-time performance.
An advisory for the PGA Aggregate Target makes it possible for the server to control dynamically the amount of PGA memory allotted to SQL work areas according to the PGA_AGGREGATE_TARGET limit that is set by the DBA


Actual Operation-Level Query Statistics

Performance tuning in the current release of Oracle9i is simplified with intelligent tuning features that provide actual operation-level query execution statistics rather than estimates. This new information helps database administrators identify what tables, partitions, and indexes are most heavily accessed.

Dynamic Sampling of Optimizer Statistics

At compile time, this feature, if enabled, will dynamically gather statistics if the existing statistics are incomplete or known to be inaccurate. With default settings, this feature addresses missing optimizer statistics, providing for a possible source of poor optimization. With optional settings, this feature addresses additional sources of inaccurate statistics. Dynamic sampling of optimizer statistics may improve performance by improving the quality of the statistics used by the query optimizer.

Locally Managed SYSTEM Tablespace

The SYSTEM tablespace can be locally managed to simplify the database administrator's tasks.

Specifying FORCE LOGGING Mode

The FORCE LOGGING clause of the CREATE DATABASE, CREATE CONTROLFILE, and CREATE TABLESPACE statement enables you to force redo log records to be written even when NOLOGGING has been specified in a DDL statement.

LogMiner Enhancements

LONG and LOB Datatype Support for Redo Logs
LONG and LOB datatypes are supported for redo logs generated on a release 9.2 or later Oracle database.

Supplemental Logging Turned Off by Default

Supplemental logging is turned off by default. This is a change from release 1 (9.0.1), in which minimal supplemental logging was turned on by default. In release 9.2, the level of supplemental logging desired must be specified.

New Formatting Options

Two new options have been added that affect the formatting of returned data. The DBMS_LOGMNR.NO_SQL_DELIMITER option suppresses the semi-colon at the end of SQL_REDO and SQL_UNDO statements. The DBMS_LOGMNR.PRINT_PRETTY_SQL option formats the reconstructed SQL statements so that they are easier to read.

New Option, DBMS_LOGMNR.CONTINUOUS_MINE, in LogMiner

A new option, DBMS_LOGMNR.CONTINUOUS_MINE, directs LogMiner to automatically add and mine redo log files that are archived after the LogMiner session has started.

DBMS_LOGMNR.NO_DICT_RESET_ONSELECT Option Unnecessary

Use of the DBMS_LOGMNR.NO_DICT_RESET_ONSELECT option is no longer necessary. When data definition language (DDL) tracking is enabled, LogMiner stores old metadata definitions so that a second select operation has all the needed metadata versions.


New Procedure, DBMS_LOGMNR_D.SET_TABLESPACE, in LogMiner

A new procedure, DBMS_LOGMNR_D.SET_TABLESPACE,recreates all LogMiner tables in a tablespace other than the default tablespace, SYSTEM.


Minimizing the Production Impact of Upgrading

The Oracle9i Database release 2 introduces new features to lessen the time spent upgrading applications and database systems. These new features include the ability to rename CONSTRAINTS and COLUMNS as well as a significant reduction in the recompilation of PL/SQL packages and procedures. The database system also provides for fast loading of wrapped source code, automatically determines to do nothing upon loading an unchanged package, view or synonym, and removes double invalidation and parallel compilation in dependency code.

Domain Indexes Enhancement

It is now possible to create and rebuild domain indexes and local domain index partitions in parallel.

Segment-level Statistics Collection

With the capacity to collect of segment-level statistics in Oracle9i release 2, users can specify that relevant statistics be collected on the segment level to pinpoint hot spots and performance bottlenecks in the system. You can query these statistics by using the V$SEGMENT_STATISTICS to do a SELECT * FROM V$SEGMENT_STATISTICS. You can also query V$SEGSTAT_NAME for statistics properties and V$SETSTAT for additional efficient access to statistics.