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 catalog 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-27T16:29:02
Database server configuration: jdbc:db2://dtec267.svldev.svl.ibm.com:446/STLEC1 (DSN09015)
Estimated plan cost: 21,359,412.443 units
Critical problems: 1 statistics recommendations, 0 index recommendations, 0 query recommendations, and 0 access path warnings.
Best practices: 1 statistics recommendations, 0 index recommendations, 1 query recommendations, and 5 access path warnings.

Advice Number Advice Type Tuning Recommendation Description
1 Statistics CRITICAL: Repair statistics: Query Tuner found problems with the repair statistics for this query. Use RUNSTATS to gather missing statistics. Recollect conflicting statistics and potential obsolete statistics. Collect statistics for potential data skew and data correlation problems. Important: if statistics are missing, Query tuner estimates subsequent recommendations based on database default statistics. Click here to review the recommended RUNSTATS script.
2 Statistics 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.
3 Query Rewrite the predicate containing indexed column O_ORDERDATE to a indexable or stage 1 (sargable )predicate. The predicate on column O_ORDERDATE in table SYSADM.ORDER has better selectivity than other predicates in this query. You can rewrite the predicate as an indexable or a stage 1 predicate: ( SYSADM.ORDER.O_ORDERDATE + 3 MONTHS ) < DATE( '1993-10-01' ) so that the predicate can filter out unqualified rows via using a table or an index scan.Click here to see the affected query text.
4 Access path Avoid reading all index keys on an index scan (QBLOCKNO = 1, PLANNO = 1). The table SYSADM.ORDER is accessed by a non-matching index scan (QBLOCKNO = 1, PLANNO = 1). If a table is accessed by non-matching index scan, then all the index keys and their RIDs are read. When a large number of keys and RIDs are accessed, DB2 might be using an inefficient access path. Consider run the statistics advisor or run the index advisor to determine whether creating an index might improve the access path.Click here to see the access plan operator.
5 Access path Consider providing indexable predicates on columns L_SHIPDATE. A matching index scan (QBLOCKNO = 1, PLANNO = 4) is used to access the table SYSADM.LINEITEM, but the predicates do not reference key columns L_SHIPDATE. If these missing key columns were referenced in indexable predicates, DB2 might be able to further narrow the index tree search ranges. Click here to see the access plan operator.
6 Access path Avoid sorting (QBLOCKNO = 1, PLANNO = 4) on a large number of records. A sort is used. When a large number of records are returned before sorting, DB2 might be using an inefficient access path. Consider rewriting the query or designing an index to avoid the sort if possible. Click here to see the access plan operator.
This Access path recommendation also applies to the following objects.
7       - (QBLOCKNO = 1, PLANNO = 5)
8       - (QBLOCKNO = 1, PLANNO = 6)

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 INDEX("SYSADM"."PXN@NKNMRK" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."UXN@NMRKNK" KEYCARD) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
1 2 RUNSTATS TABLESPACE "DB4LINEI"."TSLINEI" TABLE("SYSADM"."LINEITEM") SAMPLE 40 COLUMN("L_RETURNFLAG") COLGROUP("L_RETURNFLAG") FREQVAL COUNT 15 SORTNUM 4 INDEX("SYSADM"."SXL@PKSKOKEPDSQN" KEYCARD, "SYSADM"."PXL@OKSDRFSKEPDC" KEYCARD FREQVAL NUMCOLS 1 COUNT 15) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
1 3 RUNSTATS INDEX("SYSADM"."PXC@CKNKMS" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."UXC@NKCK" FREQVAL NUMCOLS 1 COUNT 15) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
1 4 RUNSTATS TABLESPACE "DB4ORDER"."TSORDER" TABLE("SYSADM"."ORDER") SAMPLE 40 COLUMN("O_ORDERDATE") COLGROUP("O_ORDERDATE") FREQVAL COUNT 15 HISTOGRAM NUMQUANTILES 25 SORTNUM 4 INDEX("SYSADM"."PXO@OKODCKSPOP" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."UXO#CLOKOD" KEYCARD, "SYSADM"."UXO@CKOKODSP" KEYCARD) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
2 5 RUNSTATS TABLESPACE "DB4NATN"."TSNATION" INDEX("SYSADM"."PXN@NKNMRK" KEYCARD FREQVAL NUMCOLS 1 COUNT 15 HISTOGRAM NUMCOLS 1 NUMQUANTILES 25, "SYSADM"."UXN@NMRKNK" KEYCARD) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
2 6 RUNSTATS TABLESPACE "DB4CUST"."TSCUST" INDEX("SYSADM"."PXC@CKNKMS" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."UXC@NKCK" FREQVAL NUMCOLS 1 COUNT 15 HISTOGRAM NUMCOLS 1 NUMQUANTILES 25) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
2 7 RUNSTATS TABLESPACE "DB4LINEI"."TSLINEI" TABLE("SYSADM"."LINEITEM") SAMPLE 40 COLUMN("L_RETURNFLAG") COLGROUP("L_RETURNFLAG") FREQVAL COUNT 15 SORTNUM 4 INDEX("SYSADM"."PXL@OKSDRFSKEPDC" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."SXL@PKSKOKEPDSQN" KEYCARD) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE
2 8 RUNSTATS TABLESPACE "DB4ORDER"."TSORDER" TABLE("SYSADM"."ORDER") SAMPLE 40 COLUMN("O_ORDERDATE") COLGROUP("O_ORDERDATE") FREQVAL COUNT 15 HISTOGRAM NUMQUANTILES 25 SORTNUM 4 INDEX("SYSADM"."PXO@OKODCKSPOP" KEYCARD FREQVAL NUMCOLS 1 COUNT 15, "SYSADM"."UXO#CLOKOD" KEYCARD, "SYSADM"."UXO@CKOKODSP" KEYCARD) SHRLEVEL CHANGE REPORT YES UPDATE ALL HISTORY NONE

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 SYSADM.CUSTOMER.C_CUSTKEY
     , SYSADM.CUSTOMER.C_NAME
     , SUM( SYSADM.LINEITEM.L_EXTENDEDPRICE ) AS REVENUE
     , SYSADM.CUSTOMER.C_ACCTBAL
     , SYSADM.NATION.N_NAME
     , SYSADM.CUSTOMER.C_ADDRESS
     , SYSADM.CUSTOMER.C_PHONE
     , SYSADM.CUSTOMER.C_COMMENT
    FROM SYSADM.NATION
     , SYSADM.CUSTOMER
     , SYSADM.ORDER
     , SYSADM.LINEITEM
    WHERE ( SYSADM.LINEITEM.L_RETURNFLAG = 'R'
     AND ( SYSADM.ORDER.O_ORDERDATE + 3 MONTHS ) < DATE( '1993-10-01' ) /* WARNING=Advice #3 */
     AND SYSADM.ORDER.O_ORDERDATE >= DATE( '1993-10-01' )
     AND SYSADM.CUSTOMER.C_NATIONKEY = SYSADM.NATION.N_NATIONKEY
     AND SYSADM.CUSTOMER.C_CUSTKEY = SYSADM.ORDER.O_CUSTKEY
     AND SYSADM.LINEITEM.L_ORDERKEY = SYSADM.ORDER.O_ORDERKEY
     )
    GROUP BY SYSADM.CUSTOMER.C_CUSTKEY
     , SYSADM.CUSTOMER.C_NAME
     , SYSADM.CUSTOMER.C_ACCTBAL
     , SYSADM.CUSTOMER.C_PHONE
     , SYSADM.NATION.N_NAME
     , SYSADM.CUSTOMER.C_ADDRESS
     , SYSADM.CUSTOMER.C_COMMENT
    ORDER BY SUM( SYSADM.LINEITEM.L_EXTENDEDPRICE ) DESC

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 referenced catalog tables and catalog index usage with percentage of catalog indexes used and percentage of catalog 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 Referenced Catalog Tables Catalog Index Usage
Total Cost Total CPU Total IO Table Scan Index Scan Join Sort Predicate Used Indexes (%) Unused Indexes (%) Existing Indexes
1 22,022,864.000 21,359,412.443 18,007,488.000 5,359,966.000 0 4 3 3 7 2009-08-27T16:28:37 4 4 (44.4%) 5 (55.6%) 9




