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.
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. |
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 StatementsRecommended Action
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 ) |
Create Index Statements
Advice Number | Statement Number | Statement Text |
---|
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 |
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) |
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.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 |
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 | - | |
4 | TPCDS.STORE_SALES (Q2) |
DB2OE.IDX904300115500000 | 39.428 | 53.649 | 170,963.188 | 4.160 |
|
- |
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 | - |
Method for applying the predicate.
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 |
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 |
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 |
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 |