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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-7
Keeping Statistics Current
•
For large tables, audit trail space can be exceeded during the course of the
operation, resulting in termination of the operation and backout by the TMF
subsystem. This condition is minimized if you allow NonStop SQL/MX to manage
TMF transactions.
•
If the operation cannot acquire the exclusive lock when required, NonStop SQL/MX
terminates the operation abnormally after a predetermined period of time.
Remember that the operation requires the simultaneous availability of all file labels
that must be changed during the operation. The lock timeout value is currently 60
seconds and cannot be changed.
In a similar way, certain other statements or commands present concurrency issues
that can affect the result of the operation. When you are duplicating, backing up, or
moving data from one object to another, these functions do not require sustained
exclusive access to the source objects. The only exclusive access involved is similar to
that required in Step 3 at the end of the function. During a DUP operation, you cannot
perform DML operations or utilities that update the data being duplicated. You can,
however, perform read operations.
Keeping Statistics Current
When you update statistics, information about a table is updated in the histogram
tables so that the information more accurately represents the current content and
structure of the SQL/MX database.
UPDATE STATISTICS collects and generates physical and logical statistics on SQL
objects. Physical statistics refer to the index level, nonempty block count, and EOF for
partitions of SQL/MX or SQL/MP tables and indexes. Logical statistics refer to high and
low values in columns, unique entry counts in NonStop SQL/MP, and histogram
information in NonStop SQL/MX.
To generate SQL/MX physical and logical statistics on SQL/MX objects, run SQL/MX
UPDATE STATISTICS. To generate optimal physical and logical statistics on SQL/MP
tables, first run SQL/MP UPDATE STATISTICS to update SQL/MP physical statistics,
including index levels, and then run SQL/MX UPDATE STATISTICS.
For additional information about running UPDATE STATISTICS, see the SQL/MX
Reference Manual.
For information on when and why you should update statistics, see the SQL/MX Query
Guide.
Note. HP recommends that you do not initiate a user-defined TMF transaction for
long-running DDL operations. Utility operations are not supported in user-defined TMF
transactions.