Slow performance making large selections from SDO_Geometry feature classes in ArcMap

Description

When selecting a large number of records from an SDO_Geometry feature class, slow performance may be encountered in ArcMap.

Cause

This can occur if statistics have been built on the SDE_LOGFILE tables.

If using the default shared logfile configuration, each Oracle user making selections in ArcMap will have SDE_LOGFILES and SDE_LOGFILE_DATA tables in their schema. These tables are initially created when each Oracle user selects over 100 records from a feature class in ArcMap. When the logfile tables are first created in the user's schema, they do not have statistics associated with them.

However, if Oracle's default, GATHER_STATS_JOB, is enabled (or the equivalent at Oracle 11g), this job automatically builds statistics for tables without them, including the logfile tables. The logfile tables are very dynamic, so building statistics when there are no records in the SDE_LOGFILE_DATA table subsequently leads to poor query execution when a large selection is made in ArcMap and this table contains thousands of records. This seems to be particularly noticeable with SDO_Geometry feature classes.

Solution or Workaround

1. Delete statistics from the SDE_LOGFILE_DATA table (see KB Article 32005).
2. Ensure dynamic sampling is enabled (see KB Article 32005).
3. Use the DBMS_STATS.lock_table_stats procedure to lock the SDE_LOGFILE_DATA table, so statistics cannot be built on this table in future (see KB Article 32164).
4. If using the shared logfile configuration, carry out Steps 1 and 3 for the SDE_LOGFILE_DATA table in each Oracle user's schema.
5. If using a pool of logfiles owned by the ArcSDE Administrator and not shared logfiles, delete and lock statistics on each SDE_LOGPOOL table in the ArcSDE administrator's schema.