dm_UpdateStats vs DBMS_STATS

February 2, 2007 at 8:33 am | Posted in Documentum and Oracle, Performance | Leave a comment

A comment on one of my previous posts asked whether dm_UpdateStats should be used if automatic statistic collection is enabled in Oracle 10g. I discuss the answer I gave in more detail here.

All the current databases that run Content Server use a cost-based optimizer to decide how a query should be executed. A key input into that decision is statistics the database stores about the data in tables and indexes. Clearly there is some benefit in making sure that the statistical information is up-to-date.

This can be overstated sometimes (I’ve known people who routinely run update stats before they attempt to examine any sort of problem) but whenever you have a performance problem that can be traced to the database the first thing to check is whether statistics have been created on the tables involved.

In Oracle versions 9 and 10 there are 2 different methods of collecting statistics:

  • ANALYZE TABLE … COMPUTE STATISTICS
  • DBMS_STATS package

The DBMS_STATS package is Oracle’s recommended way of collecting stats; ANALYZE TABLE … COMPUTE STATISTICS is retained for backward compatibility. Eventually DBMS_STATS will be the only supported way of collecting statistics. But bear in mind even though ANALYZE TABLE is still officially tolerated by Oracle the statistics it compiles are likely to be different from DBMS_STATS. It is possible that certain new features in the CBO (e.g. new or improved query execution methods) may not be used when ANALYZE TABLE statistics have been created.

So what does dm_UpdateStats use? ANALYZE TABLE of course. The code for dm_UpdateStats was written sometime ago and has not been seriously updated since. When I spoke to Ed Bueche at Momentum last year he indicated that the switch to DBMS_STATS would have to be done sometime, just not yet.

This is what dm_UpdateStats actually does:

  1. A list of ANALYZE TABLE commands is maintained in the table dm_update_stats_commands
  2. When dm_UpdateStats runs it refreshes the tables using the query:
    CREATE TABLE dm_udpate_stats_commands AS
    SELECT 'analyze table ' || table_name || ' compute statistics' AS commands FROM user_tables
  3. Since the table user_tables contains a list of all tables in the docbase owner’s schema the dm_update_stats_commands table contains a row-by-row list of the ANALYZE TABLE commands that must be run for all tables in the schema. The dm_UpdateStats code simply runs through this row, executing each command using the execsql API command.
  4. For good measure there is a set of additional ANALYZE statements in the script custom_oracle_stat.sql located in the directory %DOCUMENTUM%\dba\config\. These additional ANALYZE statements contain ANALYZE TABLE ... COMPUTE STATISTICS FOR COLUMNS ... statements that add histograms to certain columns. If you want to add additional histograms or alter the existing ones this is the place to add your customisations. I’ll be saying a lot more about histograms in a future posting.

So to wrap up:

  • It is a good idea to ensure statistics have been created on your tables. For most installations running dm_UpdateStats once a week works well
  • If your DBA is already running a regular job using DBMS_STATS then don’t run dm_UpdateStats (inactivate the job)
Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: