Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Performance Enhancements In Oracle9i

A number of performance enhancements have been made in Oracle9i including:

New FIRST_ROWS_n Optimization Mode

Oracle9i has added a new optimization mode which uses the cost based optimizer to optimize selection of the first 'n' rows where 'n' can equal 1, 10, 100, or 1000. This parameter can be set for the whole instance in the init.ora file, at the session level using the ALTER SESSION command or for individual statements as an optimizer hint:

OPTIMIZER_MODE = FIRST_ROWS_100

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;

SELECT /*+ FIRST_ROWS(1000) */ empno, ename, sal, job
FROM   emp
WHERE  deptno = 20;

Cursor Sharing

Oracle9i allows existing applications to improve SQL reuse by setting the CURSOR_SHARING initialization parameter:

In an ideal world the application should be written to encourage cursor sharing, but existing applications may gain reduced memory usage, faster parses, and reduced latch contention when using this option.

Index Monitoring

The usage of indexes can be monitored over a period of time.

ALTER INDEX <index-name> MONITORING USAGE;

ALTER INDEX <index-name> NOMONITORING USAGE;

On issuing the MONITORING statement Oracle clears the existing usage statistics for the index and starts to gather new usage statistics. This will continue until the NOMONITORING statement is issued. This information can be used to determine if an index is being used or not.

The statistics can be viewed using the [ALL|USER|DBA]_INDEXES views. The V$OBJECT_USAGE view can be used to see when the statistics collection started and ended.

Care should be taken before dropping an existing index. Even though the statistics may say it is not used, it may support another feature, such as foreign key indexes preventing share locks on child tables.

Index Skip Scanning

In previous releases a composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement. In Oracle9i this restriction is removed because the optimizer can perform skip scans to retrieve rowids for values that do not use the prefix.

Rather than restricting the search path using a predicate from the statement, Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column. Essentially, the index has been searched from the second level down. The optimizer uses statistics to decide if a skip scan would be more efficient than a full table scan.

This approach is advantageous because:

System Statistics

Oracle9i allows the Cost Based Optimizer (CBO) to take advantage I/O and CPU utilization statistics whilst deciding on execution plans. This information is gathered using the DBMS_STATS package. Since most systems have multiple modes of operation, careful consideration must be given to the times when system stats are gathered. In the example below the stats are gathered during the day when the system runs in OLTP mode and at night when batch operations are running.

-- Create the table to hold the stats
DBMS_STATS.create_stat_table (
  ownname => 'TSH',
  stattab => 'mystats',
  tblspace => 'tools');

-- Run during the day gather stats for
-- when the system is in OLTP mode
BEGIN
  DBMS_STATS.gather_system_stats(
    interval => 720,
    stattab  => 'mystats',
    statid   => 'OLTP');
END;
/

-- Start at night to gather stats during
-- batch operations
BEGIN
  DBMS_STATS.gather_system_stats(
    interval => 720,
    stattab  => 'mystats',
    statid   => 'BATCH');
END;
/

It is important to keep these stats up to date since system usage may vary over time. Once gathered the system stats can be switched depending on the mode of operation.

DBMS_STATS.import_system_stats('mystats','OLTP');
DBMS_STATS.import_system_stats('mystats','BATCH');

These actions could be automated using DBMS_JOB to flip the mode at specific times.

Database Statistics

The functionality of the DBMS_STATS package has been extended in Oracle9i to include:

Cached Execution Plans

The V$SQL_PLAN view can be used to view the execution plans of recent SQL statements. It contains similar information to an EXPLAIN PLAN, except the EXPLAIN PLAN is a theoretical execution plan, whereas this view contains the actual execution plans used by the server.

Hope this helps. Regards Tim...

Back to the Top.