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

Ever since I started working with Oracle, I had an interest in Oracle database performance tuning. This led, eventually, to a four day training I made and gave for customers of Transfer Solutions. Since 2012 I work for Rabobank Nederland. A few years ago I also became interested in Oracle database security. All technology aside, it is my experience that security usually plays out on a political level. I'm a Oracle certified professional for the 8i, 9i, 10g and 11g databases and Oracle Database 11g Performance Tuning Certified Expert.
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s