IBM Query Tuner Report

This report contains a summary of the recommendations from the Query Tuner advisors and tools. Examine the recommendations and corresponding DDL scripts, if applicable, and take appropriate actions to tune your query. You can also examine the formatted query and access plan summary, and cross reference the recommendations generated by the advisors. Use the cataloged table, column, and index information to do further analysis and tuning. Navigate to the different sections using the action buttons and then return to the top of the report using Back to top.



Overview

Recommendation generation timestamp: 2009-08-27T13:55:13
Database server configuration: jdbc:db2://9.30.68.24:50000/TPCD (SQL09051)
Estimated plan cost: 14,480.799 units
Critical problems: 1 statistics recommendations, 0 index recommendations, 0 query recommendations, and 0 access path warnings.
Best practices: 0 statistics recommendations, 1 index recommendations, 0 query recommendations, and 0 access path warnings.

Advice Number Advice Type Tuning Recommendation Description
1 Statistics CRITICAL: Consolidate statistics: Use RUNSTATS to recollect all the relevant statistics for this query for an accurate evaluation Important: if statistics are missing, Query tuner estimates subsequent recommendations based on database default statistics. Click here to review the recommended RUNSTATS script.
2 Index Index recommendation is found for the following tables: TPCDS .CUSTOMER, TPCDS .CUSTOMER_DEMOGRAPHICS, TPCDS .CUSTOMER_ADDRESS. The total estimated disk space required is 152.183 MB and the estimated performance improvement is 36.738%. This index recommendation will help improve query performance for general indexing. Click here to review the recommended CREATE INDEX DDL script.

Back to top



Recommended Action

The tuning advisors recommend the following actions to update the table and index statistics, and to create the necessary indexes to improve the query performance.



Update Statistics Statements

Advice Number Statement Number Statement Text
1 1 RUNSTATS ON TABLE "TPCDS"."CUSTOMER" WITH DISTRIBUTION ON COLUMNS ( "C_CURRENT_CDEMO_SK" NUM_FREQVALUES 15 NUM_QUANTILES 25 , "C_CURRENT_ADDR_SK" NUM_FREQVALUES 15 NUM_QUANTILES 25 ) AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS
1 2 RUNSTATS ON TABLE "TPCDS"."CUSTOMER_DEMOGRAPHICS" ON COLUMNS ( "CD_DEMO_SK" ) AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS TABLESAMPLE BERNOULLI ( 40.0 ) REPEATABLE ( 50 )
1 3 RUNSTATS ON TABLE "TPCDS"."CUSTOMER_ADDRESS" ON COLUMNS ( "CA_ADDRESS_SK" ) AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS

Back to recommended action


Create Index Statements

Estimated performance improvement is 36.738%.
Estimated disk space use is 152.183 MB.

Advice Number Statement Number Statement Text
2 1 CREATE INDEX "DB2OE "."IDX908272056590000" ON "TPCDS "."CUSTOMER" ("C_CURRENT_ADDR_SK" ASC, "C_CURRENT_CDEMO_SK" DESC) ALLOW REVERSE SCANS
2 2 CREATE UNIQUE INDEX "DB2OE "."IDX908272057000000" ON "TPCDS "."CUSTOMER_DEMOGRAPHICS" ("CD_DEMO_SK" ASC) INCLUDE ("CD_DEP_COLLEGE_COUNT", "CD_DEP_EMPLOYED_COUNT", "CD_DEP_COUNT", "CD_CREDIT_RATING", "CD_PURCHASE_ESTIMATE", "CD_EDUCATION_STATUS", "CD_MARITAL_STATUS", "CD_GENDER") ALLOW REVERSE SCANS
2 3 CREATE INDEX "DB2OE "."IDX908272056520000" ON "TPCDS "."CUSTOMER_ADDRESS" ("CA_COUNTY" ASC, "CA_ADDRESS_SK" DESC) ALLOW REVERSE SCANS

