Database Reference
In-Depth Information
the statistics. Therefore, run the database utility program frequently to keep
the statistics reasonably up to date.
•
Drop redundant indexes.
•
Add new indexes as suggested by changes in access patterns.
•
For a multiattribute index, verify and ensure that the order of the attributes as
defined in the index is appropriate for most of the processing using that index.
Tuning Queries and Transactions
When users run applications, they access the database through queries and trans-
actions. Queries perform read operations; transactions execute update, insert, and
delete operations. When transactions perform update, insert, and delete operations,
most of the time they deal with single records. However, queries may read one
record or a complete group of records based on a set of selection criteria. The selec-
tion may also involve join operations. Therefore, tuning queries constitutes the most
significant effort in the overall performance tuning.
As you have seen above, the query optimizer provided in the DBMS intercepts
each query and optimizes the query execution with a proper plan of execution.
Usually, you can get an indication that a query does not perform efficiently when
you note the following:
•
The query issues too many disk I/Os.
•
The query plan shows that the relevant indexes are not being used.
When you observe these symptoms, take appropriate action. Sometimes rewrit-
ing long-running queries may speed them up. Some DBMSs allow the use of hints
to the optimizer. Hints are suggestions to the optimizer to use an index other than
what it normally picks for a query when more than one index is available for a data-
base table.
Do not hesitate to run the database utility program provided for asking to explain
the plan used by the optimizer. Verify that the query is using the index you expect
it to use. Also, make sure the optimizer of your DBMS works well for all types of
queries and transactions. Some optimizers are found to perform poorly under the
following conditions:
•
Selection condition has null values
•
Selection condition involves arithmetic or string expressions
Let us close with a few comments on the impact of concurrency on the overall
performance.
•
In an environment with a high volume of transactions, excessive locking is
expected. Such excessive locking hurts performance.
•
Duration of locking also affects performance because of transactions waiting
for locks to be released.
Search WWH ::
Custom Search