CustomerC is running an application and has identified a particular query that is taking over 45 minutes to complete, returning only one row.
Problem Analysis and Resolution
Step 1: Identified the Query
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
Step 2: Run Explain for the SQL Statement
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.
Step 3: Check to Determine Whether the Tables' Statistics Are Current
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.
Step 4: Gather Statistics for the Tables
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
Step 5: Retest
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