Back to recommended action

Back to top



SQL Statement

Input Query


SELECT TPCDS.CUSTOMER_DEMOGRAPHICS.CD_GENDER
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_MARITAL_STATUS
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_EDUCATION_STATUS
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_PURCHASE_ESTIMATE
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_CREDIT_RATING
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_COUNT
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_EMPLOYED_COUNT
     , TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_COLLEGE_COUNT
     , COUNT(*)
    FROM TPCDS.CUSTOMER AS C /* no. of rows=100000 */
     , TPCDS.CUSTOMER_ADDRESS AS CA /* no. of rows=50000 */
     , TPCDS.CUSTOMER_DEMOGRAPHICS /* no. of rows=1920800 */
    WHERE CA.CA_COUNTY IN ('Rush County', 'Toole County', 'Jefferson County', 'Dona Ana County', 'La Porte County')
     AND C.C_CURRENT_ADDR_SK = CA.CA_ADDRESS_SK
     AND TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEMO_SK = C.C_CURRENT_CDEMO_SK
    GROUP BY TPCDS.CUSTOMER_DEMOGRAPHICS.CD_GENDER,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_MARITAL_STATUS,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_EDUCATION_STATUS,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_PURCHASE_ESTIMATE,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_CREDIT_RATING,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_COUNT,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_EMPLOYED_COUNT,
     TPCDS.CUSTOMER_DEMOGRAPHICS.CD_DEP_COLLEGE_COUNT

Back to top



Access Plan Summary

The following table summarizes the access plan's estimated return rows, estimated costs, the total number of table scan, index scan, join and sort operations, total number of predicates, explain timestamp, total number of cataloged tables referenced and the index usage with percentage of indexes used and percentage of indexes not used. You can examine the table and index scan details and the table access and join predicates being applied.
Note: Subselect (QBLOCK) is available for DB2 for z/OS only.

Subselect (QBLOCK) Estimated Return Rows Estimated Cost Plan Operator Totals Explain Timestamp Cataloged Tables Referenced Catalog Index Usage
Total Cost Total CPU Total IO Table Scan Index Scan Join Sort Predicate Used Indexes (%) Unused Indexes (%) Existing Indexes
- 223.096 14,480.799 15,963,469.000 1,125.478 1 3 3 1 3 2009-08-27T13:54:04 3 3 (50.0%) 3 (50.0%) 6




Database server transformed query for access plan


SELECT  Q7.$C0  AS  "CD_GENDER", 
     Q7.$C1  AS  "CD_MARITAL_STATUS", 
     Q7.$C2  AS  "CD_EDUCATION_STATUS", 
     Q7.$C3  AS  "CD_PURCHASE_ESTIMATE", 
     Q7.$C4  AS  "CD_CREDIT_RATING", 
     Q7.$C5  AS  "CD_DEP_COUNT", 
     Q7.$C6  AS  "CD_DEP_EMPLOYED_COUNT", 
     Q7.$C7  AS  "CD_DEP_COLLEGE_COUNT", 
     Q7.$C8 
