NonStop NS-Series Database Migration Guide

Post-Migration Activities
HP Integrity NonStop NS-Series Database Migration Guide—540469-001
5-2
Evaluating SQL Plans on the New System
•
If the database is populated by SQL/MP tables that are accessed by SQL/MX,
perform SQL/MP UPDATE STATISTICS on those tables and perform SQL/MX
UPDATE STATISTICS, in either order.
SQL/MX UPDATE STATISTICS obtains far more detailed information than does
SQL/MP UPDATE STATISTICS.
The larger the table, the longer the time to update the statistics. If you must obtain
complete statistics, either run the UPDATE STATISTICS command as a lower-priority
background process or run the UPDATE STATISTICS command during hours when
peak database performance is not required.
For more information about UPDATE STATISTICS, see the SQL/MX Query Guide, the
SQL/MX Reference Manual, the SQL/MP Query Guide, and the SQL/MP Reference
Manual.
Evaluating SQL Plans on the New System
Changes that occur to the structure of a database before, during, and after migration
can cause compiled query execution plans to become invalid. These changes include
modifying the database by adding columns to tables, splitting or merging partitions,
changing index leveling, and reducing slack space by compressing data. From the
Integrity NonStop NS-series system, it is important to generate new query execution
plans to ensure optimal database performance. Fortunately, you generate new query
execution plans automatically when you SQL compile the application programs on the
Integrity NonStop NS-series system.
Monitoring Performance on the New System
Whenever you add, remove, or change system resources, you alter the performance of
the system at some level. For example, running a new program can change the
number of I/O operations performed on a disk drive or increase the load on a
processor. Although some of these changes can be identified and planned in advance
of system and database migration, it is impossible to predict exactly how users will
access the new database on a daily basis. Until you actively monitor system
performance, you cannot ascertain the accuracy of your usage and performance
projections.
An ongoing and comprehensive program for monitoring system performance allows
you to intervene quickly to correct irregularities before real problems occur. For
example, when processor loads or disk activity exceed their established thresholds,
you can promptly rebalance loads to ensure continued and uninterrupted access to
those resources.
Note. To avoid locking problems, do not attempt to run SQL/MP UPDATE STATISTICS
and SQL/MX UPDATE STATISTICS on the same object concurrently.