RSS Feed for This PostCurrent Article

Orace: Analyze versus DBMS_STATS

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 or LIST 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

Trackback URL

RSS Feed for This PostPost a Comment

Refresh Image