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-27T12:20:00
Database server configuration: jdbc:db2://9.30.68.24:50000/TPCD (SQL09051)
Estimated plan cost: 6,695.266 units
Critical problems: 1 statistics recommendations, 0 index recommendations, 0 query recommendations, and 0 access path warnings.
Best practices: 0 statistics recommendations, 0 index recommendations, 1 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 Query Avoid an asterisk (*) or a long column list in the SELECT clause. Consider replacing the asterisk (*) or the long column list for table STORE_SALES, DATE_DIM in the SELECT list with just the names of the required columns.Click here to see the affected query text.

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"."DATE_DIM" ON COLUMNS ( "D_DATE_SK" ) WITH DISTRIBUTION ON COLUMNS ( "D_MOY" NUM_FREQVALUES 15 NUM_QUANTILES 25 , "D_YEAR" NUM_FREQVALUES 15 NUM_QUANTILES 25 ) AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS
1 2 RUNSTATS ON TABLE "TPCDS"."STORE_SALES" WITH DISTRIBUTION ON COLUMNS ( "SS_SOLD_DATE_SK" NUM_FREQVALUES 15 NUM_QUANTILES 25 ) AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS TABLESAMPLE BERNOULLI ( 40.0 ) REPEATABLE ( 50 )

Back to recommended action


Create Index Statements

Advice Number Statement Number Statement Text

Back to recommended action

Back to top



SQL Statement

Input Query


SELECT * /* WARNING=Advice #2 */
    FROM TPCDS.STORE_SALES /* no. of rows=2880143 */
     , TPCDS.DATE_DIM /* no. of rows=73049 */
    WHERE TPCDS.DATE_DIM.D_YEAR = 2009
     AND TPCDS.DATE_DIM.D_MOY BETWEEN 1 AND 1 + 3
     AND TPCDS.STORE_SALES.SS_SOLD_DATE_SK = TPCDS.DATE_DIM.D_DATE_SK

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
- 4,869.103 6,695.266 13,580,466.000 519.813 0 2 1 0 4 2009-08-27T12:19:28 2 2 (40.0%) 3 (60.0%) 5




Database server transformed query for access plan


SELECT  Q2.SS_SOLD_DATE_SK  AS  "SS_SOLD_DATE_SK", 
     Q2.SS_SOLD_TIME_SK  AS  "SS_SOLD_TIME_SK", 
     Q2.SS_ITEM_SK  AS  "SS_ITEM_SK", 
     Q2.SS_CUSTOMER_SK  AS  "SS_CUSTOMER_SK", 
     Q2.SS_CDEMO_SK  AS  "SS_CDEMO_SK", 
     Q2.SS_HDEMO_SK  AS  "SS_HDEMO_SK", 
     Q2.SS_ADDR_SK  AS  "SS_ADDR_SK", 
     Q2.SS_STORE_SK  AS  "SS_STORE_SK", 
     Q2.SS_PROMO_SK  AS  "SS_PROMO_SK", 
     Q2.SS_TICKET_NUMBER  AS  "SS_TICKET_NUMBER", 
     Q2.SS_QUANTITY  AS  "SS_QUANTITY", 
     Q2.SS_WHOLESALE_COST  AS  "SS_WHOLESALE_COST", 
     Q2.SS_LIST_PRICE  AS  "SS_LIST_PRICE", 
     Q2.SS_SALES_PRICE  AS  "SS_SALES_PRICE", 
     Q2.SS_EXT_DISCOUNT_AMT  AS  "SS_EXT_DISCOUNT_AMT", 
     Q2.SS_EXT_SALES_PRICE  AS  "SS_EXT_SALES_PRICE", 
     Q2.SS_EXT_WHOLESALE_COST  AS  "SS_EXT_WHOLESALE_COST", 
     Q2.SS_EXT_LIST_PRICE  AS  "SS_EXT_LIST_PRICE", 
     Q2.SS_EXT_TAX  AS  "SS_EXT_TAX", 
     Q2.SS_COUPON_AMT  AS  "SS_COUPON_AMT", 
     Q2.SS_NET_PAID  AS  "SS_NET_PAID", 
     Q2.SS_NET_PAID_INC_TAX  AS  "SS_NET_PAID_INC_TAX", 
     Q2.SS_NET_PROFIT  AS  "SS_NET_PROFIT", 
     Q1.D_DATE_SK  AS  "D_DATE_SK", 
     Q1.D_DATE_ID  AS  "D_DATE_ID", 
     Q1.D_DATE  AS  "D_DATE", 
     Q1.D_MONTH_SEQ  AS  "D_MONTH_SEQ", 
     Q1.D_WEEK_SEQ  AS  "D_WEEK_SEQ", 
     Q1.D_QUARTER_SEQ  AS  "D_QUARTER_SEQ", 
     2009  AS  "D_YEAR", 
     Q1.D_DOW  AS  "D_DOW", 
     Q1.D_MOY  AS  "D_MOY", 
     Q1.D_DOM  AS  "D_DOM", 
     Q1.D_QOY  AS  "D_QOY", 
     Q1.D_FY_YEAR  AS  "D_FY_YEAR", 
     Q1.D_FY_QUARTER_SEQ  AS  "D_FY_QUARTER_SEQ", 
     Q1.D_FY_WEEK_SEQ  AS  "D_FY_WEEK_SEQ", 
     Q1.D_DAY_NAME  AS  "D_DAY_NAME", 
     Q1.D_QUARTER_NAME  AS  "D_QUARTER_NAME", 
     Q1.D_HOLIDAY  AS  "D_HOLIDAY", 
     Q1.D_WEEKEND  AS  "D_WEEKEND", 
     Q1.D_FOLLOWING_HOLIDAY  AS  "D_FOLLOWING_HOLIDAY", 
     Q1.D_FIRST_DOM  AS  "D_FIRST_DOM", 
     Q1.D_LAST_DOM  AS  "D_LAST_DOM", 
     Q1.D_SAME_DAY_LY  AS  "D_SAME_DAY_LY", 
     Q1.D_SAME_DAY_LQ  AS  "D_SAME_DAY_LQ", 
     Q1.D_CURRENT_DAY  AS  "D_CURRENT_DAY", 
     Q1.D_CURRENT_WEEK  AS  "D_CURRENT_WEEK", 
     Q1.D_CURRENT_MONTH  AS  "D_CURRENT_MONTH", 
     Q1.D_CURRENT_QUARTER  AS  "D_CURRENT_QUARTER", 
     Q1.D_CURRENT_YEAR  AS  "D_CURRENT_YEAR" 
