1 / 33

DB2 Performance Primer

DB2 Performance Primer. Baltimore/Washington DB2 Users Group December 10, 2003. Michael Murray Senior Consultant michael.murray@ca.com. Presentation Overview. How Does DB2 Get Data? Index Considerations Tablespace Considerations Tune the SQL to Get the Biggest Bang for your Buck

josephgonzalez
Download Presentation

DB2 Performance Primer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DB2 Performance Primer Baltimore/Washington DB2 Users Group December 10, 2003 Michael Murray Senior Consultant michael.murray@ca.com

  2. Presentation Overview • How Does DB2 Get Data? • Index Considerations • Tablespace Considerations • Tune the SQL to Get the Biggest Bang for your Buck • Access Paths – What are the Choices?

  3. DB2 Subsystem Database Tablespace Indexspace Table Index Page Page Row Page Page

  4. What is a Page? 4K GETPAGE Physical I/O Row Limits 8K Your Data 16K PSID – page set identifier 4K page, maximum number of data bytes on the page 4,074. Maximum row size 4,056. Row size greater than 2,020 bytes will end up with just one row per page. 32K

  5. Locking IRLM stored procedures SQL DBM1 Database Services DSNDB01 DSNDB06 Directory Catalog DSNDB07 WorkFile User Data DBM1 Address Space • SQL, DDL, DML, DCL • Optimization • Data Manager • Relational Data System • Buffer Manager • Sort Pool • EDM Pool • RID Pool • Bind/Rebind • IRLM Interaction

  6. STOGROUP GPRVVOL Volume B2RP01 Volume B2RP02 Volume B2RP03 DB2 Stogroups SMS Managed Stogroup CREATE STOGROUP GSMSVOL VOLUMES(“*”) VCAT S0; CREATE STOGROUP GPRVVOL VOLUMES(B2RP01 ,B2RP02 ,B2RP03) VCAT P0; Non-SMS User Managed Stogroup

  7. DB2 Tablespace or Indexspace Allocations (Create, Extent Allocation, Load, Reorg) ACS Routines (4th node is NOT X or I) (4th node is X or I) SMS Storage Group T Storage Group I cache cache cache cache tablespace volume PBL8 indexspace volume PBP8 tablespace volume PB34 indexspace volume PBP2 SMS Allocations

  8. DSNDB01DSNDB06 DSNDB07 Default Tablespace Default Indexspace DSNDB04 BP7 BP4 BP48 BP49 BP0 Random Tablespaces Critical Path Indexes ERP Indexes CRM Indexes Other Indexes BP12 BP1 BP3 BP5 BP9 Critical Path Tablespace ERP Tablespaces CRM Tablespaces Other Tablespaces 32K Tablespaces BP2 BP6 BP8 BP10 BP32K Buffer Pool Strategy

  9. Group Buffer Pool (page not found) STAGE 2 PREDICATES (page found) Relational Data System Hiperpool cache (page found) indexspace (page not found) Physical I/O STAGE 1 PREDICATES BUFFER POOL DATA MANAGER cache (page not found) indexable sargable Get Page Request tablespace Buffer Manager

  10. SORT POOL SORT required Filtered Result Set 1. selected columns 2. calc/derived values 3. sort columns sort assist hardware Sort assist hardware? STAGE 2 PREDICATES BP7 • No matching index • DISTINCT • ORDER BY • UNION • GROUP BY • Some Joins Relational Data System DSNDB07 STAGE 1 PREDICATES DATA MANAGER CREATE INDEX DEFER NO indexable sargable key columns, RIDs DB2’s Work File Explained

  11. Index Considerations • Clustering Vs. Non Clustering • Unique Vs. Duplicate • Piecesize with NPI’s • Uniqueness, Performance, RI, Sorting • Table Size • Minimize I/O

  12. Indexes: Clustered Index on: Elevation (desc), State, Peak Name Non-Clustered Index on: Peak Name Unique PK Index on: Latitude, Longitude Peaks > 13,999 Feet Located in US Goal of indexes is to minimize I/O

  13. Page 501 CLUSTERED INDEX Page 552 20320 AK Mount McKinley … p. 501, 2 14831 AK Mount Bear 14831 AK Mount Bear … 14420 CO Mount Harvard p. 502, 2 • Elevation (desc) • State • Peak Name Page 502 14494 CA Mount Whitney … 14420 CO Mount Harvard … Page 503 14410 WA Mount Rainier … Page 553 14270 CO Grays Peak … p. 503, 2 14270 CO Grays Peak Page 717 14196 CO Mount Yale p. 504, 2 Page 504 14420 CO Mount Harvard p. 552, 2 14255 CO Longs Peak … 14196 CO Mount Yale p. 553, 2 14196 CO Mount Yale … 14059 CO Sunlight Peak p. 554, 2 14000 CA Thunderbolt Peak p. 555, 2 Page 505 Page 554 14172 CO Mount Bross … ROOT Page p. 505, 2 14110 CO Pikes Peak 14110 CO Pikes Peak … 14059 CO Sunlight Peak p. 506, 2 Page 506 14080 CA Starlight Peak … 14059 CO Sunlight Peak … Page 507 Page 555 14058 CA Split Mountain … 14037 CO Little Bear Peak p. 507, 2 14037 CO Little Bear Peak … 14000 CA Thunderbolt Peak p. 508, 2 Page 508 NON-LEAF Page 14005 CO Mount o/t Holy Cross … 14000 CA Thunderbolt Peak … LEAF & DATA Page

  14. Page 401 Page 501 NON-CLUSTERED INDEX Grays Peak p. 503, 2 20320 AK Mount McKinley … Little Bear Peak p. 507, 2 14831 AK Mount Bear … Page 301 • Peak Name Little Bear Peak p. 401, 2 Page 402 Page 502 Mount Bear p. 402, 2 14494 CA Mount Whitney … Longs Peak p. 504, 1 14420 CO Mount Harvard … Mount Bear p. 501, 2 Page 201 Page 403 Page 503 Mount Bear p. 301, 2 p. 505, 1 Mount Bross 14410 WA Mount Rainier … Mount o/t Holy Cross p. 302, 2 Mount Harvard p. 502, 2 14270 CO Grays Peak … Page 302 Page 404 Page 504 Mount Harvard p. 403, 2 Mount McKinley p. 501, 1 14255 CO Longs Peak … Page 101 Mount o/t Holy Cross p. 404, 2 Mount o/t Holy Cross p. 508, 1 14196 CO Mount Yale … Mount o/t Holy Cross p. 201, 2 Page 405 Page 505 Thunderbolt Peak p. 202, 2 Mount Rainier p. 503, 1 14172 CO Mount Bross … Page 303 ROOT Page Mount Whitney p. 502, 1 14110 CO Pikes Peak … Mount Whitney p. 405, 2 Pikes Peak p. 406, 2 Page 406 Page 506 Mount Yale p. 504, 2 14080 CA Starlight Peak … Page 202 Pikes Peak p. 505, 2 14059 CO Sunlight Peak … Pikes Peak p. 303, 2 Page 407 Page 507 Thunderbolt Peak p. 304, 2 14058 CA Split Mountain … Split Mountain p. 507, 1 Page 304 14037 CO Little Bear Peak … NON-LEAF Page Starlight Peak p. 506, 1 Starlight Peak p. 407, 2 Page 408 Page 508 Thunderbolt Peak p. 408, 2 Sunlight Peak p. 506, 2 14005 CO Mount o/t Holy Cross … Thunderbolt Peak p. 508, 2 NON-LEAF Page 14000 CA Thunderbolt Peak … DATA Page LEAF Page

  15. PRIMARY NON-CLUSTERED INDEX Page 461 Page 501 363438N 1181733W p. 502, 1 20320 AK Mount McKinley … • Latitude • Longitude 370116N 1182520W p. 507, 1 14831 AK Mount Bear … Page 331 370116N 1182520W p. 461, 2 Page 462 Page 502 370539N 1183124W p. 462, 2 14494 CA Mount Whitney … 370539N 1183119W p. 506, 1 14420 CO Mount Harvard … 370539N 1183124W p. 508, 2 Page 281 Page 463 Page 503 370539N 1183124W p. 331, 2 373400N 1052948W p. 507, 2 14410 WA Mount Rainier … 385039N 1061848W p. 332, 2 373738N 1073543W p. 506, 2 14270 CO Grays Peak … Page 332 Page 464 Page 504 373738N 1073543W p. 463, 2 14255 CO Longs Peak … p. 505, 2 385026N 1050238W Page 101 385039N 1061848W p. 464, 2 385039N 1061848W p. 504, 2 14196 CO Mount Yale … p. 281, 2 385039N 1061848W Page 465 Page 505 630410N 1510013W p. 282, 2 385528N 1061912W p. 502, 2 14172 CO Mount Bross … Page 333 ROOT Page 14110 CO Pikes Peak … 392007N 1060625W p. 505, 1 392007N 1060625W p. 465, 2 393802N 1054901W p. 466, 2 Page 466 Page 506 392805N 1062845W p. 508, 1 14080 CA Starlight Peak … Page 282 393802N 1054901W p. 503, 2 14059 CO Sunlight Peak … 393802N 1054901W p. 333, 2 Page 467 Page 507 630410N 1510013W p. 334, 2 14058 CA Split Mountain … 401517N 1053655W p. 504, 1 Page 334 14037 CO Little Bear Peak … NON-LEAF Page 465110N 1214531W p. 503, 1 465110N 1214531W p. 467, 2 Page 468 Page 508 630410N 1510013W p. 468, 2 611702N 1410832W p. 501, 2 14005 CO Mount o/t Holy Cross … 630410N 1510013W p. 501, 1 NON-LEAF Page 14000 CA Thunderbolt Peak … LEAF Page DATA Page

  16. Adding Columns to an Index • Improves filtering • Provides index only access • Increases matched columns • Provides a sequence to avoid SORTS • Increases cluster ratio • Helps in index screening • Is the increased RID chain length negatively impacting your non-read transactions?

  17. Indexes & Stats: PK Index on: Latitude, Longitude, Climber_ID, Strt_DT NPI: Climber_ID, Summit, O2, Guided RUNSTATS TABLESPACE dbname.tsname INDEX(ALL) 1st Column & Fullkey Cardinality on all indexes Top 10 most frequent values for 1st column of all indexes RUNSTATS INDEX owner.ixname FREQVAL xx NUMCOLS xx Give me the top 15 climbed peaks (FREQVAL 15 NUMCOLS 2 for PK index) PEAKS CLIMBED TABLE SORT Required? • SELECT CLIMBER_ID, LATITUDE, LONGITUDE, O2, GUIDED FROM PEAKS_CLIMBED • WHERE CLIMBER_ID = ‘CE000008’ AND • SUMMIT = ‘YES’ ORDER BY O2, GUIDED WITH UR; Index Statistics

  18. Page 307 Page 501 RUNSTATS TABLESPACE dbname.tsname INDEX(ALL) RUNSTATS INDEX DB2.STELEV FREQVAL 10 NUMCOLS 2 AK 20320 20320 AK Mount McKinley … p. 501, 1 AK 14831 p. 501, 2 14831 AK Mount Bear … SELECT PEAKNAME FROM PEAKS WHERE STATE = ‘CO’ AND ELEVATION =‘14110’; SELECT PEAKNAME FROM PEAKS WHERE STATE = ‘CO’; Page 308 Page 502 CA 14494 p. 502, 1 14494 CA Mount Whitney … CA 14080 p. 506, 1 14420 CO Mount Harvard … Page 309 Page 503 CA 14058 p. 507, 1 14410 WA Mount Rainier … CA 14000 p. 508, 2 14270 CO Grays Peak … Page 310 Page 504 CO 14420 p. 502, 2 14255 CO Longs Peak … CO 14270 p. 503, 2 14196 CO Mount Yale … Page 311 Page 505 CO 14255 p. 504, 1 14172 CO Mount Bross … CO 14196 p. 504, 2 14110 CO Pikes Peak … Page 312 Page 506 CO 14172 p. 505, 1 14080 CA Starlight Peak … CO 14110 p. 505, 2 14059 CO Sunlight Peak … Page 313 Page 507 CO 14059 p. 506, 2 14058 CA Split Mountain … CO 14037 p. 507, 2 14037 CO Little Bear Peak … CREATE INDEX DB2.STELEV ON DB2.PEAKS STATE, ELEVATION DEFER YES PIECESIZE 2G; Page 314 Page 508 CO 14005 p. 508, 1 14005 CO Mount o/t Holy Cross … WA 14410 p. 503, 1 14000 CA Thunderbolt Peak … LEAF Page DATA Page

  19. Page 461 Page 501 363438N 1181733W 20320 AK Mount McKinley … p. 502, 1 370116N 1182520W p. 507, 1 14831 AK Mount Bear … Page 502 Page 462 RID LIST 14494 CA Mount Whitney … 370539N 1183119W p. 506, 1 363438N 1181733W p. 502, 1 14420 CO Mount Harvard … 370539N 1183124W p. 508, 2 370116N 1182520W p. 507, 1 Page 463 370539N 1183119W p. 506, 1 Page 503 14410 WA Mount Rainier … p. 507, 2 373400N 1052948W 370539N 1183124W p. 508, 2 373738N 1073543W p. 506, 2 373738N 1073543W p. 506, 2 14270 CO Grays Peak … Page 464 Page 504 (poor cluster ratio) 385026N 1050238W p. 505, 2 physical I/O order 14255 CO Longs Peak … 385039N 1061848W p. 504, 2 14196 CO Mount Yale … Page 465 Page 505 Sorted RID LIST 385528N 1061912W p. 502, 2 14172 CO Mount Bross … 363438N 1181733W p. 502, 1 392007N 1060625W p. 505, 1 14110 CO Pikes Peak … 370539N 1183119W p. 506, 1 Page 466 Page 506 373738N 1073543W p. 506, 2 392805N 1062845W p. 508, 1 14080 CA Starlight Peak … 370116N 1182520W p. 507, 1 393802N 1054901W p. 503, 2 14059 CA Sunlight Peak … 370539N 1183124W p. 508, 2 Page 467 Page 507 SELECT ELEVATION, PEAKNAME FROM PEAKS WHERE LATITUDE IN (‘363438N’,’370116N’,’370539N’,’373738N’) 14058 CA Split Mountain … 401517N 1053655W p. 504, 1 14037 CO Little Bear Peak … 465110N 1214531W p. 503, 1 Page 468 Page 508 611702N 1410832W p. 501, 2 14005 CO Mount o/t Holy Cross … = unwanted disk head movement 630410N 1510013W p. 501, 1 14000 CA Thunderbolt Peak … List Prefetch

  20. Segmented Tablespace - 3 table example Simple Tablespace - 1 table example Simple Tablespaces can have multiple tables; however, rows can be intermingled. Use with caution on small static read only tables. Spacemap keeps track of segments (pages) by table. Spacemap page in a segmented tablespace helps when a program deletes without a where clause by marking those segments logically deleted. Spacemap page also makes segments tied to dropped tables accessible. Segsize between 4-64 increasing in increments of 4. See recommendations in notes section. Partitioned Tablespace - NUMPARTS 18 Segmented Tablespace - 1 table example Partitioned tablespaces allow only 1 table. Maximum number of partitions 254. Above 64 considered Large. Tablespace Types

  21. Table Space Scans Sequential I/O Buffer Manager initiates a GETPAGE request for P1 through P16. SELECT PEAKNAME FROM PEAKS ORDER BY RATING; LEGEND AK CA Random I/O CO Buffer Manager initiates a GETPAGE request for P3 and P13. WA SELECT PEAKNAME FROM PEAKS WHERE STATE = ‘CA’ AND RATING = ‘MODERATE’;

  22. Page 501 20320 AK Mount McKinley … 14831 AK Mount Bear … Page 502 ACTIVE 6 out of 8 SELECT PEAKNAME FROM PEAKS WHERE STATE = ‘CO’; 14494 CA Mount Whitney … 14420 CO Mount Harvard … Page 503 14410 WA Mount Rainier … 14270 CO Grays Peak … p. 502 p. 503 p. 504 p. 505 p. 506 p. 507 p. 508 p. 532 p. 570 Page 504 14255 CO Longs Peak … 1 1 1 1 1 1 24 38 14196 CO Mount Yale … Page 505 14172 CO Mount Bross … NOT ACTIVE 3 out of 8 14110 CO Pikes Peak … SELECT PEAKNAME FROM PEAKS WHERE STATE = ‘AK’; Page 506 14080 CA Starlight Peak … 14059 CO Sunlight Peak … Page 507 p. 501 p. 502 p. 522 p. 542 p. 562 p. 564 p. 566 p. 583 p. 630 14058 CA Split Mountain … 14037 CO Little Bear Peak … 1 20 20 20 2 2 17 47 Page 508 14005 CO Mount o/t Holy Cross … (data access sequential = 4 out of last 8 pages are page-sequential) 14000 CA Thunderbolt Peak … Sequential Detection

  23. Minimize Logging Blue represents static columns that do not change once the row is created Teal represents column grouping updated associated to monthly inventory transactions Purple represents column grouping updated associated to daily billing transactions Dashed box represents variable data Default column placement from data model. Adjusted column placement taking logging considerations into play.

  24. Tuning SQL • Tuning Steps • Static and Dynamic SQL • Get What You Need, Not What You Want • Reoptimization • Explain & Optimization Hints

  25. WHAT is “Estimate & Select Access Path with Cheapest Estimated Cost ” • Proper statistics can reduce the amount of SQL tuning required • Lackluster statistics can be a Pandora's box! • REBIND DATE < RUNSTATS DATE • RUNSTATS performed yearly • EXPLAIN(NO) • Optimizer does not use Real Time Stats for access path selection • Better filtering = fewer rows returned

  26. SELECT * FROM TBL sort filtered result set DSNDB07 DASD relational data system GETPAGE REQUEST buffer manger data manager BP3 DBM1 Address Space SELECT C5,C6,C7 FROM TBL sort filtered result set DSNDB07 DASD relational data system GETPAGE REQUEST buffer manger data manager BP3 Get Only What You Need

  27. IT_ROLE (parent) (DB2 RI) PROJECT_ROLE (child) . Insert new row to add a new role (data modeler) . User, Programmer, or DBA can insert the row . Slower performance DELETE RESTRICT PROJECT_ROLE(column constraint) . DDL change to add a new role (data modeler) . DBA involvement needed for the table change . Better Performance, Tighter Control Performance, Maintenance, Control

  28. REOPT(VARS) – Useful when DB2’s estimate of qualifying rows would benefit if the real host variable was available to the optimizer at execution Evaluate data values at runtime Limit parts for partition scans Influence join sequence Reoptimization is done at open cursor Static SQL – package level. Consider isolating/consolidating your reopt statements to a few static packages. Dynamic SQL – statement level. More granular and easier to invoke on a smaller scale. IFCID 0022 – Did you get a better access path? Would static SQL benefit from being dynamic and taking advantage of dynamic cache? Sounds Great! Difficult to implement and maintain. DSNZPARM change to activate Programmers should add QUERYNO to their code. How are you going to name and manage your opthints? CHAR(8) After code has been bound with explain yes, need to update plan_table rows to add a OPTHINT. To return to the good access path you have previously established, you would rebind your package with OPTHINT(‘ohstring’). Verify hint is in use! SQL code +394, HINT_USED column of PLAN_TABLE, or Query special register: CURRENT OPTIMIZATION HINT Getting Your Way REOPTIMIZATION OPTIMIZATION HINTS

  29. Access Paths • More on QUERYNO = 103; • DB2’s Choices • Understanding Predicates • Small Tables in Memory • Plan_Table Considerations

  30. ACCESSTYPE in Plan Table R Table space scan, segmented tablespace Table space scan, non-segmented tablespace Table space scan, partition scan (scanning limited parts) I mc>0 Matching index scan (using IX structure - root, non-leaf, leaf) MATCHCOLS I mc=0 Non-matching index scan (scan of index leaf pages) I1 One fetch index access (min/max) PREFETCH N Perform 1 matching index scan for each value in (LIST) INDEXONLY M Multiple index scan (same index twice or two different indexes) MX Qualifying RIDs for each index used in multiple index scan MI AND predicate, intersection of the qualifying RIDs from each index OPTHINT MU OR predicate, union of the qualifying RIDs from each index PRIMARY_ACCESSTYPE D Direct row access via ROWID (page#, rid). One getpage! ACCESNAME RID= record identifier

  31. Predicates • Know Your Predicates • Indexable, matching predicates on index key columns • Stage 1, not picked as matching but still refer to index columns (index screening) • Stage 1, operating on a data page • Stage 2, everything else • Application Programming and SQL Guide Table 68, page 629, DB2 for OS/390 and z/OS V7

  32. PEAKS Table

  33. Questions... ?

More Related