Database Reference
In-Depth Information
Improvement
Improving performance is a vast subject. If we ignore the use of more powerful hardware,
which might not even solve the problem, the following procedures may result in better
performance:
Parameter changes (e.g.,
DB_CACHE_SIZE
,
PARALLEL_EXECUTION_MESSAGE_SIZE
)
Adding an index to avoid full table scans
Dropping unnecessary indexes
Partitioning of tables and/or indexes
Materialized views
Use of bind variables instead of literals in SQL statements
Correction of bind data type mismatches
Calculation of more accurate optimizer statistics with
DBMS_STATS
Optimizer Dynamic Sampling
Use of system statistics, given that the cost-based SQL optimizer chooses better execution
plans than without them (
DBMS_STATS
)
Use of cached sequences instead of counters implemented with tables
Adding hints to SQL statements (as a last resort to improve execution plans)
Stored outlines
Supplying hidden hints with stored outlines (see Metalink note 92202.1)
SQL profiles (Oracle10
g
and subsequent releases)
SQL plan management (requires Oracle11
g
)
Use of array inserts or bulk load programming interfaces
Reduction of network round-trips (e.g., with
INSERT
RETURNING
)
PL/SQL native compilation
Changes in application coding to reduce contention, parse overhead, polling, and so forth
Performance improvements should be documented with additional measurements. You
should not rely on a single measurement, but instead take several measurements and calculate
the average.
Extrapolation
By the time a substantial performance improvement has been achieved with the test case, it is
important to extrapolate the effect of the improvement on the original application. This is to