Database server transformed query for access plan



SELECT SYSADM.CUSTOMER.C_CUSTKEY
     , SYSADM.CUSTOMER.C_NAME
     , SUM( SYSADM.LINEITEM.L_EXTENDEDPRICE ) AS REVENUE
     , SYSADM.CUSTOMER.C_ACCTBAL
     , SYSADM.NATION.N_NAME
     , SYSADM.CUSTOMER.C_ADDRESS
     , SYSADM.CUSTOMER.C_PHONE
     , SYSADM.CUSTOMER.C_COMMENT
    FROM SYSADM.NATION
     , SYSADM.CUSTOMER
     , SYSADM.ORDER
     , SYSADM.LINEITEM
    WHERE ( SYSADM.LINEITEM.L_RETURNFLAG = 'R'
     AND ADD_MONTHS( SYSADM.ORDER.O_ORDERDATE
     , ( 3 )
     ) < DATE( '1993-10-01' )
     AND SYSADM.ORDER.O_ORDERDATE >= '1993-10-01'
     AND SYSADM.CUSTOMER.C_NATIONKEY = SYSADM.NATION.N_NATIONKEY
     AND SYSADM.CUSTOMER.C_CUSTKEY = SYSADM.ORDER.O_CUSTKEY
     AND SYSADM.LINEITEM.L_ORDERKEY = SYSADM.ORDER.O_ORDERKEY
     )
    GROUP BY SYSADM.CUSTOMER.C_CUSTKEY
     , SYSADM.CUSTOMER.C_NAME
     , SYSADM.CUSTOMER.C_ACCTBAL
     , SYSADM.CUSTOMER.C_PHONE
     , SYSADM.NATION.N_NAME
     , SYSADM.CUSTOMER.C_ADDRESS
     , SYSADM.CUSTOMER.C_COMMENT
    ORDER BY SUM( SYSADM.LINEITEM.L_EXTENDEDPRICE ) DESC

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
1:1 4 SYSADM.ORDER.O_ORDERDATE>='1993-10-01' No Yes No 0.734100
1:1 5 ADD_MONTHS(SYSADM.ORDER.O_ORDERDATE,3) No Yes No 0.333333
1:2 2 SYSADM.CUSTOMER.C_CUSTKEY=SYSADM.ORDER.O_CUSTKEY Yes Yes No 0.000000
1:3 7 SYSADM.CUSTOMER.C_NATIONKEY=SYSADM.NATION.N_NATIONKEY Yes Yes No 0.040000
1:4 3 SYSADM.LINEITEM.L_ORDERKEY=SYSADM.ORDER.O_ORDERKEY Yes Yes No 0.000000
1:4 6 SYSADM.LINEITEM.L_RETURNFLAG='R' No Yes No 0.333333

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
1:1 SYSADM.ORDER SYSADM.UXO@CKOKODSP 11,011,501.000 1,034,478.560 27,223,130,100.000 0.000
  • 4 (Screening)
  • 5 (Stage 2)
