SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Maintaining Query Execution Plan Validity
A query execution plan defines the semantics and execution characteristics for a single compiled
SQL statement. Statically compiled applications typically have many query execution plans. The
query execution plans of an application are stored in an SQL/MX user module. Globally placed
modules are stored in the /usr/tandem/sqlmx/USERMODULES directory. Locally placed modules
are stored in the same directory as the C, C++, or COBOL executables that use them.
It is important to maintain valid query execution plans for the optimal performance of the database
applications in a production environment. This subsection describes the factors that affect the
validity of query execution plans and provides guidelines for recompiling, displaying, and analyzing
query execution plans.
Factors Affecting the Query Execution Plan
Changes to the table structure or the distribution of data in a table can cause a compiled query
execution plan to become invalid. When the SQL/MX executor detects these changes at run time,
it forces automatic recompilation of the query execution plan. Automatic recompilation incurs a
performance cost because it requires the query execution plan to be regenerated at run time and
stored in memory. The automatically recompiled plan is not saved for subsequent executions of
the same program or for multiple concurrent executions of the same program. Therefore, the plan
must be regenerated each time the application runs. Because of this behavior, automatic
recompilation might be unsuitable for some production environments.
To ensure the optimal performance of database applications in a production environment, either
prevent the occurrence of automatic recompilation or detect its occurrence:
• By default, automatic recompilation is enabled for all database applications. To disable
automatic recompilation, set the AUTOMATIC_RECOMPILATION default attribute to OFF.
• By default, the SQL/MX executor does not return a warning message to the database
application when a DML statement is automatically recompiled. However, NonStop SQL/MX
always logs a warning event, SQL/MX message 505, to the Event Management Service (EMS)
log when a statement is automatically recompiled. To return automatic recompilation warning
messages directly to the program, set the RECOMPILATION_WARNINGS default attribute to
ON.
For more information on these default attributes, see the SQL/MX Reference Manual. For more
information on controlling automatic recompilation within database applications, see the SQL/MX
Programming Manual for C and COBOL. For more information, see the EMS Manual and the
Operator Messages Manual.
If a plan requires automatic recompilation, you should explicitly recompile the module. See
“Recompiling a Module” (page 219).
Recompiling a Module
Periodically, depending on changes to the database or the occurrence of automatic recompilation,
you might need to recompile the modules of an application. Explicitly recompiling modules generates
optimal query execution plans and prevents the performance cost of automatic recompilation. To
compile a module, see the SQL/MX Programming Manual for C and COBOL.
To automate the process of recompiling modules, consider putting recompilation commands in a
script file.
Migrating SQL/MX Nodes to Obtain Improved Query Plan Performance
New releases of NonStop SQL/MX sometimes provide enhancements to query execution plan
performance over previous releases. To achieve these performance enhancements on all the
SQL/MX nodes in a distributed database, you must migrate all the nodes running an earlier release
of NonStop SQL/MX to the new release or install the SPR that provides the new functionality on
those nodes.
Maintaining Query Execution Plan Validity 219