Orace: Analyze versus DBMS_STATS
By admin on Apr 7, 2009 in Oracle
It is clearly documented in Oracle documentation that DBMS_STATS should be used.
Do not use the COMPUTE
and ESTIMATE
clauses of ANALYZE
to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS
package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS
. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS
package.
You must use the ANALYZE
statement (rather than DBMS_STATS
) for statistics collection not related to the cost-based optimizer, such as:
-
To use the
VALIDATE
orLIST
CHAINED
ROWS
clauses -
To collect information on freelist blocks
There are times when DBMS_STATS takes longer time to finish, and also after the analysis your queries actually run slower. Check the followings when this happen
- Make sure you patch your Oracle accordingly. Check MetaLink on the required patch level if you have the access
- Turn on parallelism if running on multi CPUs machine
- And always experiment before you decide which one to use
Post a Comment