4
1:2 SYSADM.CUSTOMER SYSADM.PXC@CKNKMS 1.000 1,076,392.000 28,324,700,200.000 27.069
  • 2 (Matching)
-
1:3 SYSADM.NATION SYSADM.PXN@NKNMRK 1.000 3,944,777.000 103,808,500,000.000 27.069
  • 7 (Matching)
-
1:4 SYSADM.LINEITEM SYSADM.PXL@OKSDRFSKEPDC 2.000 9,042,966.000 179,258,982,000.000 1,115,561.000
  • 3 (Matching)
  • 6 (Screening)
6

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
1:4 SYSADM.NATION
(Operator ID=1:3)
SYSADM.LINEITEM
(Operator ID=1:4)
NLJOIN No 2.000 9,042,966.000 179,258,982,000.000 1,115,561.000 3 -
1:3 SYSADM.CUSTOMER
(Operator ID=1:2)
SYSADM.NATION
(Operator ID=1:3)
NLJOIN No 1.000 3,944,777.000 103,808,500,000.000 27.069 7 -
1:2 SYSADM.ORDER
(Operator ID=1:1)
SYSADM.CUSTOMER
(Operator ID=1:2)
HyBRID JOIN No 1.000 1,076,392.000 28,324,700,200.000 27.069 2 -

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 4 referenced tables, 4 used indexes (44.4%), 5 unused indexes (55.6%), and a total of 9 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
SYSADM.CUSTOMER DB4CUST.TSCUST - 8 4,500,000 0 -1 0001-01-01T00:00:00
SYSADM.LINEITEM DB4LINEI.TSLINEI - 16 179,998,372 0 -1 2008-11-14T16:23:22
SYSADM.NATION DB4NATN.TSNATION - 4 25 0 3 0001-01-01T00:00:00
SYSADM.ORDER DB4ORDER.TSORDER - 9 45,000,000 0 -1 0001-01-01T00:00:00

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
SYSADM.CUSTOMER 1 C_CUSTKEY INTEGER 4 N 4,500,000
  2 C_NAME VARCHAR 25 N 4,500,000
  3 C_ADDRESS VARCHAR 40 N 3,932,160
  4 C_NATIONKEY INTEGER 4 N 25
  5 C_PHONE CHAR 15 N 4,194,304
  6 C_ACCTBAL FLOAT 8 N 1,130,496
  7 C_MKTSEGMENT CHAR 10 N 5
  8 C_COMMENT VARCHAR 117 N 4,500,000
