CustomerC is running an application and has identified a particular query that is taking over 45 minutes to complete, returning only one row.
The customer was able to determine the problem query, using the application snapshot and dynamic SQL snapshot. The problem query was identified as:
select a.c2, b.c2, c.c3 from a,b,c where a.c1=b.c1 and b.c2=c.c2 and c.c3=a.c3 and c.c1=50193
The explain plan for the SQL statement indicated the following:
Estimated Cost = 2968724 Estimated Cardinality = 99996
The DBA knew that, because the column C1 in the table C is a primary key, there can be a maximum of only one row returned by the SQL statement. Therefore, the estimated cardinality in the access plan is not correct. A difference in the expected cardinality and the optimizer's estimated cardinality this large is normally due to old or nonexistent statistics.
To determine when the last time statistics were gathered for these three tables, use the following statement:
select stats_time from syscat.tables where tabname in ('A','B','C')
The output of the above statement was:
STATS_TIME ------------------------- - - - 3 record(s) selected.
Therefore, the tables do not have current statistics.
To gather statistics, use the RUNSTATS command as follows:
runstats on table user.a with distribution and detailed indexes all runstats on table user.b with distribution and detailed indexes all runstats on table user.c with distribution and detailed indexes all
After running the RUNSTATS command for all of the tables in the statement, it was able to complete in less than two seconds, and the explain plan indicated the following:
Estimated Cost = 183 Estimated Cardinality = 1