A number of performance enhancements have been made in Oracle 9i including:
Oracle 9i 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;
This section has been expanded into a separate article here.
Oracle 9i allows existing applications to improve SQL reuse by setting the CURSOR_SHARING
initialization parameter:
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.
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 Oracle 9i 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:
Oracle 9i 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.
The functionality of the DBMS_STATS
package has been extended in Oracle 9i to include:
DBMS_STATS
package or by using the CREATE
or
ALTER
statements.
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 normal view: https://oracle-base.com/articles/9i/performance-enhancements-9i