SQL/MX Release 2.0 Best Practices

Performance Tuning 48
Analysis Methodology
Methodically follow these steps as a guide for improving query performance:
• Identify several key queries suffering poor performance, and use them as a test suite to evaluate the
effectiveness as the changes discussed later in this document are made. Obtain current
DISPLAY_EXPLAIN output for each query.
• For ODBC Server clients, when predicates for date columns are present in the query, replace them
with parameters (“?p”), to mimic the actions of the ODBC Server subsystem. This action can affect
the resulting plan.
• Obtain runtime statistics for each query, using the MXCI SET STATISTICS ON option. If a query returns a
large amount of data but you are only interested in obtaining the runtime statistics, use the [LAST 0]
clause in the SELECT statements. For example, SELECT [LAST 0] * FROM MYTABLE; will return zero rows,
but still show the runtime statistics.
• Identify all predicate columns and join columns for each query. These predicate columns will
become the entry points into each data table.
• Ensure that all tables are loaded with zero slack space, or are regularly reorganized online to
eliminate fragmentation and to ensure optimal scan performance.
• Update statistics for all tables after they have been loaded, after altering partitions, and after
adding secondary indexes.
• Obtain data-value distributions for critical key columns to determine the existence and
pervasiveness of skewed values. Critical key columns are those used for partitioning, MDAM query
technology, query predicates, and joins.
• Work with HP support representatives to establish queries that change statistics, where needed. Re-
run these queries after using the UPDATE STATISTICS statement.
• Regenerate Explain plans for the query suite and determine whether any of the plans have
changed.
• For queries that have new plans, re-run the queries and obtain runtime statistics. Determine whether
the performance improvement efforts have been effective.
• For the remaining poorly performing queries, analyze the data-access points (predicate and join
columns) for each table.
• Consider adding a secondary index.
• Consider adding a summary table.
• Consider reordering key column positions.
• Consider restructuring tables, either combining tables to avoid joins, or dividing a single table to
reduce the number of rows.
• Always regenerate Explain plans, and re-run the queries to determine the effectiveness of each
change.
• Modify the database design to accommodate the query requirements.
The main objective of these analysis steps is to provide the most efficient access path to the requested
data. Because of the skewed data distributions, the SQL/MX database optimizer cannot select an
adequate access plan. Consequently, evenly distributing data is the first change to make. After
balancing the data, you must identify existing entry points into the tables and determine whether a
secondary index, a summary table, or key-column reordering would improve response time. The most
extreme action involves physically altering the table structure in some way. Although physically altering
the table structure is normally the last course of action, it can produce the most profound query
improvements.
CONTROL QUERY SHAPE
Controlling the query shape is a last resort for changing access to the data. This method forces the query
to run exactly the plan you determine is best. If the table ever changes, (for example, the table acquires
a new index, partition, and so on) the plan you forced may no longer be efficient. Any forced queries
you make must be tracked manually. No automated way exists to identify queries that have been