Databases Reference
In-Depth Information
index is missing or that a materialized view or MDC design can be used to
remove the need for the sort. If the sort is used for a group-by operation, it may
form an excellent portion of a materialized view.
3.
Small sorts.
Small sorts are not usually a problem if they occur rarely. But a small
sort on thousands of short-running concurrent queries can be a disaster. Again,
an index, MDC, or materialized view will usually correct the problem.
4.
High interpartition communication on shared-nothing systems
. If the query execu-
tion plan suggests very high interpartition communication, it may be an indica-
tor that the tables involved in many joins are poorly collocated. A new
partitioning design for the tables (or use of replicated materialized views) can
help resolve this issue.
5.
Very high execution cost (resource consumption).
If the execution cost of a query is
very high, it's probably going to be one of the slowest queries and will consume
a lot of system resources (possibly CPU, I/O, network; the query execution
plan should tell you which). This may not be a problem—the query may
indeed be an expensive one to execute because of its complexity. But the high
cost can help lead you to the queries that need your attention.
6.
Frequently occurring predicates.
Commonly recurring predicates can form poten-
tial start/stop keys for an index. Consider the columns used in frequently occur-
ring predicates as candidates for leading columns of a new index.
7.
Design attributes not used.
If attributes you have added to your database design
(indexes, materialized views, MDC, etc.) are not being used, you may have
incorrect assumptions about the database and the workload, or perhaps the
query optimizer has erred and selected a suboptimal query execution plan that
fails to use the design attributes you have built into the system. Either way, it's a
problem that needs attention. If some design attributes turn out not to be use-
ful, you should examine the maintenance cost of keeping them around and
consider removing them.
There are some broader reasons why being able to see the query execution plans
selected by the query optimization process can be very beneficial for database design:
1.
Capturing the query execution plans allows the user to maintain a history of the
most expensive, and therefore usually the most problematic, query execution
plans across transition periods.
2.
New index additions and other design attributes, such as range and list parti-
tioning, MDC, materialized views, and partitioning choices can be examined
for their impact on the query execution plans of the queries typically issued
against the database.