FROM  (SELECT  Q6.$C0, 
     Q6.$C1, 
     Q6.$C2, 
     Q6.$C3, 
     Q6.$C4, 
     Q6.$C5, 
     Q6.$C6, 
     Q6.$C7, 
     COUNT(*  ) 
FROM  (SELECT  Q3.CD_GENDER, 
     Q3.CD_MARITAL_STATUS, 
     Q3.CD_EDUCATION_STATUS, 
     Q3.CD_PURCHASE_ESTIMATE, 
     Q3.CD_CREDIT_RATING, 
     Q3.CD_DEP_COUNT, 
     Q3.CD_DEP_EMPLOYED_COUNT, 
     Q3.CD_DEP_COLLEGE_COUNT 
FROM  TPCDS.CUSTOMER_DEMOGRAPHICS  AS  Q3, 
     TPCDS.CUSTOMER_ADDRESS  AS  Q4, 
     TPCDS.CUSTOMER  AS  Q5 
WHERE  (Q3.CD_DEMO_SK  =  Q5.C_CURRENT_CDEMO_SK) 
AND  (Q5.C_CURRENT_ADDR_SK  =  Q4.CA_ADDRESS_SK) 
AND  Q4.CA_COUNTY  IN  ('Rush  County', 
     'Toole  County', 
     'Jefferson  County', 
     'Dona  Ana  County', 
     'La  Porte  County'))  AS  Q6 
GROUP  BY  Q6.$C7, 
     Q6.$C6, 
     Q6.$C5, 
     Q6.$C4, 
     Q6.$C3, 
     Q6.$C2, 
     Q6.$C1, 
     Q6.$C0)  AS  Q7 

Back to access plan summary

Predicate for Table Access and Join

Predicates can disqualify rows and reduce the amount of required processing at later stage. The earlier DB2 can evaluate a predicate, the more the query performance can improve. When possible, try to write queries that evaluate the most restrictive predicates first. When predicates with a high filter factor are processed first, unnecessary rows are disqualified as early as possible, which can reduce processing cost at a later stage. Filter factors affect the choice of access paths by estimating the number of rows that qualified by a set of predicates.
Platform Tip: The Query block identifier and plan identifier are available on DB2 for z/OS only.

Query Block: Plan Identifier* Predicate Identifier Predicate Text Used in Join Used in Index Scan Used in Table Scan Filter Factor
- 10 (Q4.CA_COUNTY = Q2.$C0) Yes Yes No 0.000558
- 11 (Q3.CD_DEMO_SK = Q5.C_CURRENT_CDEMO_SK) Yes Yes No 0.000001
- 12 (Q5.C_CURRENT_ADDR_SK = Q4.CA_ADDRESS_SK) Yes Yes No 0.000020

Back to access plan summary

Table Access

Each time a table is accessed by the query, multiple predicate can be applied. For DB2 z/OS, the estimated costs are displayed at the mini plan level and there is no estimated cost available for index access when the value is "N".
Tip: The operator identifier is represented by an Operator ID for DB2 for Linux, UNIX, and Windows, and by "Query block: Plan ID" for DB2 for z/OS.

Operator Identifier** Table Name (Correlation ID) Index Access Estimated Qualified Rows Cumulative Total Cost Cumulative CPU Cost Cumulative IO Cost Predicate ID (How is applied) Advice Number
9 TPCDS.CUSTOMER_ADDRESS 
(Q4)
DB2OE.IDX904300126420000 27.887 25.839 120,097.023 2.000
  • 10 (START, STOP)
-
10 TPCDS.CUSTOMER 
(Q5)
DB2OE.IDX904300126500000 2.000 25.787 75,466.016 2.000
  • 12 (START, STOP)
-
11 TPCDS.CUSTOMER_DEMOGRAPHICS 
(Q3)
DB2OE.IDX904300126530000 1.000 38.653 89,011.234 3.000
  • 11 (START, STOP)
-

Back to access plan summary

Join Operation

Each table scan can be used with multiple predicate identifiers. On DB2 for z/OS, the estimated costs are displayed at the plan level.
Tip: the join identifier is represented by an operator ID for DB2 for Linux, UNIX, and Windows, and by a "Query Block: Plan ID" for DB2 for z/OS.

