Monday, July 25, 2011

Which is better for gathering statistics? DBMS_STATS or ANALYZE command

Use DBMS_STATS for gathering statistics that related to the optimizer,execution plans and so forth.
Cost-Based Optimizer (CBO) will only use the statistics been gathered by DBMS_STATS.

Use ANALYZE command to collect statistics NOT related to Cost-Based Optimizer (CBO)
Such statistics can be used by VALIDATE or LIST CHAINED ROWS clauses, can help in gathering accurate data such as empty blocks, average space, Freelist blocks and so forth.

For more information please check this link:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/general002.htm#ADMIN11525
Database Administrator's Guide(11.2)

No comments: