Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DB2 SQL Query Tuning

DB2 SQL Query Tuning

Systematic step-by-step approach for query-tuning
- Monitoring the resources consumed during query execution
- Analysing the explain plan to get insights
- Various examples

harjinder-hari

December 17, 2014
Tweet

More Decks by harjinder-hari

Other Decks in Technology

Transcript

  1. © 2012 IBM Corporation 1 DB2 LUW SQL Query Tuning

    Harjindersingh Mistry, DB2 Query Optimizer Development, IBM
  2. © 2012 IBM Corporation 2 Need For Query Tuning 

    Databases are getting larger  Systems are getting more complex  Queries are getting extremely complex  Users are demanding faster query response
  3. © 2012 IBM Corporation 3 Agenda  Systematic step-by-step approach

    for query-tuning  Monitoring the resources consumed during query execution  Analyzing the explain plan to get insights  Various examples
  4. © 2012 IBM Corporation 4 Important Resources For Query Execution

    Query Compilation Query Execution Catalog Cache Package Cache Lock List Buffer Pool Others Shared Sortheap Memory Database Global Memory Sort Heap Statement Heap Others Agent Private Memory Tablespaces Temp User System
  5. © 2012 IBM Corporation 5 Resource: Statement Heap Query Compilation

    Query Execution Catalog Cache Package Cache Lock List Buffer Pool Others Shared Sortheap Memory Database Global Memory Sort Heap Statement Heap Others Agent Private Memory Tablespaces Temp User System
  6. © 2012 IBM Corporation 6 Resource: Statement Heap  When

    statement heap is not enough:  The compiler will reduce the optimization.  Mainly, it will use a different join enumeration method.  In explain plan ( db2exfmt output ), the following message will be shown: SQLCA : (Warning SQLCA from compile) SQLCODE 437; Function SQLNO***; Message token '1'; Warning 'None'  In this situation, the access plan could be sub-optimal.  Increasing statement heap should help.
  7. © 2012 IBM Corporation 7 Resource: Sort Heap Query Compilation

    Query Execution Catalog Cache Package Cache Lock List Buffer Pool Others Shared Sortheap Memory Database Global Memory Sort Heap Statement Heap Others Agent Private Memory Tablespaces Temp User System
  8. © 2012 IBM Corporation 8 Resource: Sort Heap  During

    query execution, DB2 may run out of sort heap.  This can be verified by monitornig the query execution by using:  db2 snapshots  db2 SQL monitoring functions  The symptoms are:  sort overflows  rows written for a SELECT query  temporary data logical/physical reads  hash loops  hash join overflows  Before increasing sort heap, the following should be investigated:  in explain plan, is input cardinality of SORT/HSJOIN underestimated ?  provide more statistics to help optimizer  can we create an index ?  for SORT, index can be considered on sort columns  for HSJOIN, index can be considered on join columns  are there multiple concurrent queries using lot of sortheap ?  consider tuning shared sortheap memory  consider WLM to control concurrency
  9. © 2012 IBM Corporation 9 Resource: Buffer Pool Query Compilation

    Query Execution Catalog Cache Package Cache Lock List Buffer Pool Others Shared Sortheap Memory Database Global Memory Sort Heap Statement Heap Others Agent Private Memory Tablespaces Temp User System
  10. © 2012 IBM Corporation 10 Resource: Buffer Pool  There

    may be unnecessary logical as well as physical IOs.  This can be verified by monitornig the query execution by using:  db2 snapshots  db2 SQL monitoring functions  The symptoms are:  buffer pool data logical/physical reads  buffer pool index logical/physical reads  asynchronous data/index reads  The following should be investigated :  in explain plan, is input cardinality of NLJOIN underestimated ?  provide more statistics to help optimizer  there may be a huge opportunity of indexing/partitioning  find expensive operations from explain plan  consider the relevant predicates to find columns on which indexing/partitioning can be done  are there multiple concurrent queries using lot of sortheap ?  consider WLM to control concurrency
  11. © 2012 IBM Corporation 11 Resource: CPU Query Compilation Query

    Execution Catalog Cache Package Cache Lock List Others Shared Sortheap Memory Database Global Memory Sort Heap Statement Heap Others Agent Private Memory Tablespaces Temp User System Buffer Pool
  12. © 2012 IBM Corporation 12 Resource: CPU  Some predicates

    may be applied sub-optimally.  That can consume a lot of CPU.  This can be verified by monitoring the query execution by using:  db2 snapshots  db2 SQL monitoring functions  OS monitoring tools like vmstat  The symptoms are:  all ( or most of ) the IOs are logical  high user-CPU utilization  The following should be investigated:  in explain plan, is there any sargable/residual predicate ?  consider index on relevant columns  consider rewriting the query
  13. © 2012 IBM Corporation 13 Explain Plan Analysis  Along

    with various monitoring data, the explain plan can provide deep insights of query execution.  The recommended way to collect explain plan is to use db2exfmt:  connect to db  set current explain mode explain  < query >  set current explain mode no  db2exfmt -d db -1 -o < output file >
  14. © 2012 IBM Corporation 14 Explain Plan Analysis: General Approach

     Step 1: Take a quick overview of query and environment  Step 2: Check optimizer's cardinality estimations  Step 3: Check access-method and/or join-method of most expensive operation
  15. © 2012 IBM Corporation 15 Explain Plan Analysis: General Approach

     Step 1: Take a quick overview of query and environment  Understand the original statement and optionally optimized statement from db2exfmt output  Look at the global settings like buffer-pool, sort-heap, optimization-level etc.  Look at the objects used in the query and check if there are signs of poor maintenance/design e.g. existance of overflow records
  16. © 2012 IBM Corporation 16 Explain Plan Analysis: General Approach

     Step 2: Check optimizer's cardinality estimations  Once the query and environment are found to be ok, then check for cardinality estimations.  This is the most important step. It requires knowledge about underlying data.  Look for cardinality underestimation's clue: small numbers less than 1 like 3.00579e-05  Can that small number have a big impact ?  For example:  is that small number on the outer side ( LHS child ) of NLJOIN operation and inner side ( RHS child ) is a big table ?  is that small number on the build side ( RHS child ) of HSJOIN operation ?
  17. © 2012 IBM Corporation 17 Explain Plan Analysis: General Approach

     Step 2: Check optimizer's cardinality estimations ( Contd … )  If the cardinality underestimation can have a big impact, then confirm the actual number of records by using section actuals.  Once, it is confirmed that optimizer had underestimated cardinality, then analyze the relevant predicates and corresponding data.  In most of the cases, collecting additional statistics resolves the issue.  These additional statistics are statistical view and column group statistics. More on them is described via examples later.
  18. © 2012 IBM Corporation 18 Explain Plan Analysis: General Approach

     Step 3: Check access-method and/or join-method of most expensive operation  Once the cardinality estimations are found to be ok, then look for opportunities of reducing the cost of query execution.  First, locate the most expensive operation in the explain plan.  In most of the cases, indexing and/or partitioning strategies resolve the issue.  Indexing and partitioning is further described via examples later.
  19. © 2012 IBM Corporation 19 Agenda  Systematic step-by-step approach

    for query-tuning  Monitoring the resources consumed during query execution  Analyzing the explain plan to get insights  Various examples
  20. © 2012 IBM Corporation 20 Example: Statistical View  A

    query performance is poor. The CPU consumption is high and there are many logical IOs. There was no major change either in system or application.  Query: select i_item_id, i_item_desc, s_state, count(ss_quantity) as store_sales_quantitycount, avg(ss_quantity) as store_sales_quantityave, stddev(ss_quantity) as store_sales_quantitystdev, stddev(ss_quantity)/avg(ss_quantity) as store_sales_quantitycov, count(sr_return_quantity) as_store_returns_quantitycount, avg(sr_return_quantity) as_store_returns_quantityave, stddev(sr_return_quantity) as_store_returns_quantitystdev, stddev(sr_return_quantity)/avg(sr_return_quantity) as store_returns_quantitycov, count(cs_quantity) as catalog_sales_quantitycount, avg(cs_quantity) as catalog_sales_quantityave, stddev(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitystdev, stddev(cs_quantity)/avg(cs_quantity) as catalog_sales_quantitycov from store_sales, store_returns, catalog_sales, date_dim d1, date_dim d2, date_dim d3, store, item where d1.d_quarter_name = '2001Q1' and d1.d_date_sk = ss_sold_date_sk and i_item_sk = ss_item_sk and s_store_sk = ss_store_sk and ss_customer_sk = sr_customer_sk and ss_item_sk = sr_item_sk and ss_ticket_number = sr_ticket_number and sr_returned_date_sk = d2.d_date_sk and d2.d_quarter_name in ('2001Q1', '2001Q2', '2001Q3') and sr_customer_sk = cs_bill_customer_sk and sr_item_sk = cs_item_sk and cs_sold_date_sk = d3.d_date_sk and d3.d_quarter_name in ('2001Q1', '2001Q2', '2001Q3') group by i_item_id, i_item_desc, s_state order by i_item_id, i_item_desc, s_state fetch first 100 rows only
  21. © 2012 IBM Corporation 21 Example: Statistical View  Access

    plan ( snippet ):  Note the small numbers in NLJOIN(9) and NLJOIN(10)  This is a clue for a possibility of cardinality underestimation.  As there are NLJOINs involved and big fact tables are in the inner, let us check the actual cardinality. ... 1.38112e­05 ^NLJOIN ( 9) 30963 13550.8 /­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­\ 0.0113066 0.00122151 ^NLJOIN FETCH ( 10) ( 16) 30949.4 20.3389 13548.8 3 /­­­­­­­­­­­­­­+­­­­­­­­­­­­­­\ /­­­+­­­­\ 1053.61 1.07313e­05 1 73049 ^HSJOIN FETCH IXSCAN TABLE: HARMISTR ( 11) ( 14) ( 17) DATE_DIM 9536.15 20.3465 13.5662 Q9 10388 3 2 /­­­­­+­­­­­­\ /­­­+­­­­\ | 287514 267.691 1 2.8804e+06 73049 TBSCAN TBSCAN IXSCAN TABLE: HARMISTR INDEX: SYSIBM ( 12) ( 13) ( 15) STORE_SALES SQL131219050845050 7121.4 2388.85 13.5738 Q12 Q9 7776 2612 2 | | | 287514 73049 2.8804e+06 TABLE: HARMISTR TABLE: HARMISTR INDEX: SYSIBM STORE_RETURNS DATE_DIM SQL131219050957440 Q11 Q8 Q12
  22. © 2012 IBM Corporation 22 Example: Statistical View  Actual

    cardinality: Rows Rows Actual RETURN ( 1) Cost I/O | ... 1.38112e­05 5958 ^NLJOIN ( 9) 30963 NA /­­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­\ 0.0113066 0.00122151 30229 0.197096 ^NLJOIN FETCH ( 10) ( 16) 30949.4 20.3389 NA NA /­­­­­­­­­­­­­­+­­­­­­­­­­­­­­\ /­­­+­­­­\ 1053.61 1.07313e­05 1 73049 40686 0.742983 0.976943 NA ^HSJOIN FETCH IXSCAN TABLE: HARMISTR ( 11) ( 14) ( 17) DATE_DIM 9536.15 20.3465 13.5662 Q9 NA NA NA /­­­­­+­­­­­­\ /­­­+­­­­\ | 287514 267.691 1 2.8804e+06 73049 287514 274 1 NA NA TBSCAN TBSCAN IXSCAN TABLE: HARMISTR INDEX: SYSIBM ( 12) ( 13) ( 15) STORE_SALES SQL131219050845050 7121.4 2388.85 13.5738 Q12 Q9 NA NA NA | | | 287514 73049 2.8804e+06 NA NA NA TABLE: HARMISTR TABLE: HARMISTR INDEX: SYSIBM STORE_RETURNS DATE_DIM SQL131219050957440 Q11 Q8 Q12
  23. © 2012 IBM Corporation 23 Example: Statistical View  Predicates

    involved:  TBSCAN(13) is applying local predicate: Q8.D_QUARTER_NAME IN ('2001Q1', '2001Q2', '2001Q3')  HSJOIN(11) is applying join predicate: (Q11.SR_RETURNED_DATE_SK = Q8.D_DATE_SK)  It seems that majority of qualified fact table rows refer to very small fraction of dimension table.  A statistical view should help in this case.  Solution: Create a statistical view on tables STORE_RETURNS and DATE_DIM. create view sv_store_returns_date_dim as (select Q8.* from HARMISTR.DATE_DIM as Q8, HARMISTR.STORE_RETURNS as Q11 where Q11.SR_RETURNED_DATE_SK = Q8.D_DATE_SK); alter view harmistr.sv_store_returns_date_dim enable query optimization; runstats on view harmistr.sv_store_returns_date_dim tablesample bernoulli(10);  In the same way, we should create statistical view for STORE_SALES and DATE_DIM as well as CATALOG_SALES and DATE_DIM.
  24. © 2012 IBM Corporation 24 Identifying Expensive Operator  Cost

    is the number just below the operator number  Operator cost is the increase in the cost compared to the previous operator (or sum of all input operator costs)  Cost numbers are cumulative 3.00579e+6 >NLJOIN ( 10) 1.54341e+06 548903 /---------+---------\ 3.00579e+6 1 HSJOIN IXSCAN ( 11) ( 15) 1.23169e+06 311718 284686 0 /------+------\ | 1.01317e+07 215511 1317 TBSCAN BTQ INDEX: MYSCHEMA ( 12) ( 13) IDX_TBL_F 116551 1.07116e+06 Q5 93464 181971 | | 1.01317e+07 215511 TABLE: MYSCHEMA TBSCAN TBL_E ( 14) Q3 1.07016e+06 181971 | 215511 TABLE: MYSCHEMA TBL_D
  25. © 2012 IBM Corporation 25 Expensive Table Scan • Are

    only a few columns needed ? – Index only access possible ? • Predicates filter significantly ? – ISCAN–FETCH may be cheaper – Consider Indexes for IN or OR predicates • Is this a fact table with aggregation ? – Could we define a Materialized Query Table • Partition, Partition, Partition – Could it be partitioned by range ? – Could this be a Multi-dimension Clustered table ? – Both MDC + Range partitioning ?
  26. © 2012 IBM Corporation 26 Example: Expensive IN Predicate •

    Problem – C1 IN (10. 100, 500, 10000) – Large table scan to fetch a few rows • Solution – Consider an index with the column – For example: index on (C1, C3) – Index (C2, C1) is also good if the – query has an equality predicate on C2 8 NLJOIN ( 9) 122.22 16 /-----+-----\ 4 2 TBSCAN FETCH ( 10) ( 13) 0.014061 30.3206 0 4 | /----+----\ 4 2 298854 TABFNC: SYSIBM IXSCAN TABLE: MYSCHEMA GENROW ( 3) BIGTABLE Q3 30.2756 Q1 3 | 298854 INDEX: SAPP29 INDX~0 Q1 After 8 TBSCAN ( 2) 3312.15 702 | 298854 TABLE: MYSCHEMA BIGTABLE Q1 Before
  27. © 2012 IBM Corporation 27 Expensive SORT • Is there

    spilling ? – Could you increase SORTHEAP or BUFFERPOOL ? … do not increase SORTHEAP too high if there are concurrent Hash joins or sorts in this query and specially in a multi-user environment • Could you create an index to avoid the sort – Note that an ISCAN-FETCH may be more expensive – Perhaps an index-only access ?
  28. © 2012 IBM Corporation 28 Example: SORT Spills • The

    details for a SORT will indicate if the SORT spilled • The I/Os indicate that there was spilling associated with the SORT. • Minimize spills by considering indexes and as discussed earlier, by balancing SORTHEAP, SHEAPTHRES and BUFFERPOOL SORT ( 16) 6.14826e+06 1.30119e+06 | 3.65665e+07 TBSCAN ( 17) 2.00653e+06 1.14286e+06 | 3.74999e+07 TABLE: TPCD.ORDERS
  29. © 2012 IBM Corporation 29 Summary Highly Utilized Resource Examples

    of Symptoms in Monitoring Data Examples of Symptoms in Explain Plan Possible Solution Alternatives Statement Heap -- - SQLCODE 437 Message Token “1” - Consider increasing statement heap Sort Heap - Sort overflow - Hash join overflow - Temp IO - Cardinality underestimation for SORT/HSJOIN - Table scans - Consider collecting additional statistics - Creating indexes may help - For concurrent queries, consider WLM Buffer Pool - Logical and physical IOs - Few records SELECTed from many rows READ - Cardinality underestimation for NLJOIN - Table scans - Consider collecting additional statistics - Creating indexes may help - For concurrent queries, consider WLM CPU - No major IO bottlenecks - Sargable / Residual predicates - Consider rewriting query - Creating indexes may help - For concurrent queries, consider WLM
  30. © 2012 IBM Corporation 30 Appendix

  31. © 2012 IBM Corporation 31 List of Useful Diagnostic Tools

     For collecting explain information  Section explain  For formatting explain information  db2exfmt  For monitoring query execution  db2pd  db2top  db2 snapshots  db2 SQL monitoring functions  Section actuals
  32. © 2012 IBM Corporation 32 Example: RUNSTATS  A query

    is performing poorly on production system as compared to development system.  Original Statement: ­­ Note: VW is a view select * from db2inst1.vw v, db2inst1.t1 t1 where v.x = t1.x  Optimized Statement: SELECT ... FROM DB2INST1.T4 AS Q1, DB2INST1.T3 AS Q2, DB2INST1.T2 AS Q3, DB2INST1.T1 AS Q4 WHERE (Q1.T2ID = Q2.T2ID) AND (Q3.T4ID = Q1.T4ID) AND (Q1.Y = 'Y') AND (Q1.Z = 'N') AND (Q3.X = Q4.X) AND (Q3.Y IS NULL )
  33. © 2012 IBM Corporation 33 Example: RUNSTATS  Access plan:

    9.906 NLJOIN ( 2) 11476.2 2892 /­­­­­­­­­­­­­­+­­­­­­­­­­­­­­\ 247.66 0.04 NLJOIN FETCH ( 3) ( 8) 11333.4 100.125 2888 4.00412 /­­­­­­­­­+­­­­­­­­\ /­­­+­­\ 0.0033 74416 1 712084 NLJOIN TBSCAN IXSCAN TABLE: DB2INST1 ( 4) ( 7) ( 9) T2 0.0199984 11333.4 75.0188 52 2888 3 /­­­­­­­+­­­­­­\ | | NOTE>> 0.0208 0.16 74416 712084 IXSCAN IXSCAN TABLE: DB2INST1 INDEX: DB2INST1 ( 5) ( 6) T1 IDX_1 0.0104877 0.00951078 0 1 | | 13 100 INDEX: DB2INST1 INDEX: DB2INST1 IDX_T4 IDX_T3
  34. © 2012 IBM Corporation 34 Example: RUNSTATS  COUNT(*) queries

    to confirm the actual counts SELECT COUNT(*) FROM "DB2INST1 "."T4"; RESULT: 13 ROWS SELECT COUNT(*) FROM "DB2INST1 "."T4" AS Q1 WHERE (Q1.Y = 'Y') AND (Q1.Z = 'N'); RESULT: 13 ROWS  Extended diagnostic information Diagnostic Identifier: 1 Diagnostic Details: EXP0045W. The table named "DB2INST1.T4" has fabricated statistics. This can lead to poor cardinality and predicate filtering estimates. The size of the table changed significantly since the last time the RUNSTATS command was run.  Solution: Collect statistics by running RUNSTATS on the table DB2INST1.T4
  35. © 2012 IBM Corporation 35 Example: RUNSTATS  Access plan

    after RUNSTATS is executed  Note IXSCAN(8) 6191.41 HSJOIN ( 2) 11948.8 2892 /­­­­­­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­\ 74416 2.08 NLJOIN HSJOIN ( 3) ( 7) 11945.4 0.0277668 2892 0 /­­­­­­+­­­­­­\ /­­­­­­­+­­­­­­\ 74416 1 13 4 TBSCAN FETCH IXSCAN IXSCAN ( 4) ( 5) ( 8) ( 9) 11333.4 100.125 0.0156447 0.0112472 2888 4.00412 0 0 | /­­­+­­­\ | | 74416 1 712084 13 4 TABLE: DB2INST1 IXSCAN TABLE: DB2INST1 INDEX: DB2INST1 INDEX: DB2INST1 T1 ( 6) T2 IDX_T4 IDX_T3 75.0188 3 | 712084 INDEX: DB2INST1 IDX_1
  36. © 2012 IBM Corporation 36 Statistical View  Let us

    take a very simple example to better understand statistical view Table:T1 Table:T2 X Y Y ­­­­­­­­ ­­­ 1 A A 2 B A 2 C A 4 D A 7 E A 7 F B 7 G D 7 H E 9 I F 9 J I Distribution of values in column T1.X TYPE SEQNO COLVALUE VALCOUNT ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ F 1 7 4 F 2 9 2 F 3 2 2 Consider the following query: SELECT * FROM T1, T2 WHERE T1.Y = T2.Y Distribution of values in column T1.X, after join TYPE SEQNO COLVALUE VALCOUNT ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ F 1 1 5 F 2 7 2
  37. © 2012 IBM Corporation 37 Statistical View  Now, consider

    the following query: SELECT * FROM T1, T2 WHERE T1.Y = T2.Y AND T1.X=1  Without statistical view, optimizer will estimate cardinality of this query = 1, but the correct value is 5 !
  38. © 2012 IBM Corporation 38 Example: Column Group Stats 

    A query's performance is poor.  Query: Original statement SELECT ... FROM T1, T2, T3, T4 WHERE ... AND T1.ACCT_NUM = T2.ACCT_NUM AND T2.ACCT_NUM = T3.ACCT_NUM AND T2.ID_NUM = T3.ID_NUM AND T1.ACCT_NUM = T4.ACCT_NUM AND T1.REP_NUM = T4.REP_NUM AND T4.ACCT_NUM = T3.ACCT_NUM AND T4.ID_NUM = T3.ID_NUM AND T4.ACCT_IND = T3.ACCT_IND AND ('1800­01­01­00.00.00.000000' < T1.DATE_MOD) AND T1.COMM IN ('A', 'D') ...
  39. © 2012 IBM Corporation 39 Example: Column Group Stats 

    Access Plan 55.8297 NLJOIN ( 2) 226198 66138.3 /­­­­­­­­­­­­+­­­­­­­­­­­\ 55.8297 1 NLJOIN FETCH ( 3) ( 10) 221047 92.2672 65932.4 3.68909 /­­­­+­­­\ /­­­+­­­\ 57.0648 0.978356 3.05367 3.81189e+06 TBSCAN IXSCAN IXSCAN TABLE: DB2INST1 ( 4) ( 9) ( 11) T3 218193 50.0315 50.0303 65818.2 2 2 | | | 57.0648 1.06432e+06 3.81189e+06 SORT INDEX: DB2INST1 INDEX: DB2INST1 ( 5) T2_IDX T3_IDX 218193 65818.2 | 57.0648 <<< NOTE: Join of two big tables producing only 57 rows !!! HSJOIN ( 6) 218193 65818.2 /­­­­­­+­­­­­\ 6.69807e+06 1.15013e+06 TBSCAN TBSCAN ( 7) ( 8) 60191.6 33264.5 33540 17057 | | 6.69807e+06 3.3602e+06 TABLE: DB2INST1 TABLE: DB2INST1 T4 T1
  40. © 2012 IBM Corporation 40 Example: Column Group Stats 

    Predicates applied by HSJOIN(6): (Q4.ACCT_NUM = Q1.ACCT_NUM) (Q1.REP_NUM = Q4.REP_NUM)  Note that there are multiple equality join predicates between these two tables.  The optimizer assumes that two predicates are independent of each other. However, the columns could be statistically correlated and that could result in underestimation of cardinality.  This estimation is important because it is part of NLJOIN(3)'s outer side and that NLJOIN has a big table in its inner side.  So, let us check the actual cardinality.
  41. © 2012 IBM Corporation 41 Example: Column Group Stats 

    Actual cardinality SELECT COUNT(*) FROM T4 AS Q1, T1 AS Q4 WHERE (Q1.REP_NUM = Q4.REP_NUM) AND (Q4.ACCT_NUM = Q1.ACCT_NUM) AND ('1800­01­01­00.00.00.000000' < Q4.DATE_MOD) AND Q4.COMM IN ('A', 'D'); RESULT: 1,155,273 rows  Solution: Column group statistics will help.  In this case, columns group statistics should be collected on parent table ( generally the one with primary key ).
  42. © 2012 IBM Corporation 42 Example: Column Group Stats 

    To find parent-child relationship between two tables, consider the statistics of join columns: Table:T1 Table:T4 Column Colcard High2key Low2key Colcard High2key Low2key ACCT_ NUM 3357997 'JERE2' '00003' 3662064 'V222S' '00003' REP_ NUM 32341 '95517' '63135' 36864 '99999' '63135'  From these statistics, T4 is the parent table because  COLCARD of ACCT_NUM and REP_NUM is greater than T1's respective COLCARD statistics  the HIGH2KEY for each column is greater and  the LOW2KEY are the same  So, column group statistics should be collected on T4 table: RUNSTATS ON TABLE DB2INST1.T4 ON ALL COLUMNS AND COLUMNS ((ACCT_NUM, REP_NUM)) WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL
  43. © 2012 IBM Corporation 43 Column Group Stats  Let

    us take a very simple example to better understand column group statistics.  Here, country and city columns are correlated. Country City Hotel Name ­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ Germany Bremen Hilton Germany Bremen Best Western Germany Frankfurt InterCity Canada Toronto Four Seasons Canada Toronto Intercontinental WHERE Country = ? And City = ? With Independence : Selectivity = 1/2 * 1/3 = 0.1667  The following column group statistics will help RUNSTATS ON TABLE db2inst1.hotel ON ALL COLUMNS AND COLUMNS ( (country, city) ) WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL With Column Group Statistics : Selectivity = 0.3333
  44. © 2012 IBM Corporation 44 Example: Expensive OR Predicate •

    Problem – (C1 = 5 AND C2 = 10) OR (C1 = 6 AND C2 = 12) – Large table scan to fetch a few rows • Solution – Consider indexes for an Index-Oring plan 8 FETCH ( 2) 230.236 6.9672 /----+----\ 8 298854 RIDSCN TABLE: MYSCHEMA ( 3) BIGTABLE 30.2756 Q1 4 /-----+------\ 2 6 SORT SORT ( 4) ( 6) 15.138 15.138 2 2 | | 2 6 IXSCAN IXSCAN ( 5) ( 7) 15.1373 15.1373 2 2 | | 298854 298854 INDEX: SAPP29 INDEX: SAPP29 INDX~0 INDX~0 Q1 Q1 After 8 TBSCAN ( 2) 3312.15 702 | 298854 TABLE: MYSCHEMA BIGTABLE Q1 Before
  45. © 2012 IBM Corporation 45 Expensive ISCAN and FETCH •

    FETCH cost is very high ? • List Prefetch plans on the inner of nested loop joins ? – Look at the CLUSTERRATIO (or CLUSTERFACTOR) – If it is not close to 100 (or 1), consider REORG against this index if this is the key index used in most queries • Index-Only Access possible ? • If good filtering commonly used predicates are applied at the FETCH – Consider adding the column or columns to the index
  46. © 2012 IBM Corporation 46 Expensive Nested Loop Join •

    A TSCAN or SORT on the inner should be avoided as far as possible – Consider an index – Consider REORG • Outer is large and you have an ISCAN-FETCH on the inner ? – Consider Index-Only access • Expression on join predicate ? – Could you avoid the expression on the inner table column so that you could use an index with start-stop keys ? – Could you use generated columns with that expression ?
  47. © 2012 IBM Corporation 47 Example: Avoid SORT on the

    inner of NLJOIN • Problem: – “List-prefetch” chosen because of poor clustering • Solution: – Index-Only Access – REORG 33467.5 NLJOIN ( 11) 15491.5 10529.4 /-----------+-----------\ 267.277 125.216 BTQ FETCH ( 12) ( 17) 884.75 55.7205 376.972 37.9624 | /------+-----\ 13.3638 125.216 3.16068e+08 FETCH RIDSCN DP-TABLE: MYSCHEMA ( 13) ( 18) TRANSFACT 884.666 46.1653 Q10 376.972 5.96244 /---+---\ | 4686.66 18722 125.216 RIDSCN TABLE: MYSCHEMA SORT ( 14) TIMEDIM ( 19) 529.869 Q9 46.1651 68 5.96244 | | 4686.66 125.216 SORT IXSCAN ( 15) ( 20) 529.869 46.1508 68 5.96244 | | 4686.66 3.16068e+08 IXSCAN INDEX: MYSCHEMA ( 16) TRANSFACT_IDX 528.787 Q10
  48. © 2012 IBM Corporation 48 Example: Expensive Communication • Large

    Table Queues (TQs) – Consider replicated dimension tables – Could the large dimension table that is commonly joined be partitioned the same way as the fact table ? ... 1.44527e+07 ^NLJOIN ( 3) 9.04117e+08 8.98332e+07 /----+----\ 1.44527e+07 1 TBSCAN BTQ* <--- ( 4) ( 5) 761760 76.468 109515 8 | | 1.44527e+07 1 DP-TABLE: TPCDS FETCH STORE_SALES ( 6) Q4 18.7064 2 /---+----\ 1 266000 IXSCAN TABLE: TPCDS ( 7) CUSTOMER 10.4897 Q2 ... Input Streams: ------------- Partition Map ID: 4 Partitioning: ( 0) Single Node (# 0) Partition Output Streams: -------------- Partition Map ID: 3 Partitioning: (REPL )
  49. © 2012 IBM Corporation 49 References 1) Get the most

    out of DB2 optimizer http://www.ibm.com/developerworks/data/library/techarticle/dm- 1025db2accessplan/ 2) Understand column group statistics in DB2: http://www.ibm.com/developerworks/data/library/techarticle/dm- 0612kapoor/index.html