Example: Listing all committed DDL statements using the change history event monitor

You can use the change history event monitor to quickly list all committed DDL statements executed to determine if any changes made might be impacting your workload.

Scenario

In this example, the database administrator (DBA) notices a degradation in the performance of a number of queries has occurred in the last 24 hours. The DBA uses the change history event monitor to quickly examine the DDL executed during that time period, in order to determine if any changes were made that might be having a significant impact on the workload (for example, indexes that were dropped). The DBA previously created a change history event monitor called CHGHIST, that is being used to track DDL statements. The DBA issues the following statement to list all committed DDL statements captured by the change history event monitor in the last 24 hours. The issued statement excludes statements that were rolled back, either via a ROLLBACK statement or ROLLBACK TO SAVEPOINT statement.

Note that the change history event monitor records DDL events when the DDL is executed. Whether the DDL causes any change in the database depends on the DDL being committed.

WITH savepoint_rollbacks (global_tran_id, local_tran_id, savepoint_id) AS
   (SELECT DISTINCT T.global_transaction_id, T.local_transaction_id, T.savepoint_id
      FROM DDLSTMTEXEC_CHGHIST as D, TXNCOMPLETION_CHGHIST as T
      WHERE T.txn_completion_status='S' AND
            D.savepoint_id >= T.savepoint_id AND
            D.event_timestamp <= T.event_timestamp)
   SELECT VARCHAR(D.STMT_TEXT, 70) AS STMT_TEXT FROM DDLSTMTEXEC_CHGHIST as D,
            TXNCOMPLETION_CHGHIST as T
      WHERE D.global_transaction_id = T.global_transaction_id AND
            D.local_transaction_id = T.local_transaction_id AND
            T.txn_completion_status = 'C' AND
            (D.global_transaction_id, D.local_transaction_id, D.savepoint_id)
   NOT IN (SELECT * FROM savepoint_rollbacks) AND
            D.EVENT_TIMESTAMP > CURRENT TIMESTAMP - 24 HOURS;

STMT_TEXT
----------------------------------------------------------------------
CREATE INDEX I1 ON T1 (ONE)

1 record(s) selected.