How To: Utilize Oracle's optimizer_dynamic_sampling


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.


Oracle’s CBO has the ability to dynamically collect statistics on a segment if statistics have not previously been created. Allowing Oracle to dynamically gather statistics can be useful in the following circumstances:

· Statistics are not present
· Statistics are out of date
· Current statistics cannot be used or lead to estimation errors

Oracle uses the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter to determine the level of dynamic sampling for the database. Refer to the Oracle documentation to understand the default settings for this parameter.

Based on the initialization parameter, Oracle determines at compile time if the query would benefit from dynamic sampling. If it would, Oracle samples a small random sample of the object’s blocks. Oracle also collects column and index statistics if necessary.

Dynamic sampling and SDE_LOGFILE_DATA tables

SDE_LOGFILE_DATA tables are very dynamic. As a session proceeds to perform operations which use selection sets, records are inserted in and deleted from this table. If no sessions are accessing the table, there are 0 rows in the table. Any statistics that were stored for this segment are either be out of date or misleading. Therefore, for this segment, allowing Oracle to dynamically collect statistics leads to better execution plans.

With ArcSDE 9.1 and 9.0, statistics were calculated automatically when the SDE_LOGFILE_DATA table was initially created. Upon creation, there are no rows in the table and the statistics report there are 0 rows and 0 blocks used. These statistics are used regardless if there are 200 rows in the table or 200,000 rows, potentially causing the CBO to make poor choices when determining the best execution plan.

With ArcSDE 9.2, statistics are not automatically created on SDE_LOGFILE_DATA tables. Therefore, if the OPTIMIZER_DYNAMIC_SAMPLING parameter is set, the CBO dynamically samples the tables.

Dynamic sampling and KEYSET tables

KEYSET tables are used to find related records and are created and dropped for user’s sessions. These objects are new at 9.2 and do not have statistics created automatically. To ensure dynamic sampling, the OPTIMIZER_DYNAMIC_SAMPLING parameter must be set.

Enabling dynamic sampling

To enable Oracle to perform dynamic sampling, verify the Oracle OPTIMIZER_DYNAMIC_SAMPLING parameter is set. Additionally, for SDE_LOGFILE_DATA tables created with pre 9.2, remove any current statistics on SDE_LOGFILE_DATA tables.

  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:

    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:

    select name, value
    from v$parameter
    where name = 'optimizer_dynamic_sampling';

    ------------------------------ ------------------------------
    optimizer_dynamic_sampling 0

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

    alter system
    set optimizer_dynamic_sampling = 2