We have an application that loads data from files and runs validation queries on the loaded data. We have observed very slow validation queries on the first file loaded after the nightly statistics job has run (and cleared all of the plans for the tables that have had new stats gathered). This causes huge delays in processing if the first file is a relatively large one. Comparing sql traces for a first and second run of the same small test file after the nightly stats job has run, the plans in the (slower) first run are generally showing parallel execution and the plans in the second run are not.

Question: How can we manage this situation to get better plan selection on the first run after the stats job?

Some relevant parameter settings are shown below. Note that parallel_degree_policy is set to ADAPTIVE, the thought being to give the optimizer as much flexibility as possible.

The tables and indexes in question have degree of 1.

We have observed similar behavior on both single and 2-node RAC systems.

SQL> show parameter optimizer;
NAME TYPE VALUE
------------------------------------ ------- --------
optimizer_adaptive_features boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 12.1.0.2
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_inmemory_aware boolean TRUE
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> show parameter parallel;
NAME TYPE VALUE
------------------------------- ------- --------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_level integer 100
parallel_degree_limit string CPU
parallel_degree_policy string ADAPTIVE
parallel_execution_message_size integer 16384
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 666
parallel_min_percent integer 0
parallel_min_servers integer 96
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 2
parallel_servers_target integer 384
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0