Databases Reference
In-Depth Information
SQLLDR, SYS_DL_CURSOR, SYS_PARALLEL_TXN, SYS_RID_ORDER,
TIV_GB, TIV_SSF, UNNEST, USE_TTT_FOR_GSETS.
11.7.3 Query Hints When the SQL Is Not Available to Modify
In the majority of cases the database designer is not the same person as the application
designer and he or she does not usually have access to the application code in order to
add the hints. Because of this, a more powerful strategy for hints is one that allows
database administrators to add the hints without modifying the SQL text of the
incoming queries. The basic strategy that database vendors use to accomplish this is to
allow an administrator to define a template for queries with an associated hint. When a
query enters the system, it is briefly examined to see if it matches one of these tem-
plates and, if there is a match, the rules for that template are applied to the incoming
query. Oracle calls this Stored Outlines and DB2 calls this Optimization Profiles.
Stored Outlines and Optimization Profiles provided via a table allow prepackaged
applications to be “hinted” without opening them up to modify the SQL. A disadvan-
tage is that the external guideline must be matched to the SQL statement fairly closely.
Products like DB2 and Oracle try to normalize the statement as much as possible by
squeezing white space and uppercasing, but if the statement text gets rearranged at all,
it will often no longer match.
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. Alarm bells.
Table scans and large sorts that appear in the query execution plans
are alarm bells. These should immediately grab your attention and prompt exploration
for design features to add or remove.
•
Tip 2. Determine where the bulk of the query execution time is spent.
Use query
execution plans heavily during database design to detect major resource consumption
points. These points in the query execution plan are the areas where you should focus
your design efforts to improve query performance and therefore system efficiency.
•
Tip 3. Ensure your design features are being used.
Use query execution plan analysis
to see if indexes, MDC, materialized views, and range partitioning are actually being
appropriately used by the query compiler the way you expect.
•
Tip 4. Index cardinality.
Use query execution plan analysis to determine the selec-
tivity of predicates applied on base table accesses; this can be a good indicator for
whether an index can help reduce table access time. If the selectivity is very high (i.e.,
not many rows get filtered out), more than 40% or so, it is unlikely an index will help
reduce I/O, unless the index is designed to include all the columns required by the
operator on the table.
