eTutorials.org

Problem Description

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