DB2 SQL Query Tuning

© 2012 IBM Corporation
1
DB2 LUW SQL Query Tuning
Harjindersingh Mistry, DB2 Query Optimizer Development, IBM

View Slide

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

View Slide

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

View Slide

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

View Slide

5
Resource: Statement Heap
Query
Compilation
Query
Execution
Tablespaces
Temp User System

View Slide

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.

View Slide

7
Resource: Sort Heap
Query
Compilation
Query
Execution
Tablespaces
Temp User System

View Slide

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

View Slide

9
Resource: Buffer Pool
Query
Compilation
Query
Execution
Tablespaces
Temp User System

View Slide

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


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

View Slide

11
Resource: CPU
Query
Compilation
Query
Execution
Lock List Others
Tablespaces
Temp User System
Buffer Pool

View Slide

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


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

View Slide

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 >

View Slide

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

View Slide

15

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

View Slide

16

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 ?

View Slide

17

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.

View Slide

18

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.

View Slide

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

View Slide

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

View Slide

21

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.38112e05
^NLJOIN
( 9)
30963
13550.8
/+\
0.0113066 0.00122151
^NLJOIN FETCH
( 10) ( 16)
30949.4 20.3389
13548.8 3
/+\ /+\
1053.61 1.07313e05 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

View Slide

22

Actual cardinality:
Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
...
1.38112e05
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.07313e05 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

View Slide

23

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.

View Slide

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

View Slide

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 ?

View Slide

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

View Slide

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 ?

View Slide

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

View Slide

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
CPU - No major IO
bottlenecks
- Sargable / Residual
predicates
- Consider rewriting query

View Slide

30
Appendix

View Slide

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


Section actuals

View Slide

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 )

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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 !

View Slide

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.ID_NUM = T3.ID_NUM AND
T1.REP_NUM = T4.REP_NUM AND
T4.ID_NUM = T3.ID_NUM AND
T4.ACCT_IND = T3.ACCT_IND AND
('1800010100.00.00.000000' < T1.DATE_MOD) AND
T1.COMM IN ('A', 'D') ...

View Slide

39

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

View Slide

40

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.

View Slide

41

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
('1800010100.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 ).

View Slide

42

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

View Slide

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

View Slide

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

View Slide

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

View Slide

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 ?

View Slide

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

View Slide

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 )

View Slide

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

View Slide

DB2 SQL Query Tuning

DB2 SQL Query Tuning

harjinder-hari

More Decks by harjinder-hari

Other Decks in Technology

Featured

Transcript