SYSADM.LINEITEM 1 L_ORDERKEY INTEGER 4 N 45,000,000
  2 L_PARTKEY INTEGER 4 N 6,000,000
  3 L_SUPPKEY INTEGER 4 N 303,104
  4 L_LINENUMBER INTEGER 4 N 7
  5 L_QUANTITY FLOAT 8 N 50
  6 L_EXTENDEDPRICE FLOAT 8 N 2,326,528
  7 L_DISCOUNT FLOAT 8 N 11
  8 L_TAX FLOAT 8 N 9
  9 L_RETURNFLAG CHAR 1 N 3
  10 L_LINESTATUS CHAR 1 N 2
  11 L_SHIPDATE DATE 4 N 2,304
  12 L_COMMITDATE DATE 4 N 2,240
  13 L_RECEIPTDATE DATE 4 N 2,400
  14 L_SHIPINSTRUCT CHAR 25 N 4
  15 L_SHIPMODE CHAR 10 N 7
  16 L_COMMENT VARCHAR 44 N 157,286,400
SYSADM.NATION 1 N_NATIONKEY INTEGER 4 N 25
  2 N_NAME CHAR 25 N 25
  3 N_REGIONKEY INTEGER 4 N 5
  4 N_COMMENT VARCHAR 152 N 25
SYSADM.ORDER 1 O_ORDERKEY INTEGER 4 N 45,000,000
  2 O_CUSTKEY INTEGER 4 N 0
  3 O_ORDERSTATUS CHAR 1 N 3
  4 O_TOTALPRICE FLOAT 8 N 23,068,672
  5 O_ORDERDATE DATE 4 N 2,304
  6 O_ORDERPRIORITY CHAR 15 N 5
  7 O_CLERK CHAR 15 N 0
  8 O_SHIPPRIORITY INTEGER 4 N 1
  9 O_COMMENT VARCHAR 79 N 42,991,616

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
SYSADM.CUSTOMER SYSADM.PXC@CKNKMS Type 2 Unique index Yes 29040 3 100.0 Yes 4,500,000 4,500,000 C_CUSTKEY Asc 4,500,000 0001-01-01T00:00:00
                      C_NATIONKEY Asc 25 0001-01-01T00:00:00
                      C_MKTSEGMENT Asc 5 0001-01-01T00:00:00
  SYSADM.UXC@NKCK Type 2 Unique index No 17858 3 100.0 No 25 4,500,000 C_NATIONKEY Asc 25 0001-01-01T00:00:00
                      C_CUSTKEY Asc 4,500,000 0001-01-01T00:00:00
