On, Off

On

Can be set for an individual connection or for PUBLIC. You must have the SET ANY PUBLIC OPTION system privilege to set this option for PUBLIC or for another user or role.

The database server updates statistics during normal statement execution and uses the gathered statistics to self-tune column statistics. Set the collect_statistics_on_dml_updates option to Off to disable the updating of statistics during the execution of data-altering DML statements such as INSERT, DELETE, and UPDATE.

Under normal circumstances, it is not necessary to turn off this option. However, in environments where large amounts of data are frequently changing, setting this option to Off can improve performance—assuming update_statistics is also set to On.

The difference between the collect_statistics_on_dml_updates option and the update_statistics option is that the update_statistics option compares the actual number of rows that satisfy a predicate with the number of rows that are estimated to satisfy the predicate, and then updates the estimates. The collect_statistics_on_dml_updates option modifies the column statistics based on the values of the specific rows that are inserted, updated, or deleted.

• update_statistics option
• Tip: Update column statistics
• collect_statistics_on_dml_updates connection property