Transcript Rev Up Your SQL Application with Enhanced Statistics!

Rev Up Your SQL Application
with Enhanced Statistics!
Ken McDonald, BMC Software
December, 2011
(written by Jim Dee)
What Does “Enhanced” Mean?
From dictionary.com:
en·hance [en-hans, -hahns]
–verb (used with object),-hanced, -hanc·ing.
1. to raise to a higher degree; intensify; magnify: The candelight enhanced her beauty.
2. to raise the value or price of: Rarity enhances the worth of old coins.
© Copyright 7/16/2015 BMC Software, Inc
2
Introduction
Changes made to optimizer statistics in DB2 9
Columns in SYSINDEX - CLUSTERRATIOF and DATAREPEATFACTORF
Enhancing these index statistics is optional
Point is to improve performance of key range extractions in SQL
-
No help for single row retrievals
© Copyright 7/16/2015 BMC Software, Inc
3
DB2 Index Structure
Root page
Nonleaf
Pages
Leaf
Pages
Table
Pages
© Copyright 7/16/2015 BMC Software, Inc
4
Clustered Retrieval
Root page
Nonleaf
Pages
Leaf
Pages
Table
Pages
© Copyright 7/16/2015 BMC Software, Inc
5
Not So Clustered Retrieval
Root page
Nonleaf
Pages
Leaf
Pages
Table
Pages
© Copyright 7/16/2015 BMC Software, Inc
6
More Clustering Examples
4C0135
4C0039
4BE811
4BE80D
4BE704
4BE021
…page numbers
descending
© Copyright 7/16/2015 BMC Software, Inc
4C0134
4C0135
4C0011
4C040D
4BFF04
4C0021
…up and down in a
narrow range
4C0133
…
4C180E
4C2A0D
…
4C4804
4C6121
…
…gradual drift in one
direction
7
What is DATAREPEATFACTORF?
4C0135
4C0039
4C0131
4C010D
4C0004
4C0021
4C0022
4C0001
4C0122
4C0107
4C0123
4C1202
12 rids…3 page numbers
© Copyright 7/16/2015 BMC Software, Inc
4C0134
4C0235
4BFF11
4C140D
4C0104
4C2021
4C0629
4BEC07
4BF123
4C082F
4C2101
4C3240
12 rids…11 page numbers
8
STATCLUS
Line 9 on DSNTIP6 panel (“STATISTICS CLUSTERING”)
“ENHANCED” is default
You can specify STANDARD
STATCLUS in DSN6SPRM macro
Affects RUNSTATS operation, not the optimizer
© Copyright 7/16/2015 BMC Software, Inc
9
STATCLUS Migration
What happens to existing stats and packages when STATCLUS changes from
STANDARD to ENHANCED?
-
Nothing! Must run RUNSTATS and BIND
Dynamic SQL affected as soon as RUNSTATS runs
© Copyright 7/16/2015 BMC Software, Inc
10
CLUSTERRATIO Calculation Improvements
Looks at every RID
Considers declining values
Considers size of prefetch window
Point is to improve optimization of index scans
© Copyright 7/16/2015 BMC Software, Inc
11
CLUSTERRATIO Calculation Overview
Based on analysis of tablespace page referred to by each RID (no need to
access tablespace!)
Consider index as list of tablespace page numbers
Want one number that gives average expectation that synchronous I/O will be
avoided
© Copyright 7/16/2015 BMC Software, Inc
12
CLUSTERRATIO Calculation
Assume 32K tablespace
Prefetch size is 4 pages (for buffer pool between 3.2M and 160M)
Last page # = 13
This page # = 75
…
73 + 4
Sliding window:
11 to 73
New sliding window: 12 to 75
© Copyright 7/16/2015 BMC Software, Inc
13
CLUSTERRATIO Calculation Details
Example calculation, based on prefetch amount of 8
-
Could be 16K tablespace in buffer pool with VPSIZE=8000
Page numbers are decimal
Page #
13
35
40
Window after
13-13
35-35
35-40
# clustered
1
1
#unclustered
0
1
-
… 96
29
104
3
7
35-96
33-96
41-104
3-3
3-7
2
9
10
11
11
12
1
1
1
1
2
2
CLUSTERRATIOF = 12 / (12 + 2) = .8571
© Copyright 7/16/2015 BMC Software, Inc
14
Does This Work?
All rids considered
-
More accurately simulates access to table pages
Descending rids “count”
-
Supports dynamic prefetch as well as list prefetch
“Oscillating” rids in small range
-
Within prefetch amount of range “counts”
Gradual trend in page numbers
-
Will “move” sliding window
© Copyright 7/16/2015 BMC Software, Inc
15
What About Partitioned Spaces?
Each partition of index calculated separately
Results averaged for index, weighted by table partition cardinality
NPI’s?
-
They can include rids from many table partitions
“Switch” in page number from one partition to another is always counted as
unclustered
We know the second page won’t be part of the same prefetch!
© Copyright 7/16/2015 BMC Software, Inc
16
DATAREPEATFACTORF Calculation Overview
Adds consideration of row density in tablespace
For clustered data, want number to reflect relative probability of buffer hits
after asynchronous I/O
Lower is better
© Copyright 7/16/2015 BMC Software, Inc
17
DATAREPEATFACTORF Calculation
Assume 32K tablespace
Prefetch size is 4 pages (for buffer pool between 3.2M and 160M)
Last page # = 13
This page # = 75
…
Sliding window:
11 to 73
© Copyright 7/16/2015 BMC Software, Inc
18
DATAREPEATFACTORF Calculation Details
Example calculation, based on prefetch amount of 8
-
Could be 16K tablespace in buffer pool with VPSIZE=8000
Page numbers are decimal
Page #
13
35
35
2
10
18
26
3
15
Window after
13-13
35-35
35-35
2-2
2-10
2-18
2-26
2-26
2-26
DRF
1
2
2
3
4
5
6
6
6
-
DATAREPEATFACTORF = 6
© Copyright 7/16/2015 BMC Software, Inc
19
Breathe!
© Copyright 7/16/2015 BMC Software, Inc
20
When Do Enhanced Statistics Matter?
----------------------------------------------------| ACCESSTYPE | MATCHCOLS | ACCESSNAME | INDEXONLY |
----------------------------------------------------| I
|
1 | ITEMSHIP
| N
|
-----------------------------------------------------
-
Access must be to the table, through a scan of the index
Most of the time, the significant CLUSTERRATIOF threshold is 80%
7800% change from 1% to 79% doesn’t matter
2.5% change from 79% to 81% does matter
© Copyright 7/16/2015 BMC Software, Inc
21
Hybrid Join Example
---------------------------------------------------------------|
METHOD
| ACCESSTYPE |
ACCESSNAME
| SORTN_JOIN| PREFETCH |
---------------------------------------------------------------|
4 | I
| CUSTIX
| N
| L
|
----------------------------------------------------------------
-
METHOD value of 4 indicates a hybrid join
List prefetch usually selected to access the table
SORTN_JOIN value of ‘N’ indicates the rid list is not sorted…CLUSTERRATIOF
for CUSTIX > 80%
Value is ‘Y’ if CLUSTERRATIOF <= 80%
© Copyright 7/16/2015 BMC Software, Inc
22
DATAREPEATFACTORF Example
---------------------------------------------------------------|
METHOD
| ACCESSTYPE |
ACCESSNAME
| INDEXONLY | PREFETCH |
---------------------------------------------------------------|
0 | I
| ORDERCUST
| N
| L
|
----------------------------------------------------------------
-
Index scan to access a table
PREFETCH is not chosen for CLUSTERRATIOF > 81%
A high DATAREPEATFACTORF (close to number of rows in table) causes
selection of prefetch up to 90%
© Copyright 7/16/2015 BMC Software, Inc
23
When Will ENHANCED Help?
Low CLUSTERRATIOF increases
-
All rids now counted
Descending rids
Page numbers going up and down within a range
Avoid sorts for hybrid joins, avoid prefetch in nested loop joins
Clustered index with less dense data
-
Avoid prefetch in index scans
© Copyright 7/16/2015 BMC Software, Inc
24
When Will ENHANCED Hurt?
When DB2 was doing the right thing for the wrong reasons
Possible to decrease CLUSTERRATIOF which was artificially high
Where CLUSTERRATIOF increases, maybe prefetch was a good idea
© Copyright 7/16/2015 BMC Software, Inc
25
How To Get There?
Clone data to ENHANCED DB2?
-
Might work for limited amount of data
RUNSTATS … REPORT YES UPDATE NONE
Compare reported CLUSTERRATIOF with catalog
-
-
Changes above or below threshold
EXPLAIN SQL identified as using indexes of interest
Change SQL if possible
© Copyright 7/16/2015 BMC Software, Inc
26
Alternatives
Use Plan Stability!
REBIND…PLANMGMT(EXTENDED)
-
Creates new “Original” package
If problems, REBIND…SWITCH(ORIGINAL)
Or, copy SYSINDEXES to user table
If problems, revert CLUSTERRATIOF and set DATAREPEATFACTORF to -1
-
Columns are updatable
© Copyright 7/16/2015 BMC Software, Inc
27
Bibliography
“DB2 9 for z/OS Performance Topics”, IBM Redbook, SG24-7473-00
“DB2 Version 9.1 for z/OS SQL Reference”, SC18-9854-02
“DB2 9 for z/OS Performance Monitoring and Tuning Guide”, SES1-2947-04
“DB2 Version 9.1 for z/OS Utility Guide and Reference”, SC18-9855-03
© Copyright 7/16/2015 BMC Software, Inc
28
Questions?
Ken McDonald
BMC Software Inc.
[email protected]
© Copyright 7/16/2015 BMC Software, Inc
29