Performance of in-place alters for DDL operations

In-place alter operations on data definition language (DDL) statements can slow performance. Therefore, monitor outstanding in-place alter operation because many outstanding alter operations affect subsequent ALTER TABLE statements.

The oncheck -pT command displays data-page versions for outstanding in-place alter operations. An in-place alter is outstanding when data pages still exist with the old definition.

Sample oncheck -pT output for the customer table shows a portion of the output that the following oncheck command produces after four in-place alter operations are run on the customer demonstration table:

Figure 1: Sample oncheck -pT output for the customer table

oncheck -pT stores_demo:customer

...
Home Data Page Version Summary

            Version           Count

            0 (oldest)           2
            1           0
            2           0
            3           0
            4 (current)           0
...

The Count field in Sample oncheck -pT output for the customer table displays the number of pages that currently use that version of the table definition. This oncheck output shows that four versions are outstanding:

A value of 2 in the Count field for the oldest version indicates that two pages use the oldest version.
A value of 0 in the Count fields for the next four versions indicates that no pages were to the latest table definition.

Important: As you perform more in-place alter operation on a table, each subsequent ALTER statement or the SQL statements that run against the tables with outstanding alters take more time to run than the previous statement. To maintain efficient performance, regularly remove outstanding in-place alter operations.

You can remove in-place alter operations by running the admin( ) or task( ) SQL administration command with the table update_ipa or fragment update_ipa argument. You can include the parallel option to run the operation in parallel. For example, the following statement removes in-place alter operations in parallel from a table that is named auto:

EXECUTE FUNCTION task('table update_ipa parallel','auto');

You can remove in-place alter operations by converting data pages to the latest definition with a dummy UPDATE statement. For example, the following statement, which sets a column value to the existing value, causes the database server to convert the format of the data pages to the latest definition:

UPDATE tab1 SET col1 = col1;

If your goal is saving runtime CPU, then plan to keep as few outstanding alters operations on a table as possible (generally no more than 3 or 4). If your goal is to save on disk space and your alter operations add or grow columns, then leaving in-place alters outstanding helps reduce disk space. If you need to revert to an earlier version of the database server, however, one requirement is that no data pages can include incomplete ALTER TABLE or ALTER FRAGMENT operations.

After all outstanding in-place alter operations have been completed on a table or fragment, the oncheck -pT command displays the total number of data pages in the Count field for the current version of the table.