Updating oracle statistics

by  |  04-Feb-2015 00:59

Let's take a close look at each directive and see how it is used to gather top-quality statistics for the cost-based SQL optimizer.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).

The optimizer depends on various inputs, and one of these is system derived information.

This information includes such things as disk response times and CPU response times.

Analyze is the old way, starting around 8i or 9i, Oracle documentation started saying to use dbms_stats instead.

Analyze is still useful, does a few things that dbms_stats doesn't.

By and large, for optimizer statistics, use dbms_stats.

Community Discussion