Performance Enhancements from 8i to 9i

Performance Enhancements

Index Monitoring

You can now monitor an index to determine if it’s being used and collect usage statistics. To turn on monitoring:

ALTER INDEX my_index MONITORING USAGE;

To turn off monitoring:

ALTER INDEX my_index NOMONITORING USAGE;

Query the dictionary table V$OBJECT_USAGE to view the index utilization data.

Skip Scan Index Access

Oracle can now use skip scan technology to scan composite indexes when a query does not include the prefix column. This applies to B-tree, cluster, and descending index scans. It does not apply to bitmap, domain, and function-based indexes, or to reverse-key indexes.

First Rows Optimization

9i introduces the ability to optimize data retrieval for the first N rows, where the only allowable values for N are: 1, 10, 100 or 1,000. You can set the optimizer mode for a session like this:

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100 ;

Or set the OPTIMIZER_GOAL, which overrides the OPTIMIZER_MODE:

ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_1000 ;

Remember that an optimizer hint on a particular SQL statement overrides either of these settings for that one statement.

System Statistics

Use these procedures in the DBMS_STATS package to gather system statistics (for CPU and I/O utilization). Be sure to gather statistics during a period in which the workload is typical.

Procedure:	Use:
GATHER_SYSTEM_STATS	Gathers system performance statistics
SET_SYSTEM_STATS	Sets system stats in the table specified by the STATTAB parameter
GET_SYSTEM_STATS	Gets system stats from the table specified by the STATTAB parameter

9i includes these values you encode on the METHOD_OPT parameter of various DBMS_STATS procedures:

Parameter:	Use:
SKEWONLY	Creates histogram based on data distribution
REPEAT	Creates histogram with the same number of buckets
AUTO	Creates histogram based on data distribution and how the column is accessed

The new 9i columns in the PLAN_TABLE to track system statistics include: CPU_COST, IO_COST, and TEMP_SPACE.

Cursor Sharing

Traditionally Oracle only shares a cursor in the Shared Pool if the statements are completely identical (even including spacing). This is the default cursor sharing setting of EXACT. 9i now allows you to set this parameter to SIMILAR, or even to force cursor sharing with FORCE:

ALTER SESSION SET cursor_sharing = SIMILAR ;