HP Neoview Release 2.5 Software - Manual (Page 101)

9 Tips for Query Plan Performance Improvements
NOTE: Refer to the course Neoview Performance Analyzer and Query Plan Workshop, course ID
01105317, for additional information about NPA and query plans. This course is designed to
simplify explain plans by utilizing Neoview Performance Analyzer (NPA) to review query plans,
identify common problems, and demonstrate physical DB design best practices.
Determine the Base Table Access Strategy
Through the FILE_SCAN operator, the query plan provides the information you need to
understand the type of access used to obtain data from a table. The FILE_SCAN operator always
runs within the disk process. The scan_type field of the FILE_SCAN operator indicates the type
of access made to the table partitions. The choices are:
Description scan_type Field Choices
Indicates the table or partition is scanned completely and sequentially.
This usually means that the query text primary key (or secondary
index) predicates were not useful for limiting the scan. full scan
Indicates the table or partition was partially scanned, usually due to
the presence of beginning or ending primary key predicates, or
MDAM predicates. subset scan
Indicates that exactly one row is accessed (see FILE_SCAN_UNIQUE
operator). unique scan
Indicates whether the scan was in forward or reverse order, based
upon the ordering of the table's primary key columns and the order
specified in the query text. No performance penalty exists for a reverse
order scan. scan direction
Indicates Multi-Dimensional Access Method (MDAM). MDAM is an
access strategy used by the disk process to prevent full table/partition
scans by "probing" through the data and skipping groups of unneeded
rows, which improves performance. Typically, MDAM is used only
when predicates are absent from low cardinality primary key columns
(often the left-most columns), but present for other columns. mdam
Contains the predicate values specified for the primary key columns,
if any. If no predicate values are present for a column, these fields
contain the terms <min> or <max>, indicating that the entire domain
range of values for the column is selected by the query. begin_key/end_key
When to Change the Default Aggregate Function
You might choose to change the default aggregate function if you want to check whether
processing skew might exist. Add the Processing Node Utilization graph three times to the
workspace. For one graph, apply the minimum aggregate function. For the second graph, apply
the maximum aggregate function. For the third graph, apply the average aggregate function.
See if there is a huge discrepancy in the values. If so, this could be indicative of problems, including
skewed data. For more information about aggregate functions, see
“Aggregate Functions”
(page 27) .
Improve File Scan Performance
Follow these guidelines to improve file scan performance:
Determine the Base Table Access Strategy 101