Using Dynamic Sampling to Improve Batch Data Load Performance

After collecting DBMS_STATS (see Collecting Statistics for Optimization), delete and lock statistics for the temporary tables used by Batch Data Load in order to optimize performance. When encountering tables with no statistics, Oracle RDBMS dynamically gathers the necessary statistics as part of query optimization. With volatile temporary tables, dynamic sampling contributes to better performance.

1. Set database initialization parameter optimizer_dynamic_sampling to 2 or higher. (The default value is 2.)

2. Log into SQL*Plus as system and run the following commands to delete statistics for Batch Data Load temporary tables:

   EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>'RXC', TABNAME=>'TEMPORARY_BATCH_DCIS'); 
   EXEC  DBMS_STATS.LOCK_TABLE_STATS(ownname=>'RXC', TABNAME=>'TEMPORARY_BATCH_DCIS'); 
   EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>'RXC', TABNAME=>'TEMPORARY_BATCH_DCMS'); 
    

3. Lock statistics for the Batch Data Load temporary tables to enable dynamic sampling:

   EXEC  DBMS_STATS.LOCK_TABLE_STATS(ownname=>'RXC', TABNAME=>'TEMPORARY_BATCH_DCMS'); 
   EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname=>'RXC', TABNAME=>'temporary_batch_data_items'); 
   EXEC  DBMS_STATS.LOCK_TABLE_STATS(ownname=>'RXC', TABNAME=>'temporary_batch_data_items');