Lock SDE_LOGFILE_DATA table statistics with Oracle

Summary

ESRI recommends that Oracle's dynamic sampling capability be used to determine the best execution plan on very dynamic tables, specifically SDE_LOGFILE_DATA tables.

By allowing Oracle to dynamically sample the table upon query execution, the statistical information gathered is accurate for this given moment. If statistics are gathered for these tables, the statistical information may be incorrect for the table at any given time. This influences Oracle's Cost Based Optimizer (CBO) in choosing a suboptimal execution plan.

It is recommended to drop the statistics on the SDE_LOGFILE_DATA tables and lock the empty (uncollected) statistical information using the DBMS_STATS.LOCK_TABLE_STATS procedure.

This only applies to Oracle 10g; Oracle 9i does not have the lock_table_stats procedure.

Procedure

1. Before locking the statistics on the SDE_LOGFILE_DATA tables, drop any current statistics and enable dynamic sampling. See the link in the Related Information section for more information.
2. Use the following command to lock the statistics on the SDE_LOGFILE_DATA tables. This maintains empty statistics for these tables.
   
   

   Code:
   exec sys.dbms_stats.lock_table_stats('<USER>', 'SDE_LOGFILE_DATA')

   
   When gathering schema stats in the future, the SDE_LOGFILE_DATA table would not update with current statistics.