Transcript Slide 1

DB2 Pools…..or……
Where has all my storage gone?
Thank you, Peter, Paul, and Mary
7/17/2015
Topics covered in this presentation
› Virtual Storage Usage in DB2 for z/OS
› The main DB2 Storage Pools
› We always start with the Bufferpools
› The EDM Pool, RID Pool and SORT Pool
–
–
–
–
–
–
2
What they are used for and how do they work
How large do they need to be?
What happens if they are sized incorrectly
Monitoring Pool Usage
Impact of DB2 for z/OS V8 and 64 bit addressing
Recommended Practices, Hints and Tips
7/17/2015
©2005 BMC Software
Bars and Lines (not to scale)
And Now -
64 bits - 16Eb
The BEAM
You can now address 8.6
Billion times as much storage…
User Area
Above the Bar
Another way of looking at it:
If 2Gb represents 1 second
Then 16Eb is over 272 years!
49 bits - 512Tb
Reserved
For System
32 bits - 4Gb
31 bits - 2Gb
Wasted
The Bar
Above the Line
24 bits - 16Mb
3
7/17/2015
Below the Line
The Line
©2005 BMC Software
What comes next?
› Kilobyte
› Megabyte
› Gigabyte
› Terabyte
› Petabyte
› Exabyte
› Zettabyte
› Yottabyte
10 power of 3
10 power of 6
10 power of 9
10 power of 12
10 power of 15
10 power of 18
10 power of 21
10 power of 24
Also have Mebibyte for exact power of 2 (1,048,576) etc.
4
7/17/2015
©2005 BMC Software
Where DB2 Storage is allocated
› Locks Stored in IRLM
› Most of the rest comes out of DBM1
– Limited to 2Gb below line before V8
• Significant VSC (Virtual Storage Constraint)
– Some relief available by using Data Spaces and/or Hiperpools
– Much more help in V8 using 64 bit addressing
› Many conflicting demands for limited resource
– Results in a juggling act at many sites
– Primary motivator for many early V8 adopters
• VSCR in CMPAT mode
– Excellent resources available with more detail on DB2 Virtual Storage
Management
• See John Campbell’s excellent paper on this topic
– ftp://ftp.software.ibm.com/software/data/db2zos/DB2VSTORprez.pdf
5
7/17/2015
©2005 BMC Software
Mainframe Technology Evolution
I/O is the Laggard
› I/O remains very slow in computing terms
– I/O Performance hasn’t improved as quickly as other areas
› If we compare improvements over the past 10 years:
CPU
Real Storage
DB Size
I/O time
374 mips  17,802 mips
8Gb  512Gb
200 Gb  20 Tb
30ms  2-3ms
x 48
x 64
x 100
x 10-15
› DB2 uses Virtual Storage to reduce I/O and improve performance
6
7/17/2015
©2005 BMC Software
Below the Line Storage
› Used to be the major problem
› Still some storage areas below the line
– 600Kb base requirement
– 800 bytes per Active thread
– 300 bytes (V7) or <20 bytes (V8) per Open Dataset
› Major consequence is DSMAX
– Theoretical limit was 32K (V7) – now 100K in V8
– Practical V7 limit more likely to be mid-20’s
– Problem for ERP systems and multi-partitioned objects
› All above assumes SWA is above the line
– If not, DSMAX will need to be 5,000 or less, even in V8
7
7/17/2015
©2005 BMC Software
DB2 Virtual Storage Map (V7)
8
7/17/2015
©2005 BMC Software
DB2 Virtual Storage Map (V8)
9
7/17/2015
©2005 BMC Software
Where Has All My Memory Gone?
Much of it has gone to pools
Bufferpool
EDM Pool
RID Pool
Sort Pool
Bufferpools - Cache for data and index pages
EDM Pool - Cache for access paths and related
data such as DBDs
RID Pool - Cache to prevent unnecessary I/O
SORT Pool - Internal Sortwork area (per Sort)
10
7/17/2015
©2005 BMC Software
DB2 Pool Sizing (EDM, RID, and SORT)
DSNZPARM Settings
11
7/17/2015
©2005 BMC Software
Buffer Pool Management
A Little History
Recommendations
(then)
One Big BP0
BP0
BP1
BP32K for composite
rows > 4K and some
unique apps
BP32K
BP2
Recommendations (now)
Way to big for this text box
BP0
BP49
50
BP8K0
BP8K9
10
BP16K0
BP16K9
10
BP32K
BP32K9
10
Buffer Pool Management
Nuts & Bolts
› Virtual Pool Caching Options
Where Do DB2 Pages Go?
› DATASPACE
› Primary
– DBM1 Address
Space
– Cumulative total
1.6 GB (although
much less is
what’s practical)
DBM1 AS
Virtual
Bufferpool
Dataspace
Virtual
Bufferpool
– I/O Directly from and
to Dataspace
– One VP can span
multiple spaces
– Cumulative total of
256GB across all
dataspaces
›Hiperpools
Hiperpool
13
7/17/2015
– Uses ESO hiperspace to create an extended cache for
your data
– Is read-only data
– Faster response times than I/O from disk
– VSCR for the DBM1 address space
©2005 BMC Software
Buffer Pool Management
Nuts & Bolts – Multi-Address Space Options
› Virtual Pool Caching Options
Where Do DB2 Pages Go?
64 bits - 16Eb
DBM1 AS
Hiperspace
Dataspace
The Bar
31 bits - 2Gb
Above the Line
Virtual
Bufferpool
Hiperpool
Virtual
Bufferpool
›What’s the goal
– Leverage MVX/ESA architecture to reduce impact of virtual
storage constraint in the DBM1 address space
– Multiple address space architecture
– Architecture up to and including DB2 Version 7
14
7/17/2015
©2005 BMC Software
The Line
24 bits - 16Mb
Below the Line
Buffer Pool Management
Nuts & Bolts – 64-bit Architecture
Where Do DB2 Pages Go?
› Virtual Pool Caching Options
64 bits - 16Eb
DBM1 AS
Virtual
Bufferpool
› With DB2 V8 and z/OS 64-bit
– 64 bit architecture eliminates need for multiple
address spaces for virtual pools
– All buffers now in DBM! Address space
– Virtually eliminates VSC in the DBM1 address
space
• Still some potential problems related to other
pools
• Doesn’t eliminate problems with system paging
15
7/17/2015
User Area
Above the Bar
49 bits - 512Tb
Reserved
For System
32 bits - 4Gb
31 bits - 2Gb
Wasted
The Bar
Above the Line
24 bits - 16Mb
©2005 BMC Software
Below the Line
The Line
Buffer Pool Management
Read/Write Operations
› Read Operations
– Random – One page at a time
– Prefetch – Either sequential, list, or dynamic
– Percentage of bufferpool allowed for use by prefetch operations controllable
via user managed thresholds
› Write Operations
– Writes are queued by dataset and externalized when
• System Checkpoint occurs
• User modifiable thresholds are exceeded
– Deferred write threshold applies to all pages in the pool (in use and updated)
– Vertical deferred write threshold applies pages for a single pageset ( updated pages)
– Up to 32 pages written at a time depending on page size
16
7/17/2015
©2005 BMC Software
Buffer Pool Management
What’s the Pools Look Like
BPn
Updated Pages In-Use Pages
Available Pages
Being
Handled
Normal Read
Queue
Sequential Prefetch
Queue
Queued per
data set
Available Pages
Unavailable Pages
From the V7 Administration Guide
17
7/17/2015
©2005 BMC Software
Buffer Pool Management
Fixed Thresholds
Assisting
Parallel
Sequential
Parallel
Sequentia
l
(50%)
Prefetch DisabledData Manager Critical
(90%)
(95%)
Vertical
Deferred Write Sequentia
l
Deferred
( 50%)
Steal
Write
› Immediate(10%)
Write Threshold (97.5%) – very bad!!!
(80%)
›
›
Immediate Write
(97.5%)
– DB2 loses benefit of asynchronous write
– Each updated page is written to disk synchronous with the SQL request
Data Manager Threshold (95%) - very bad!!
– DB2 now does a page access operation for each row being referenced on a single page
– Significant impact on processor resources
Sequential Prefetch – (90%) – pretty bad!
– Prefetch disabled till more buffers become available
Buffer Pool Management
Variable Thresholds
VPPSEQT:
Parallel
Sequential
VPXPSEQT: Assisting
Parallel Sequential
(50% of VPSEQT)
(0% of VPPSEQT)
VDWQT: Vertical
Deferred Write
(10%)
DWQT: Deferred
Write ( 50%)
Data Manager Critical
(95%)
Prefetch Disabled
(90%)
VPSEQT:
Sequential
Steal (80%)
Immediate Write
(97.5%)
› VPSEQT & HPSEQT – Key control knob
›
›
– Controls what % of VBP or Hiperpool used by sequentially accessed pages
– Setting to 0 effectively disables prefetch for objects in this BP
DWQT and VDWQT – Key control knob
– When reached, DB2 will begin scheduling asynchronous write engines for datasets with oldest updated
pages and continues till below threshold
– DWQT looks at all updates while VDWQT looks at updates for individual datasets
VPXPSEQT & VPPSEQT
– How much of the BP can be used in assisting parallel operations as a % of VPSEQT
– VPP is for local parallelism and VPX is for Sysplex query parallelism
Buffer Pool Management
Nuts and Bolts – V7 Buffer Pools
Output of the Display Bufferpool Command
•A DB2 V7 Example
•Hiperpools in Play
•Dataspace in play as well
BUFFERPOOL TYPE
Indicates Primary or Dataspace
HIPERPOOL SIZE
HIPERPOOL usage and size
BUFFERPOOL
THRESHOLDS
More on this later
20
7/17/2015
©2005 BMC Software
Buffer Pool Management
Nuts and Bolts – DB2 Version 8
Output of the Display Bufferpool Command
•A DB2 V8 Example
•No Hiperspace or Dataspace in play
•Page Fix Attribute new with V8
•Thresholds slightly different
BUFFERPOOL TYPE
A single entry – no dataspace
Page Fix Yes PGFIX(YES)
Page Fixes BP in Real Storage
BUFFERPOOL
THRESHOLDS
21
7/17/2015
©2005 BMC Software
More on this later
Measuring DB2 Buffer Pools
How Effective Are my Bufferpools?
Collect
performance data in order to evaluate your buffer pool
efficiency
 System paging, DB2 DBM1 page faulting
 Getpages per second
 Synch I/Os per second
 VP “system” hit ratios, VP+HP “system” hit ratios
 VP “application” hit ratios, VP+HP “application” hit ratios
 VP minimum residency times, VP+HP min residency times
 Write I/Os per second
