Index Monitoring

alter index IND1 monitoring usage;

Track whether or not the index has been used with view V$OBJECT_USAGE.
NB. Issuing this command will invalidate every SQL statement in the library cache which references the index.

Skip scan index access

This new feature is designed to speed up index range scans. Especially beneficial if the leading column has a low cardinality. It takes advantage of how B*Tree indexes are organized. By examining the contents of branch blocks and by considering the values in non-leading keys, Oracle can immediately eliminate any leaf blocks which cannot possibly contain the data searched for. This way, less blocks need to be scanned thereby reducing IO thereby improving performance.

This new feature makes it possible to use non-leading keys of concatenated indexes (previously you had to use leading key values in the search). Consequently, this feature is likely to render secondary indexes for referencing non-primary column data superfluous to requirements. They can be dropped (after being closely monitored with the new monitoring function) making a further positive contribution to performance.

This new algorithm is implemented by default.

CURSOR_SHARING

This init.ora parameter can now be set dynamically at both system and session level. Can now be set to similar as well as the existing force and exact modes:

exact    -  Still the default. Do not attempt to share cursors at all. Use literal value always.
force    - Replace literal values with bind variables without checking for the existence of histograms or skewed data 
similar    - Use literal value only if it would make a difference to the execution plan to do so. For example, where there are histograms present or highly skewed data

Cached Execution Plans

A new dictionary view V$SQL_PLAN acts as kind of a real-time, global plan table for all SQL statements in the library cache. This is one of the most underrated new features in my opinion.

FIRST_ROWS

Four new modes to replace FIRST_ROWS when setting optimizer_mode:

FIRST_ROWS_1
FIRST_ROWS_10
FIRST_ROWS_100
FIRST_ROWS_1000

The FIRST_ROWS hint can take any value for the number of rows:

select /*+ first_rows(50) */   *  from emp;

DBMS_STATS

DBMS_STATS.GATHER_INDEX_STATS (
DBMS_STATS.GATHER_TABLE_STATS (
DBMS_STATS.GATHER_SCHEMA_STATS (
DBMS_STATS.GATHER_DATABASE_STATS (

The METHOD_OPT argument now supports new method options when specifying 'FOR ALL COLUMNS SIZE ...'

System Statistics

DBMS_STATS.GATHER_SYSTEM_STATS (

The 9i cost based optimizer now has the ability to take into account the system workload profile at a particular time of day when calculating an execution plan. You can sample and store a workload profile directly into the data dictionary or sample and store several workload profiles for different times of the day in a local table and switch them in and out of the data dictionary as necessary. For example, you may want to create 'nightlybatch' and 'dailyoltp' profiles if the workload profile is different during those times.

exec dbms_stats.create_stat_table ('SCOTT','STATTAB','USERS');
exec dbms_stats.import_system_stats('STATTAB','dailyoltp','SCOTT');