FROM  TPCDS.DATE_DIM  AS  Q1, 
     TPCDS.STORE_SALES  AS  Q2 
WHERE  (Q1.D_MOY  <=  4) 
AND  (1  <=  Q1.D_MOY) 
AND  (Q1.D_YEAR  =  2009) 
AND  (Q2.SS_SOLD_DATE_SK  =  Q1.D_DATE_SK) 

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
- 2 (Q1.D_MOY <= 4) No Yes No 0.340000
- 3 (1 <= Q1.D_MOY) No Yes No 1.000000
- 4 (Q1.D_YEAR = 2009) No Yes No 0.004972
- 5 (Q2.SS_SOLD_DATE_SK = Q1.D_DATE_SK) Yes Yes No 0.000014

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
3 TPCDS.DATE_DIM 
(Q1)
DB2OE.IDX904300112320000 123.495 79.010 339,024.031 6.118
  • 2 (STOP)
  • 3 (START)
  • 4 (START, STOP)
-
4 TPCDS.STORE_SALES 
(Q2)
DB2OE.IDX904300115500000 39.428 53.649 170,963.188 4.160
  • 5 (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
2 TPCDS.DATE_DIM
(Correlation ID=Q1)
TPCDS.STORE_SALES
(Correlation ID=Q2)
NLJOIN No 4,869.103 6,695.266 13,580,466.000 519.813 5 -

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 2 referenced tables, 2 used indexes (40.0%), 3 unused indexes (60.0%), and a total of 5 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.DATE_DIM USERSPACE1 - 28 73,049 2,150 117 2009-08-19T12:28:46
TPCDS.STORE_SALES USERSPACE1 23 2,880,143 73,888 102 2009-08-19T12:29:10

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.DATE_DIM 0 D_DATE_SK INTEGER 4 N 73049.0
  1 D_DATE_ID CHARACTER 16 N -1.0
  2 D_DATE DATE 4 N -1.0
  3 D_MONTH_SEQ INTEGER 4 N -1.0
  4 D_WEEK_SEQ INTEGER 4 N -1.0
  5 D_QUARTER_SEQ INTEGER 4 N -1.0
  6 D_YEAR INTEGER 4 N 201.0
  7 D_DOW INTEGER 4 N -1.0
  8 D_MOY INTEGER 4 N 12.0
  9 D_DOM INTEGER 4 N -1.0
  10 D_QOY INTEGER 4 N -1.0
  11 D_FY_YEAR INTEGER 4 N -1.0
  12 D_FY_QUARTER_SEQ INTEGER 4 N -1.0
  13 D_FY_WEEK_SEQ INTEGER 4 N -1.0
  14 D_DAY_NAME CHARACTER 9 N -1.0
  15 D_QUARTER_NAME CHARACTER 6 N -1.0
  16 D_HOLIDAY CHARACTER 1 N -1.0
  17 D_WEEKEND CHARACTER 1 N -1.0
  18 D_FOLLOWING_HOLIDAY CHARACTER 1 N -1.0
  19 D_FIRST_DOM INTEGER 4 N -1.0
  20 D_LAST_DOM INTEGER 4 N -1.0
  21 D_SAME_DAY_LY INTEGER 4 N -1.0
  22 D_SAME_DAY_LQ INTEGER 4 N -1.0
  23 D_CURRENT_DAY CHARACTER 1 N -1.0
  24 D_CURRENT_WEEK CHARACTER 1 N -1.0
  25 D_CURRENT_MONTH CHARACTER 1 N -1.0
  26 D_CURRENT_QUARTER CHARACTER 1 N -1.0
  27 D_CURRENT_YEAR CHARACTER 1 N -1.0
TPCDS.STORE_SALES 0 SS_SOLD_DATE_SK INTEGER 4 N 1823.0
  1 SS_SOLD_TIME_SK INTEGER 4 N 45056.0
  2 SS_ITEM_SK INTEGER 4 N 18000.0
  3 SS_CUSTOMER_SK INTEGER 4 N 89856.0
  4 SS_CDEMO_SK INTEGER 4 N 236504.0
  5 SS_HDEMO_SK INTEGER 4 N 7168.0
  6 SS_ADDR_SK INTEGER 4 N 49920.0
  7 SS_STORE_SK INTEGER 4 N 6.0
  8 SS_PROMO_SK INTEGER 4 N 300.0
  9 SS_TICKET_NUMBER INTEGER 4 N 239623.0
  10 SS_QUANTITY INTEGER 4 N 100.0
  11 SS_WHOLESALE_COST DECIMAL (7,2) N 9920.0
  12 SS_LIST_PRICE DECIMAL (7,2) N 20096.0
  13 SS_SALES_PRICE DECIMAL (7,2) N 18048.0
  14 SS_EXT_DISCOUNT_AMT DECIMAL (7,2) N 119809.0
  15 SS_EXT_SALES_PRICE DECIMAL (7,2) N 287422.0
  16 SS_EXT_WHOLESALE_COST DECIMAL (7,2) N 318545.0
  17 SS_EXT_LIST_PRICE DECIMAL (7,2) N 438209.0
  18 SS_EXT_TAX DECIMAL (7,2) N 64256.0
  19 SS_COUPON_AMT DECIMAL (7,2) N 119809.0
  20 SS_NET_PAID DECIMAL (7,2) N 317461.0
  21 SS_NET_PAID_INC_TAX DECIMAL (7,2) N 429793.0
  22 SS_NET_PROFIT DECIMAL (7,2) N 360401.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.DATE_DIM DB2OE.IDX904300112320000 Regular Duplicate allowed Yes 2436 4 -1 No 201 73,049.000 D_YEAR Asc 201.0 2009-08-19T12:28:46
                      D_LAST_DOM Asc -1.0 2009-08-19T12:28:46
                      D_FIRST_DOM Asc -1.0 2009-08-19T12:28:46
                      D_FOLLOWING_HOLIDAY Asc -1.0 2009-08-19T12:28:46
                      D_WEEKEND Asc -1.0 2009-08-19T12:28:46
                      D_HOLIDAY Asc -1.0 2009-08-19T12:28:46
                      D_QUARTER_NAME Asc -1.0 2009-08-19T12:28:46
                      D_DAY_NAME Asc -1.0 2009-08-19T12:28:46
                      D_FY_WEEK_SEQ Asc -1.0 2009-08-19T12:28:46
                      D_FY_QUARTER_SEQ Asc -1.0 2009-08-19T12:28:46
                      D_FY_YEAR Asc -1.0 2009-08-19T12:28:46
                      D_MOY Asc 12.0 2009-08-19T12:28:46
                      D_QOY Asc -1.0 2009-08-19T12:28:46
                      D_DOM Asc -1.0 2009-08-19T12:28:46
                      D_DOW Asc -1.0 2009-08-19T12:28:46
                      D_QUARTER_SEQ Asc -1.0 2009-08-19T12:28:46
                      D_WEEK_SEQ Asc -1.0 2009-08-19T12:28:46
                      D_MONTH_SEQ Asc -1.0 2009-08-19T12:28:46
                      D_DATE Asc -1.0 2009-08-19T12:28:46
                      D_DATE_ID Asc -1.0 2009-08-19T12:28:46
                      D_DATE_SK Asc 73049.0 2009-08-19T12:28:46
                      D_CURRENT_YEAR Asc -1.0 2009-08-19T12:28:46
                      D_CURRENT_QUARTER Asc -1.0 2009-08-19T12:28:46
                      D_CURRENT_MONTH Asc -1.0 2009-08-19T12:28:46
                      D_CURRENT_WEEK Asc -1.0 2009-08-19T12:28:46
                      D_CURRENT_DAY Asc -1.0 2009-08-19T12:28:46
                      D_SAME_DAY_LQ Asc -1.0 2009-08-19T12:28:46
                      D_SAME_DAY_LY Asc -1.0 2009-08-19T12:28:46
  DB2OE.IDX904292302380000 Regular Duplicate allowed No 387 3 -1 No 201 73,049.000 D_YEAR Asc 201.0 2009-08-19T12:28:46
                      D_DATE_SK Asc 73049.0 2009-08-19T12:28:46
  SYSIBM.SQL090429132201570 Regular Primary index No 305 3 -1 No 73,049.000 73,049.000 D_DATE_SK Asc 73049.0 2009-08-19T12:28:46
TPCDS.STORE_SALES DB2OE.IDX904300115500000 Regular Duplicate allowed Yes 84711 5 -1 No 1,823.000 2,880,143.000 SS_SOLD_DATE_SK Asc 1823.0 2009-08-19T12:29:10
                      SS_EXT_DISCOUNT_AMT Asc 119809.0 2009-08-19T12:29:10
                      SS_SALES_PRICE Asc 18048.0 2009-08-19T12:29:10
                      SS_LIST_PRICE Asc 20096.0 2009-08-19T12:29:10
                      SS_WHOLESALE_COST Asc 9920.0 2009-08-19T12:29:10
                      SS_QUANTITY Asc 100.0 2009-08-19T12:29:10
                      SS_TICKET_NUMBER Asc 239623.0 2009-08-19T12:29:10
                      SS_PROMO_SK Asc 300.0 2009-08-19T12:29:10
                      SS_STORE_SK Asc 6.0 2009-08-19T12:29:10
                      SS_ADDR_SK Asc 49920.0 2009-08-19T12:29:10
                      SS_HDEMO_SK Asc 7168.0 2009-08-19T12:29:10
                      SS_NET_PROFIT Asc 360401.0 2009-08-19T12:29:10
                      SS_CDEMO_SK Asc 236504.0 2009-08-19T12:29:10
                      SS_CUSTOMER_SK Asc 89856.0 2009-08-19T12:29:10
                      SS_ITEM_SK Asc 18000.0 2009-08-19T12:29:10
                      SS_SOLD_TIME_SK Asc 45056.0 2009-08-19T12:29:10
                      SS_NET_PAID_INC_TAX Asc 429793.0 2009-08-19T12:29:10
                      SS_NET_PAID Asc 317461.0 2009-08-19T12:29:10
                      SS_COUPON_AMT Asc 119809.0 2009-08-19T12:29:10
                      SS_EXT_TAX Asc 64256.0 2009-08-19T12:29:10
                      SS_EXT_LIST_PRICE Asc 438209.0 2009-08-19T12:29:10
                      SS_EXT_WHOLESALE_COST Asc 318545.0 2009-08-19T12:29:10
                      SS_EXT_SALES_PRICE Asc 287422.0 2009-08-19T12:29:10
  SYSIBM.SQL090429132203310 Regular Primary index No 15239 3 -1 No 18,000.000 2,880,143.000 SS_ITEM_SK Asc 18000.0 2009-08-19T12:29:10
                      SS_TICKET_NUMBER Asc 239623.0 2009-08-19T12:29:10

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-27T12:20:00.


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

Back to top