Join Identifier** Join Left Operand Join Right Operand Join Method Is Join Data Sorted Estimated Qualified Rows Cumulative Total Cost Cumulative CPU Cost Cumulative IO Cost Predicate Identifier Advice Number
7 SYSIBM.GENROW
(Correlation ID=Q1)
TPCDS.CUSTOMER_ADDRESS
(Correlation ID=Q4)
NLJOIN No 139.435 128.988 423,151.125 10.000 10 -
6 NLJOIN
(Operator ID=7)
TPCDS.CUSTOMER
(Correlation ID=Q5)
NLJOIN No 278.869 3,717.401 4,707,726.500 288.869 12 -
5 NLJOIN
(Operator ID=6)
TPCDS.CUSTOMER_DEMOGRAPHICS
(Correlation ID=Q3)
NLJOIN No 278.869 14,478.928 14,341,618.000 1,125.478 11 -

Back to access plan summary

Back to top

Method for applying the predicate.



Table and Index Catalog Information

The following list displays the referenced table statistics in the query access plan. All columns and indexes for the referenced tables are also listed. You can examine the table size, associated primary and foreign keys, cardinality and index columns information; and use the table and index statistics to plan for and reorganize tables and indexes with the REORG utility.

There are 3 referenced tables, 3 used indexes (50.0%), 3 unused indexes (50.0%), and a total of 6 indexes.


Referenced tables, sorted by table name.

**Tablespace name, qualified with database name when on a DB2 for z/OS data server.

Table Name Tablespace Name Foreign Key -> Parent Key Number of Columns Table Cardinality Number of Pages Average Row Size Stats Last Updated
TPCDS.CUSTOMER USERSPACE1 18 100,000 5,268 209 2009-04-29T18:21:51
TPCDS.CUSTOMER_ADDRESS USERSPACE1 - 13 50,000 2,014 160 2009-04-29T18:21:56
TPCDS.CUSTOMER_DEMOGRAPHICS USERSPACE1 - 9 1,920,800 30,028 62 2009-04-29T18:21:56

Back to table and index catalog info

All table columns, sorted by table name and physical column order

Table Name Column Number Column Name Column Type Length Nullable Column Cardinality
TPCDS.CUSTOMER 0 C_CUSTOMER_SK INTEGER 4 N 100000.0
  1 C_CUSTOMER_ID CHARACTER 16 N 100000.0
  2 C_CURRENT_CDEMO_SK INTEGER 4 N 94208.0
  3 C_CURRENT_HDEMO_SK INTEGER 4 N 7168.0
  4 C_CURRENT_ADDR_SK INTEGER 4 N 41984.0
  5 C_FIRST_SHIPTO_DATE_SK INTEGER 4 N 3651.0
  6 C_FIRST_SALES_DATE_SK INTEGER 4 N 3584.0
  7 C_SALUTATION CHARACTER 10 N 6.0
  8 C_FIRST_NAME CHARACTER 20 N 3648.0
  9 C_LAST_NAME CHARACTER 30 N 4992.0
  10 C_PREFERRED_CUST_FLAG CHARACTER 1 N 2.0
  11 C_BIRTH_DAY INTEGER 4 N 31.0
  12 C_BIRTH_MONTH INTEGER 4 N 12.0
  13 C_BIRTH_YEAR INTEGER 4 N 69.0
  14 C_BIRTH_COUNTRY VARCHAR 20 N 190.0
  15 C_LOGIN CHARACTER 13 Y 1.0
  16 C_EMAIL_ADDRESS CHARACTER 50 N 96256.0
  17 C_LAST_REVIEW_DATE CHARACTER 10 N 368.0
TPCDS.CUSTOMER_ADDRESS 0 CA_ADDRESS_SK INTEGER 4 N 50000.0
  1 CA_ADDRESS_ID CHARACTER 16 N -1.0
  2 CA_STREET_NUMBER CHARACTER 10 N -1.0
  3 CA_STREET_NAME VARCHAR 60 N -1.0
  4 CA_STREET_TYPE CHARACTER 15 N -1.0
  5 CA_SUITE_NUMBER CHARACTER 10 N -1.0
  6 CA_CITY VARCHAR 60 N -1.0
  7 CA_COUNTY VARCHAR 30 N 1664.0
  8 CA_STATE CHARACTER 2 N -1.0
  9 CA_ZIP CHARACTER 10 N -1.0
  10 CA_COUNTRY VARCHAR 20 N -1.0
  11 CA_GMT_OFFSET DECIMAL (5,2) N -1.0
  12 CA_LOCATION_TYPE CHARACTER 20 N -1.0