SYSADM.LINEITEM SYSADM.PXL@OKSDRFSKEPDC Type 2 Duplicate allowed Yes 1747586 3 100.0 Yes 45,000,000 179,998,372 L_ORDERKEY Asc 45,000,000 0001-01-01T00:00:00
                      L_SHIPDATE Asc 2,304 0001-01-01T00:00:00
                      L_RETURNFLAG Asc 3 0001-01-01T00:00:00
                      L_SUPPKEY Asc 303,104 0001-01-01T00:00:00
                      L_EXTENDEDPRICE Asc 2,326,528 0001-01-01T00:00:00
                      L_DISCOUNT Asc 11 0001-01-01T00:00:00
  SYSADM.SXL@PKSKOKEPDSQN Type 2 Duplicate allowed No 2195103 4 50.0 No 6,000,000 179,998,372 L_PARTKEY Asc 6,000,000 0001-01-01T00:00:00
                      L_SUPPKEY Asc 303,104 0001-01-01T00:00:00
                      L_ORDERKEY Asc 45,000,000 0001-01-01T00:00:00
                      L_EXTENDEDPRICE Asc 2,326,528 0001-01-01T00:00:00
                      L_DISCOUNT Asc 11 0001-01-01T00:00:00
                      L_QUANTITY Asc 50 0001-01-01T00:00:00
SYSADM.NATION SYSADM.PXN@NKNMRK Type 2 Unique index Yes 25 2 100.0 Yes 25 25 N_NATIONKEY Asc 25 0001-01-01T00:00:00
                      N_NAME Asc 25 0001-01-01T00:00:00
                      N_REGIONKEY Asc 5 0001-01-01T00:00:00
  SYSADM.UXN@NMRKNK Type 2 Unique index No -1 -1 0 No -1 -1 N_NAME Asc 25 0001-01-01T00:00:00
                      N_REGIONKEY Asc 5 0001-01-01T00:00:00
                      N_NATIONKEY Asc 25 0001-01-01T00:00:00
SYSADM.ORDER SYSADM.UXO@CKOKODSP Type 2 Unique index Yes 0 2 0 No 0 0 O_CUSTKEY Asc 0 2009-03-31T12:10:59
                      O_ORDERKEY Asc 45,000,000 2009-03-31T12:10:59
                      O_ORDERDATE Asc 2,304 2009-03-31T12:10:59
                      O_SHIPPRIORITY Asc 1 2009-03-31T12:10:59
  SYSADM.PXO@OKODCKSPOP Type 2 Unique index No 436920 3 100.0 Yes 45,000,000 45,000,000 O_ORDERKEY Asc 45,000,000 0001-01-01T00:00:00
                      O_ORDERDATE Asc 2,304 0001-01-01T00:00:00
                      O_CUSTKEY Asc 0 0001-01-01T00:00:00
                      O_SHIPPRIORITY Asc 1 0001-01-01T00:00:00
                      O_ORDERPRIORITY Asc 5 0001-01-01T00:00:00
  SYSADM.UXO#CLOKOD Type 2 Unique index No 0 2 0 No 0 0 O_CLERK Asc 0 2009-03-31T12:10:59
                      O_ORDERKEY Asc 45,000,000 2009-03-31T12:10:59
                      O_ORDERDATE Asc 2,304 2009-03-31T12:10:59

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-27T16:29:02.


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

Back to top