Performance Enhancements In Oracle9i
A number of performance enhancements have been made in Oracle9i including:
- New FIRST_ROWS_n Optimization Mode
- Cursor Sharing
- Index Monitoring
- Index Skip Scanning
- System Statistics
- Database Statistics
- Cached Execution Plans
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;
Oracle9i allows existing applications to improve SQL reuse by setting the
EXACT- (Default) Only statements with an exact text match will share the same SQL area.
SIMILAR- Oracle will substitute bind variable for all literals, thereby increasing the chances of a text match. Oracle will force similar statements to share the SQL area without deteriorating execution plans.
FORCE- The same as SIMILAR except that execution plans may deteriorate. This option should only be used if the risk of suboptimal plans is outweighed by the increase in cursor sharing.
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.
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:
- It reduces the number of indexes needed to support a range of queries. This increases performance by reducing index maintenance and decreases wasted space associated with multiple indexes.
- The prefix column should be the most discriminating and the most widely used in queries. These two conditions do not always go hand in hand which makes the decision difficult. In these situations skip scanning reduces the impact of makeing the "wrong" decision.
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
-- 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.
These actions could be automated using
DBMS_JOB to flip the mode at specific times.
The functionality of the
DBMS_STATS package has been extended in Oracle9i to include:
- Storage of existing stats in a user defined stats table before being overwritten with the new data. This was possible in Oracle8i, but it can be achieved using a single statement in Oracle9i.
The 'staleness' of statistics for objects, schemas or the entire database can be monitored. This information
can be used to decide what stats need to be gathered. When gathering stats you can decide whether to gather
stats for object with no stats, stale stats or regardless of the state of the current stats. Oracle considers
the stats to be stale when approximately 10% of rows for an object have been modified. Monitoring can be switched
on using the
DBMS_STATSpackage or by using the
Cached Execution Plans
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...