TPCDS.CUSTOMER_DEMOGRAPHICS 0 CD_DEMO_SK INTEGER 4 N 1920800.0
  1 CD_GENDER CHARACTER 1 N -1.0
  2 CD_MARITAL_STATUS CHARACTER 1 N -1.0
  3 CD_EDUCATION_STATUS CHARACTER 20 N -1.0
  4 CD_PURCHASE_ESTIMATE INTEGER 4 N -1.0
  5 CD_CREDIT_RATING CHARACTER 10 N -1.0
  6 CD_DEP_COUNT INTEGER 4 N -1.0
  7 CD_DEP_EMPLOYED_COUNT INTEGER 4 N -1.0
  8 CD_DEP_COLLEGE_COUNT INTEGER 4 N -1.0

Back to table and index catalog info

All indexes, sorted by table name, referenced index name, unreferenced index name, and key column sequence

Table Name Index Name Index Type Unique Rule Used in Plan Leaf Pages Index Levels Cluster Ratio (%) Clustering First Key Cardinality Full Key Cardinality Key Column Key Order Column Cardinality Statistics Last Updated
TPCDS.CUSTOMER DB2OE.IDX904300126500000 Regular Duplicate allowed Yes -1 -1 -1 No -1 -1.000 C_CURRENT_ADDR_SK Asc 41984.0 -
                      C_CURRENT_CDEMO_SK Asc 94208.0 -
  SYSIBM.SQL090429132202790 Regular Primary index No 417 3 -1 No 100,000.000 100,000.000 C_CUSTOMER_SK Asc 100000.0 2009-04-29T13:26:01
TPCDS.CUSTOMER_ADDRESS DB2OE.IDX904300126420000 Regular Duplicate allowed Yes -1 -1 -1 No -1 -1.000 CA_COUNTY Asc 1664.0 -
                      CA_ADDRESS_SK Asc 50000.0 -
  SYSIBM.SQL090429132201450 Regular Primary index No 209 3 -1 No 50,000.000 50,000.000 CA_ADDRESS_SK Asc 50000.0 2009-04-29T18:21:56
TPCDS.CUSTOMER_DEMOGRAPHICS DB2OE.IDX904300126530000 Regular Unique index Yes -1 -1 -1 No -1 -1.000 CD_DEMO_SK Asc 1920800.0 -
                      CD_GENDER OTHERS -1.0 -
                      CD_MARITAL_STATUS OTHERS -1.0 -
                      CD_EDUCATION_STATUS OTHERS -1.0 -
                      CD_PURCHASE_ESTIMATE OTHERS -1.0 -
                      CD_CREDIT_RATING OTHERS -1.0 -
                      CD_DEP_COUNT OTHERS -1.0 -
                      CD_DEP_EMPLOYED_COUNT OTHERS -1.0 -
                      CD_DEP_COLLEGE_COUNT OTHERS -1.0 -
  SYSIBM.SQL090429132201490 Regular Primary index No 8004 3 -1 No 1,920,800.000 1,920,800.000 CD_DEMO_SK Asc 1920800.0 2009-04-29T18:21:56

Back to table and index catalog info

Back to top



Query Tuner Report Log

The following log records the runtime information including the client version, server license, and which tuning advisors are used to obtain query tuning recommendations.

Server license entitlement and version: IBM Optim Query Workload Tuner for DB2, Version 2.2
Query Tuner client version: 2.2.0.1
Report completion timestamp: 2009-08-27T13:55:13.


Advisor Run Status
Statistics Success
Index Success
Query Success
Access path Success

Back to top