Problem DescriptionCustomerC 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 ResolutionStep 1: Identified the QueryThe 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 StatementThe 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 CurrentTo 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 TablesTo 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: RetestAfter 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 |