Page
22
7/17/2015
updates per write
©2005 BMC Software
Buffer Pool Management
What’s the Pools Look Like
Bufferpool View
23
7/17/2015
©2005 BMC Software
Buffer Pool Management
Am I Exceeding Fixed Thresholds
Look at Adverse Performance Events for a BP
24
7/17/2015
©2005 BMC Software
Bufferpool Tuning
Questions to Ask
Bufferpools
› Should there be more of them?
› Are they configured right for the workloads?
› Are there more I/O delays than necessary?
› Is DB2 suffering system paging delays
› Am I using all the resources I have available?
– Efficiently?
› Is Sort working well? How about List Prefetch?
› Are there any virtual storage constraints problems?
– Big issue prior to DB2 V8
– Don’t assume there won’t be issues with DB2 V8
25
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
Initial Steps
›Look for low-hanging fruit
– Identify the busiest bufferpools and try to drive up
their hit ratios
• Increase BP sizes incrementally and measure
improvements
• Monitor impact on system paging and VS in DBM1
• If no improvement in hit ratios be sure to lower to
previous value or even below if that makes sense
›If this doesn’t help (enough) then you may
need to take a more analytical approach
26
7/17/2015
©2005 BMC Software
Bufferpool Assignments
Rules of Thumb
›Catalog and Directory
– Dedicated pool, big enough to reduce the I/O to a trickle
– Must be BP0, plan to move everything else somewhere else
›DSNDB07 – Work Files
– Heavily accessed and are often usually highly sequential
– Dedicated pool set up to favor sequential access (VPSEQT and HPSEQT
at 90 percent or higher.
›Tables and indexes are accessed very differently
– In general don’t mix in same pool
27
7/17/2015
©2005 BMC Software
Tuning Your Bufferpools
Breaking Down Your Application
› Segregate objects with different performance attributes into different
pools to avoid conflict
› Get to know your application objects and how they are accessed
–
–
–
–
–
–
–
28
Object Type (Table or index, system or temporary object, 4K, 8K, etc)
What’s the activity level for this object
How is the object accessed (random or sequential)
How heavily is the object updated
What’s the object size and how is it indexed
How important is the object to the overall application
How important is the overall application to your business
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
More Rules of Thumb
› Random or Sequential Access
– Objects access sequentially should be placed in pools that favor
prefetch
• Prefetch thresholds should be high
– Objects access purely randomly should be in bufferpools set up to
deter prefetch
• Very low VPSEQT value
– 0 might be too low, use 10%
• Large objects with low hit ratios don’t benefit from large bufferpool sizes
• Consider buffering more of the indexes that are used for these accesses
29
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
More Rules of Thumb
›Heavily Updated Objects
– In pools with deferred write thresholds
• high enough to allow multiple updates to the same page to occur before write I/O
is performed.
– Setting the vertical deferred write threshold correctly to avoid I/O spikes that
can occur during system checkpoint
• Use the absolute page threshold to trigger a steady flow of writes up 128 pages
at a time
30
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
More Rules of Thumb
›Application activity levels
– Heavily accessed (busy) objects will roll less frequently accessed pages out
of the pool
• Applications with less activity will always do I/O, no matter how much storage is
assigned to the pool (assuming the busy object doesn’t become resident)
• Assigning less busy objects to a separate pool will improve their performance,
even if it is a small pool
• Assumes less busy application has lower priority
31
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
More Rules of Thumb
› Reference, Look-up, code tables
– Can improve performance dramatically if you can arrange for them to live in a pool
sized to allow them to become resident
• Ideal for code pages, reference tables, etc.
• Combine the tables into segmented tables to reduce number of physical objects you
need to manage
• Since these objects are primarily read-only use the FIFO (First In, First Out) to kill
overhead of LRU management
• Set VPSEQT low to eliminate Sequential Prefetch overhead.
– Large binary objects to own bufferpool
• A single access can wipe out an otherwise happy pool
• Dataspace pools look especially good for these
• Set the DWTQ to 0 for both Log(yes) and Log(no) LOBS
– Helps avoid massive writes at DB2 checkpoints
32
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
This Means More Work
›Things to consider
– The more data is segregated into different pools, the more your storage is
divided up, and the more you risk have a burst of activity cause one pool to
become over committed while another is near idle.
– A compromise between segregation and complexity is required.
– Higher complexity can result in better potential performance, but may also
require more diligent monitoring to ensure that all sizing is correct and no
rogue applications are messing up your system.
33
7/17/2015
©2005 BMC Software
Tuning DB2 Buffer Pools
Final
Considerations
Watch those VSC
limits!
Tuning is an ongoing process required to keep up with
changing workloads
But with some thinking and leg-work, the results can be
dramatic
Less
I/O
Faster response
More throughput
Better consistency
34
7/17/2015
©2005 BMC Software
EDM Pool Management
Nuts and Bolts
›Caches access path & internal
EDM Pool
structure definitions
DB2 Database Services
›This pool contains
–DBDs – database descriptors
DBM1
–Skeleton Package and Cursor Tables
(SKPT & SKCT)
CT
DBD
DBD
–Package and Cursor Tables – (PT/CT)
–Authorization cache block for each
DS
SKCT SKCT
plan (optional)
• Except those with CACHESIZE(0)
PT
–Skeletons of dynamic SQL for CACHE
DYNAMIC SQL (optional)
• Could be in data space (VSCR in V7)
–Trigger Packages
35
7/17/2015
CT
©2005 BMC Software
SKPT
DS
CT
CT
SKPT
DS
CT
Consequences if sized incorrectly
› Too Small
– Increased I/O against Directory
• DBD01, SPT01, SCT02
–
–
–
–
Response time degradation due to increased I/O
Increased CPU (e.g. Auth check if SKCT continually stolen)
Re-preparation of Dynamic SQL
Fewer Concurrent Threads
› Too Large
– Wasted storage that could be better used elsewhere
36
7/17/2015
©2005 BMC Software
Obtaining Space in EDM Pool
› Available
– Any unused Storage
– Old CTs and PTs (Completed URIDs)
› Stealable
– SKCTs and SKPTs (Cached Copies for future use)
– Inactive DBDs
› Not Available
– DBDs that are being used by a URID
– CTs and PTs for Active URIDs (Application Copies)
› LRU used for Available then Stealable Storage
– If no Space is available you get a -904 with RC 00C90089
37
7/17/2015
©2005 BMC Software
Best Fit or First Fit for Allocating Space
in the EDM Pool
› Controlled by DSNZPARM setting EDMBFIT
– Only applies to EDM Pools bigger than 40Mb
– Default is EDMBFIT = No (like previous releases)
› EDMBFIT = No
– Uses first available space in LRU chain for any object
– Fragments Storage but better for performance
› EDMBFIT = Yes
– Continues to look for ideal space for required object
– Provides optimal Storage use for VSCR systems…
– Cost is CPU (free Chain Search) and Latch Contention
38
7/17/2015
©2005 BMC Software
BIND Options and the EDM Pool
› ACQUIRE(USE) vs ACQUIRE(ALLOCATE)
– USE requires less space in the EDM Pool (check PLSIZE)
– ALLOCATE uses more resources at Bind Time
– ALLOCATE also turns off selective partition locking
› RELEASE(COMMIT) vs RELEASE(DEALLOCATE)
– COMMIT reduces strain on EDM Pool by freeing CTs earlier
– But it can have a considerable impact on Performance
• e.g. Index Lookaside and Sequential Detection Counters reset
– Consider COMMIT for Infrequently used Packages?
› DEGREE(ANY) vs DEGREE(1)
– DB2 keeps two access paths in Plan in case of fallback
– Check using AVGSIZE column in SYSPLAN or SYSPACKAGE
39
7/17/2015
©2005 BMC Software
Some points to consider…
› Storage Requirement grows each release of DB2
– Especially for DB2 V8 (Unicode, Long Names) but 64 bit helps
› Don’t use 1 plan for batch and 1 for online
– Even with Packages the search time could cause problems
– Difficult to diagnose problems
– Copy of Plan Header gets loaded into EDM Pool for each User
› Auth Cache is stored in the EDMPOOL SKCT
– If your Plans are Granted to Public why waste the space?
› Put Global DSC in Data Space for DB2 V7
– Provides immediate VSCR and allows larger Cache
40
7/17/2015
©2005 BMC Software
… more points
› Java requires program to retrieve data definition
– Provided by DSNZPARM DESCSTAT
– Increases EDM Pool size
› If EDM Pool size is a problem consider CONTSTOR
– DSNZPARM introduced by PQ14391
– Contracts Thread Storage after 50 commits or if it gets >1Mb
– Downside is CPU cost of doing this – main purpose is VSCR
› EDM Pool for Data Sharing likely to be 10% larger
– DDL invalidates DBD in other ssid EDM Pools
– Loaded again the next time it is needed
– In-flight URIDs require old copy – result is >1 copy in EDM Pool
› All objects loaded into EDM Pool use BP0
41
7/17/2015
©2005 BMC Software
How to Monitor the EDM Pool
› All these numbers are available using most Performance Monitors or
via the DB2 Accounting and Statistics traces
› Monitor Free Pages in Chain
– Only reduce EDM Pool size if consistently >20% pool size
› Fails due to Pool Full should be zero
– Any non zero figure is an application failure and impacts a user
› Ratio of Loads of Package & Cursor Sections, DBDs from Pool vs
from DASD
– Ideally should be above 80% for most systems
42
7/17/2015
©2005 BMC Software
EDM Pool Management
Dynamic SQL Cache – What’s it like
› Cached Statements
– Details on statement (text and such)
– Significant reduction in overhead in heavy
dynamic environments where same SQL
statement may be executed many times.
– Increases overall size for EDM Pool
• Specify a dataspace for holding the SQL in
V7 environments
– Skeletons moved to dataspace
• No issues in DB2 V8
– Cache above the bar
43
7/17/2015
©2005 BMC Software
Changes in DB2 V8
› EDM Pool split into 3 components
– DBD Pool - only for DBDs (above Bar)
– Statement Pool - SKPTs for Dynamic SQL (above Bar)
– EDM Pool - for remaining data (still below Bar)
› DBDs are always in V8 Format in DBD Pool
–
–
–
–
Even in Compat Mode
Use Unicode and Long Names - usually larger
In ENFM and NFM they are stored in Directory in V8 Format
In Compat mode they are written in V7 Format
› Using Online Schema causes DBD to grow
44
7/17/2015
©2005 BMC Software
RID Pool Management
Nuts and Bolts
› RID pool used more than you might think
–
–
–
–
RID Pool
List Prefetch
Multi-Index Access
Hybrid Join
Enforce Unique keys when updating multiple rows
› Optimizer looks at RID Pool size
– If it estimates SQL will use >50% pool it won’t use RIDS
› If no space or SQL breaks limits it reverts to a TS scan
– 25% of the rows in the table (minimum 4,075)
– 16 million RIDS
› Consider using REOPT(VARS)
45
7/17/2015
©2005 BMC Software
Sizing and Storage Allocation
› Default size 4Mb
– Maximum is 1,000Mb (V7)
– Defined by MAXRBLK DSNZPARM
› Created at DB2 start up but no space allocated
– Allocated in 16K blocks (V7)
– Each block holds 4,075 4-byte RIDS
› Storage Requirement can quickly get quite large
– 1 million 5-byte RIDs requires almost 5Mb of RID Pool
– And you need twice this if you’re Sorting RIDs
› A single user can use whole Pool at execution time
– Size as large as you can given VSCR and monitor
46
7/17/2015
©2005 BMC Software
RID Pool Management
Key Metrics
Space Allocated – High Water and Current
Space Allocated as needed up to MAXRBLK
Exceeded RDS Limit
More that 25% of table rows being accessed
Need a different access path
Exceeded DM Limit
More that 16 Million RIDS being processed
No Storage Available
RID Pool
Number of failures – incremented each time a
process using RID fails due to lack of space
Allocate more space to the RID Pool
47
7/17/2015
©2005 BMC Software
What changes in DB2 V8
› Partial 64 bit exploitation support provided
› RID Pool split into 2 sections
– RID Map below Bar – probably 90% smaller than V7
– RID Lists stored Above Bar
› Maximum size increased to 10Gb
– 25% Below Bar and 75% Above it
› RID Block size now 32K
› Each RID List can handle double number of RIDs
– Approx 26 million before RID access disabled
48
7/17/2015
©2005 BMC Software
How DB2 Sorts data
› DB2 uses a Sort Algorithm called a Tournament Sort
– This has been built into the Hardware Microcode
– Provides DB2 with highly efficient Sorts
ABLE
DELTA
ZULU
BAKER
MIKE
CHARLIE
X-RAY
49
7/17/2015
ABLE
DELTA
MIKE
ZULU
BAKER
CHARLIE
X-RAY
©2005 BMC Software
ABLE
BAKER
CHARLIE
DELTA
MIKE
X-RAY
ZULU
The Tournament Sort
1.Input data read into the leaf pages of a tree structure
›
›
›
›
At each level of the tree the data is compared to it’s neighbour
The ‘Winner’ (lowest value for an Ascending Sort) moves up the tree
At the top of the tree the sorted entries are placed into Runs
Winning entries are then removed from the tree and the next value inserted
2. If >1 Run is generated then get a Merge phase
› Each Run is in sequence but they have to be merged together
50
7/17/2015
©2005 BMC Software
What gets Sorted?
› All Selected Columns plus Sort Key
SELECT C1, C2, C3, C4, C5
…
ORDER BY C2, C4
Sort Record is C2, C4, C1, C2, C3, C4, C5
› VARCHAR padded to full length + length indicator
› Null Fields include Null indicator
› If LOB column is Selected DB2 Sorts a 51 byte pointer
› Add 16 bytes for interface to Hardware Sort capability
› Total Sort record is rounded up to a Half Word Boundary
51
7/17/2015
©2005 BMC Software
Reduce Data to be Sorted
› If Sorted Record > 4075 bytes DB2 uses a TAG SORT
– Data to be sorted placed directly into 32K Workfile Database
– Sorts the keys plus the Address (RID) of the data
– Retrieves Data using RID resulting from Sort
› If Sort fits into SORTPOOL no Temp Database required
› Both big performance hits so only sort what’s needed
› Don’t select unnecessary columns
– Why Select a column in the WHERE Clause with = predicate?
SELECT C1, C2, C3
FROM TABLE
WHERE C1 = ‘CUSTOMER’
52
7/17/2015
©2005 BMC Software
Workfile Database
› Sorts that don’t fit in SORTPOOL overflow here
› DSNDB07 for non-Data Sharing systems
› Ensure it has it’s own dedicated Bufferpool
– Set VPSEQT = 90-95% (not entirely sequential)
– Set VDWQT & DWQT high (90%) - want to keep pages in Pool
› Should be at least 5 Workfiles
– All same size as allocated in rotation
– Don’t use Secondary
• It will all get used anyway
› Make sure you have a 32K workfile for big sorts
53
7/17/2015
©2005 BMC Software
Monitoring SORT
› Check Accounting and Statistics Reports
– Merge Passes Degraded Low Buffers / Merge Passes Requested
• Indicates Sort Prefetch has been reduced due to Buffer Shortage
– Workfile Request rejected Low Buffers / Workfile Requests
• Reduced degree of Parallel Merge due to Buffer Shortage
– Workfile Requests for Merge Pass / Merge Passes Requested
• If low this tells you on average your SORT Pool is probably OK
› As with all Statistics remember you can get anomalies
› Check Workfile Bufferpool Statistics (easiest if BP7)
– Should have Very High Sequential activity
– High numbers of Synchronous reads indicates problems
› Detailed information by thread in IFCID 95 and 96
54
7/17/2015
©2005 BMC Software
Changes in DB2 V8
› Sort Tree Nodes go above the Bar
– Bottom Level of Sort tree containing the full Sort Data
– By far the largest Storage requirement of a Sort
› Still some SORTPOOL space below the Bar
– Remaining elements of the Sort Tree
– Pointers to the data above the Bar
› Will provide considerable relief from Sort Storage problems
› Reduces overflows into Workfile database
› Maximum Sort Key size 16,000 bytes from 4,000
55
7/17/2015
©2005 BMC Software
Summary
› DB2 Pools are critical to meeting your overall performance objectives
– I/O is still the major component of response time
› This is a broad, complex topic that is affected by database design
and application implementation
– Every DB2 release introduces changes of some kind
› There are lots of sources of information on this topic
– DB2 publications
– Trade publications
– IDUG and IOD sessions
› Remember to take a holistic approach to tuning
– Focusing on only one area can create problems in other areas
56
7/17/2015
©2005 BMC Software