Useful stuff from event 10053 traces – part 2

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.

 

Advertisements

About Marcel-Jan Krijgsman

Marcel-Jan is de PR-functionaris van de Werkgroep Maan en Planeten. Hij verzorgt ook het nieuws op de Facebook pagina en deze blog.
This entry was posted in Oracle performance tuning and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s