Problem DescriptionAfter fixing the problem above and running RUNSTATS for all tables in the database, CustomerC found another statement that was taking too long to complete. Problem Analysis and ResolutionStep 1: Identify 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 c1,c2 from f where c1=67 and c2='NS' Step 2: Run Explain for the SQL StatementThe explain plan for the identified statement, as shown below, indicated that the optimizer had chosen an index-only access plan. Estimated Cost = 5814.739 Estimated Cardinality = 1 Access Plan: ----------- Total Cost: 5814.739 Query Degree: 1 Rows RETURN ( 1) Cost I/O 888.548 IXSCAN ( 2) 5814.739 2 955343 INDEX: USER1 FX1 Step 3: Check the IndexesAn index-only access plan normally provides good performance, and because the query is returning only one row, there appears to be something wrong with the table or indexes. The indexes for the table can be examined using the DESCRIBE TABLE command. The SHOW DETAIL option is required to get a listing of the key columns for the index. describe indexes for table f show detail The output of the above statement was: Index Index Unique Number of schema name rule columns Column names ------------ ------------ ------------ ------------ ------------ USER1 FX1 D 2 +C2+C1 Therefore, there is one index named FX1 on the table, and the index keys are C2 and C1. Because there is a + sign before each column name, the index keys are in ascending order. Step 4: Examine the Key CardinalityTo examine the cardinality of the index keys, there are two options, assuming that RUNSTATS was run recently:
The following statement will query the SYSCAT.INDEXES view to examine the key cardinality: select firstkeycard, first2keycard from syscat.indexes where indname='FX1' The output of this statement is: FIRSTKEYCARD FIRST2KEYCARD -------------------- -------------------- 4 34816 1 record(s) selected. Therefore, there are only four distinct values for C2 and 34,816/4, or 8,704 distinct values for C1. Therefore, if the order of the keys were reversed , the selectivity of the query would be improved, and the index would be able to find the result much quicker with an index defined as C1,C2. Step 5: Create the New IndexCreate the new index and capture the index statistics as follows : create index fx2 on f (c1,c2) collect statistics Step 6: RetestAfter creating the new index, the query was able to complete in less than a second. The new access plan is again using index-only access, but it is now using the new index. The new access plan looked like the following: Estimated Cost = 42.6512 Estimated Cardinality = 1 Access Plan: ----------- Total Cost: 42.6512 Query Degree: 1 Rows RETURN ( 1) Cost I/O 888.548 IXSCAN ( 2) 5814.739 2 955343 INDEX: USER1 FX2 |