Updating statistics in oracle
The dbms_stats utility is a great way to improve SQL execution speed.
By using dbms_stats to collect top-quality statistics, the CBO will usually make an intelligent decision about the fastest way to execute any SQL query.
If you issue the alter table xxx monitoring command, Oracle tracks changed tables with the dba_tab_modifications view, which allows you to see the exact number of inserts, updates, and deletes tracked since the last analysis of statistics.
You can verify the accuracy of the automatic statistics sampling by looking at the dba_tables sample_size column.
To aid in intelligent histogram generation, Oracle uses the method_opt parameter of dbms_stats.Oracle histograms statistics can be created when you have a highly skewed index, where some values have a disproportional number of rows.In the real world, this is quite rare, and one of the most common mistakes with the CBO is the unnecessary introduction of histograms in the CBO statistics.Using one of the four provided methods, this option governs the way Oracle statistics are refreshed: * gather Reanalyzes the whole schema * gather empty Only analyzes tables that have no existing statistics * gather stale Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).* gather auto Reanalyzes objects which currently have no statistics and objects with stale statistics (Using gather auto is like combining gather stale and gather empty.) Note that both gather stale and gather auto require monitoring.