I would like to reflect once more on the “dynamic sampling used for this statement” message. Suppose dynamic sampling really happened with reason, because statistics were old, missing or otherwise insufficient. Now what?
Dynamic sampling because of missing or old statistics
Usually dynamic sampling means there is a reason that Oracle needs an adhoc sample of your table or index to come up with an execution plan. But what statistics are old/missing? If you’re dealing with a join of 7 tables with partitions you could query dba_tables, dba_indexes, dba_tab_partitions and dba_tab_columns to find out what’s missing. But you’ll find the reasons for dynamic sampling much faster in an event 10053 trace. Just open the trace file and look for “** Dynamic sampling initial checks returning TRUE”.
Access path analysis for TABLE_A *************************************** SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for TABLE_A[TABLE_ALIAS] *** 2014-04-09 17:21:07.430 ** Performing dynamic sampling initial checks. ** Column (#4): END_DATE( NO STATISTICS (using defaults) AvgLen: 9 NDV: 1259020 Nulls: 0 Density: 0.000001 Column (#10): DELETE_FLG( NO STATISTICS (using defaults) AvgLen: 512 NDV: 1259020 Nulls: 0 Density: 0.000001 ** Dynamic sampling initial checks returning TRUE (level = 5).
Above that line you will find what statistics were lacking according to Oracle. So in this example the columns END_DATE and DELETE_FLG of TABLE_A have no statistics. Maybe because you ran dbms_stats without gathering (some) column statistics (check what you’re entering for the method_opt argument). Maybe because theses columns were just added. Anyway, it’s clear what needs to be done. Something like:
BEGIN dbms_stats.Gather_table_stats(user, 'TABLE_A', method_opt => 'FOR ALL COLUMNS SIZE AUTO'); END; /
I’m curious what you think about event 10053 traces, so I created this little poll.