SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Managing Database Applications
HP NonStop SQL/MX Installation and Management Guide—523723-004
11-14
Maintaining Query Execution Plan Validity
For more information, see the Guardian User’s Guide and the Safeguard User’s Guide.
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 limitation, 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 and the
SQL/MX Programming Manual for Java. For more information about EMS, see the
EMS Manual and the Operator Messages Manual.