|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 9i
OS Details :
Sun Solaris9
As
part of upgrading to
9i, numerous query tuning issues can come up. Listed below are some of
the things that can be done to have your queries performing better
again.
(1) Make sure that at the least,
9.2.0.6 patch for 9i is installed. This patch fixes numerous optimizer
based issues including:
- Incorrect cardinality estimate
with not equals and NULL values
- Incorrect cost estimation with
bitmap index, range partition and nested loop
- Unnecessary partition scanning
depending on predicate order
- Suboptimal plan possible with
inline view and binds (or replaced literals)
- Poor cardinality estimate for
"IN" transformation using join with duplicate elimination
- Bad plan choice due to poor
selectivity for "col=:bind" or "col=expression" for single
value column
- Index skip scan is sometimes
estimated at very high cost
- Suboptimal plan possible for
FIRST_ROWS for SQL with ORDER BY and indexes
- CBO can waste CPU trying to
optimize INSERT INTO .. VALUES statements
- CBO may choose a more expensive
HASH JOIN plan with no partition pruning
- Cartesian join can occur due to
not unnesting a subquery
- CBO suboptimal plan for
multiple 'or' conditions
- In FULL OUTER JOIN a NESTED
LOOPS ANTI is used when unique index exists
- Sub-optimal execution plan for
range partitioned table with INLIST
(2) If the value of
OPTIMIZER_FEATURES_ENABLE parameter is set to 9.2.0, it
enables the following 3 parameters:
_UNNEST_SUBQUERY
_COMPLEX_VIEW_MERGING
_B_TREE_BITMAP_PLANS
Enabling the above 3 parameters
can cause slowdowns. The slowdowns by setting the
optimizer_features_enable parameter to 9.2.0 can be nullified by doing
a few things as described in (3) below.
(3) Set the value for _B_TREE_BITMAP_PLANS
parameter to FALSE. This will eliminate any bitmap plans that
optimizer comes up with for B-Tree indexes even if there are no bitmap
indexes.
Also, set the following event to
disable Cartesian joins with complex views:
event = "38043 trace name
context forever, level 1"
(4) Set the appropriate value for
optimizer_index_cost_adj parameter. The default for this
parameter is 100 percent, at which the optimizer evaluates index
access paths at the regular cost. Any other value makes the optimizer
evaluate the access path at that percentage of the regular cost. For
example, a setting of 50 makes the index access path look half as
expensive as normal. Set different values for this parameter in
your test environment and come up with the value that suits the SQL
statements that are submitted to your database
(5) Set the appropriate value for
optimizer_index_caching parameter. This parameter lets you
adjust the behavior of cost-based optimization to favor nested loops
joins and IN-list iterators. You can modify the optimizer's
assumptions about index caching for nested loops joins and IN-list
iterators by setting this parameter to a value between 0 and 100 to
indicate the percentage of the index blocks the optimizer should
assume are in the cache.
(6) Make sure system stats are
collected within the database at different machine load times. System
statistics can be collected using this method:
exec
dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>120,
stattab=>'<table_name>',statid=>'stat_batch')
Collect system stats at different
points during the day and night using different statid values,
whenever different types of operations (OLTP, batch or DSS) occur
against the database. Once the system stats have been collected, they
can be imported in to the dictionary using
dbms_stats.import_system_stats package.
(7) Make sure monitoring is
enabled on all the table so that you collect only stale statistics for
the schema. Frequent collection of statistics can cause execution plan
instability.
(8) And finally, make sure the
correct value is set for the db_file_multiblock_read_count
parameter. The formula to calculate the value for this parameter is
this:
( (OS Max IO size /
db_block_size) )
Get the value of OS Max IO size
from you Unix admin. On most Sun boxes, this value is 128K.
|
