Utilize Oracle's optimizer_dynamic_sampling

Summary

Oracle statistics help Oracle’s Cost Based Optimizer (CBO) in determining the most efficient execution path for returning result sets from SQL statements.

ESRI recommends that all objects, including the ArcSDE repository tables, have Oracle statistics collected. However, there are some tables for which it is best not to store statistics as these tables are very dynamic. These are the SDE_LOGFILE_DATA and KEYSET tables. Due to the rapid change in the number of records in these tables, ESRI recommends using Oracle’s OPTIMIZER_DYNAMIC_SAMPLING to gather statistics for these tables at the time of the query.

Procedure

1. Remove the current statistics on SDE_LOGFILE_DATA tables created pre 9.2.
   
   For each database user that has shared logfile tables, drop the existing statistics on the SDE_LOGFILE_DATA tables:
   
   

   Code:
   exec DBMS_STATS.DELETE_TABLE_STATS(‘<owner>’,’sde_logfile_data’);

   
   
2. Check the OPTIMIZER_DYNAMIC_SAMPLING parameter:
   
   Query the current setting of the OPTIMIZER_DYNAMIC_SAMPLING parameter as the SYSDBA user:
   
   

   Code:
   select name, value
   from v$parameter
   where name = 'optimizer_dynamic_sampling';
   
   NAME                           VALUE
   ------------------------------ ------------------------------
   optimizer_dynamic_sampling     0
   

   
   If this parameter is set to 0, alter the system to set this parameter:
   
   

   Code:
   alter system
   set optimizer_dynamic_sampling = 2
   scope=both;