First page Back Continue Last page Overview Graphics

DBMS_STATS…


Notes:

The use of the ANALYZE command to gather statistics has been deprecated and will probably be desupported in future releases.
In this example I use the GATHER_SCHEMA_STATS procedure to collect statistics all tables and all dependent indexes in the current schema. Unlike the ANALYZE command (deprecated), the default is not to collect statistics on dependent indexes, so you must use CASCADE =>TRUE parameter.
The METHOD_OPT parameter controls the creation of histograms. Histograms allow the optimizer to detect and plan for skewed distributions of data. Histograms are expensive, so only create them on indexed columns.
Warning: The default is to create histograms on every column. This is rarely useful and very expensive. Use the method_opt=> 'FOR ALL INDEXED COLUMNS' clause, which will create histograms only for some columns.

OPTIONS=>GATHER_STALE limits the collection of statistics to only those tables that have the MONITORING option turned on and have at least 10% of the total rows updated since the last collection. The default OPTION is GATHER, which causes all objects in the schema to be affected, regardless of the current state of their statistics.
See the supplied script DBMS_STATS.SQL for a working example of the code shown above.