|
Controls the gathering of statistics during the execution of data-altering DML statements such as INSERT, DELETE, and UPDATE.
On, Off
On
| PUBLIC role | For current user | For other users | |
|---|---|---|---|
| Allowed to set permanently? | Yes, with SET ANY PUBLIC OPTION | Yes | Yes, with SET ANY PUBLIC OPTION |
| Allowed to set temporarily? | Yes, with SET ANY PUBLIC OPTION | Yes (current connection only) | No |
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 when 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.