IDUG 20 Invaluable DB 2 LUW Performance Insights
#IDUG 20 Invaluable DB 2 LUW Performance Insights from Around the Globe Scott Richard Hayes DBI Software Session Code: D 4 Monday 11 November, 16: 30 -17: 30 | Platform: DB 2 for Linux, UNIX, Windows
#IDUG 2 Submitted to IDUG… Abstract / Bullet Points • Twenty useful, practical, and immediately actionable DB 2 LUW performance tips and insights, with SQL and command examples, supporting the most popular and newest releases. • Bullets: • Share 20 actionable tips and insights that attendees can put into practice straight away and leave IDUG feeling it was an invaluable experience. • Entertain people. The best IDUG presentations make people laugh and smile. • Avoid politics and other offensive things. • Inspire attendees to become presenters and volunteers. • Finish on time. Nobody likes to be late for lunch or the next session!
#IDUG 3 Our Agenda… • Whatever inspires me to inspire you to greatness! • Wit & Wisdom • Thoughts on Identifying Tuning Opportunities • + Solutions! • Numerous Pearls of DB 2 Advice
#IDUG 4 DB 2 World Traveler…
#IDUG 5 Forget about what you think you might know… How do you find actionable tuning opportunities? • “There are no such things as problems – only opportunities for improvement” • db 2 pd, db 2 top, snapshots, SQL, tools… • Numbers, rates, ratios, time spent, costs…
#IDUG 6 How much does it weigh? TOTAL weight and RELATIVE weight…
#IDUG 7 We have a WEIGHT “Opportunity for Improvement”
#IDUG “A well stated problem is a problem half solved” - Charles Kettering WISDOM
#IDUG 9 This begs the question… What’s weighing down your beloved database? • Under what rock do you look first? • • SQL? Tablespaces? Bufferpools? Partitions? Family Members? Database? TABLES!
#IDUG 10 TABLES @ The HEART of Physical Design • Top Tables by Rows Read, Rows Written, and Overflows • Drop/Add Indexes • Volatile, PCTFREE • MDC, Partitioning (hash, range), AST, Organize by Row or Column (BLU)
#IDUG 11 Table Performance Analysis Table Rows Read per Transaction (TBRRTX) & WEIGHT • Not every TX accesses every table, so we expect Rows Read/#TX to be a small average, normally < 10, and often 3 or less • TBRRTX tells you where you have Data Page scans occurring • > 10, likely opportunity for improvement • > 100, definitely opportunity for improvement • > 1, 000, crisis! DO NOT UPGRADE HARDWARE • In addition to the cost per TX, find the % of DB Rows Read (Relative Weight) by expressing Table Rows Read x 100 / Sum of all Rows Read.
#IDUG 12 SQL Handout Table Read Cost and Relative Weight select substr(a. tabschema, 1, 20) as TABSCHEMA, substr(a. tabname, 1, 25) as TABNAME, a. rows_read as Rows. Read, CAST((((A. ROWS_READ) * 100. 0) / (Select (SUM(Z. ROWS_READ) + 1. 0) FROM SYSIBMADM. SNAPTAB Z WHERE A. DBPARTITIONNUM = Z. DBPARTITIONNUM )) AS DECIMAL(5, 2)) AS PCT_DB_TB_ROWSREAD, CAST( (a. rows_read / (b. commit_sql_stmts + b. rollback_sql_stmts + 1. 0)) AS DECIMAL(13, 3)) as TBRRTX from SYSIBMADM. snaptab a, SYSIBMADM. snapdb b where a. dbpartitionnum = b. dbpartitionnum order by a. rows_read desc fetch first 20 rows only;
#IDUG 13 Examples Table Relative Weights and Read I/O Costs
#IDUG “One cannot appreciate what one does not know” - Scott Hayes WISDOM
#IDUG http: //www. dbisoftware. com/blog/db 2_performance. php? id=123 • How DB 2 Sees the SQL Workload: Relative Costs Select c 1, c 2, c 4 from tbl where c 5 ‘ 0210’ cpu=. 1 Select c 8== >‘ 0360’ ‘ 0680’ ‘ 0120’ ‘ 0220’ cpu=. 2 ‘ 0500’ ‘ 8800’ ‘ 0300’ ‘ 0450’ ‘ 0490’ ‘ 0670’ ‘Bob’ ‘ 0110’ ‘ 0190’ ‘ 0390’ ‘ 0790’ ‘ 2380’ ‘ 4560’ cpu=. 1 cpu=. 3 100’s of SQL statements per second… SQL Snapshot shows 19 different statements! WRONG ANSWER! • How the DBA needs to see the SQL Workload: SQL Statement Count Tot. CPU% Select c 1, c 2, c 4 from tbl where c 5 = ‘? ’ 10 11 12 13 14 15 16 987654321 . 1. 2. 3. 4. 5. 6. 7. 8. 9 1. 0 1. 1 1. 2 1. 3 1. 4 1. 5 1. 6 66. 6 Select c 1, c 2, c 4 from tbl where c 5 > ‘? ’ 21 . 3. 6 25. 0 Select c 1, c 2, c 4 from tbl where c 8 = ‘? ’ 1 . 2 8. 33 19 2. 4 100. 00 US Patent # 6, 772, 411 Totals: “Costly SQL” – Aggregate Costs with Relative WEIGHTS
#IDUG 16 SQL WEIGHTS Aggregated, Concentrated Costs, & their WEIGHTS • Now that you know the TABLES with the heaviest WEIGHTS, what is the heavy SQL driving I/O to the heavy tables? • STMT_TEXT like %TABLE_NAME% has some limitations • What are the HEAVIEST SQL – By table? Across the DB? • • CPU % Rows Read % Logical Reads % Physical Reads % Rows Written % Execution Time % Sort Time %
#IDUG 17 SQL HEAVY WEIGHTS by CPU Time (microseconds)
#IDUG 18 SQL HEAVY WEIGHTS by CPU Time (microseconds) - Examples
#IDUG 19 SQL HEAVY WEIGHTS by Rows Read
#IDUG 20 SQL HEAVY WEIGHTS by Rows Read - Examples
#IDUG “ 2 EARS. 1 MOUTH. That’s an important ratio. Listen twice as much as you talk. ” -Scott Hayes WISDOM
#IDUG “No one cares about how much you know until they know how much you care” -Scott Hayes WISDOM
#IDUG Logical Read Costs: B-Tree Index Structure LR NLEVELS =3 LR LR LR PR ROOT page Intermediate (non-leaf) pages Leaf Pages NLEAF = 5 Data Pages CREATE UNIQUE INDEX EMPNOIX on EMPLOYEE (EMPNO); SELECT * FROM EMPLOYEE WHERE EMPNO = ‘ 000210’; LR + LR to IX + (LR to Data + PR to Data) = ¼ or 25% miss ratio, or 100 – 25% = 75% Hit Ratio for the query (assumes IX pages in BP)
#IDUG 24 SQL HEAVY WEIGHTS by Logical Reads (Total, Index, or Data)
#IDUG 25 SQL HEAVY WEIGHTS by Logical Reads (Total, Index, or Data) - Examples
#IDUG 26 SQL HEAVY WEIGHTS by Physical Reads (Total, Index, or Data)
#IDUG 27 SQL HEAVY WEIGHTS by Physical Reads (Total, Index, or Data) - Examples
#IDUG 28 SQL HEAVY WEIGHTS by Rows Written
#IDUG 29 SQL HEAVY WEIGHTS by Rows Written -EXAMPLES
#IDUG 30 SQL HEAVY WEIGHTS by Execution Time
#IDUG 31 SQL HEAVY WEIGHTS by Execution Time - Examples
#IDUG 32 SQL HEAVY WEIGHTS by SORT Time (SORT is still a 4 Letter Word!)
#IDUG 33 SQL HEAVY WEIGHTS by SORT Time - EXAMPLES
#IDUG “If you bring a problem to the table, bring solutions too. You’ll be more popular!” -Scott Hayes WISDOM
#IDUG 35 We’ve seen how to find problems… LET’S EXPLORE OPTIMIZED SOLUTIONS!
#IDUG 36 #1 SOLUTION: Having the RIGHT indexes to lower execution cost! • Good Indexes: • Are Used! • Have High FULLKEYCARD compared to TBCARD • > 5% giving a rid list of 20 or fewer RIDs for each distinct column value combination (ROT) • Satisfy the Sargable Predicates (>=, <=, =, >, <) of WHERE clauses • Reduce Scans & Sorts
#IDUG 37 #1 Solution – Part 2 NOT HAVING BAD (Low Cardinality) INDEXES!
#IDUG 38 #1 Solution – Part 2 NOT HAVING BAD INDEXES! BAD EXAMPLE!
#IDUG 39 #2 Solution Write Better SQL • TELL DB 2 WHAT YOU WANT, • NOT WHAT YOU DON’T WANT, • AND DON’T BE INDECISIVE • For goodness sakes! • BEWARE of Expressions • Relief in DB 2 V 10. 5
#IDUG 40 #3 Solution Getting out the Big Guns when #1 & #2 don’t work • MDC, MQT, AST Tables, Range and/or Hash Partitioning • Bufferpool tuning • Aligning Randomly Read tablespaces with Bufferpools intended to do Random I/O • Aligning Prefetched tablespaces with Bufferpools intended to do Prefetch I/O • Prefetch Bufferpools should use NUMBLOCKPAGES @ ~3% of Bufferpool size • Check DB 2_PARALLEL_IO registry value • Check NUM_IOSERVERS, PREFETCHSIZE, EXTENTSIZE • Remember, you can only hide so many performance problems in memory until you achieve CPU exhaustion with more concurrent work
#IDUG 41 More about INDEXES V 10. 1 & V 10. 5 • Jump Scans introduced in V 10. 1 • A more “efficient” means for using sub-optimal indexes? • V 9. 7 to V 10. 1 upgrade of E-COM DB gave 13 out of 4, 267 unique SQL patterns with lower LREAD costs and improved results. • V 10. 5 • Indexes on Expressions! YAY! DB 2 caught up to Oracle 8 i (LOL) • CREATE INDEX EMPUP ON EMPLOYEE (UPPER(LASTNAME), EMPNO) • New RANDOM option specifies that index entries are to be kept in random order of the column values • Cannot be used with CLUSTER indexes, ALLOW REVERSE SCANS • Create Index MYSCHEME. MYIX on MYSCHEME. MYTABLE (MY_AWESOME_COLUMN RANDOM); • Might mitigate the need to use PAGE SPLIT HIGH if queries have only = predicates
#IDUG 42 Is DB 2 BLU Right for YOU? ORGANIZE BY COLUMN TABLES – “Load & Go” • Do you have tables that are very heavily READ? • Do these have minimal, or zero, WRITE Activity? • Are they free from obnoxious unsupported data types like LOBs? • Does the database not violate any restrictions? • • • pure. Scale®, partitioned databases, DB w/out automatic storage Tablespaces not enabled for reclaimable storage, no HADR Must be UNICODE and IDENTITY or IDENTITY 16 No use of DB 2_COMPATIBILITY_VECTOR Others: http: //pic. dhe. ibm. com/infocenter/db 2 luw/v 10 r 5/topic/com. ibm. db 2. luw. admin. dbobj. doc/c 0060592. html • Do the SQL queries reference very few columns, generally? • Do SQL queries do a lot of grouping, aggregation, summarizing? • … Then DB 2 BLU 10. 5 ORG by COL might be a great solution!
#IDUG Take a picture of your luggage before you fly- easy description when lost - documents bag condition WISDOM
#IDUG 44 The latest news… IBM Enhances Mt. Rushmore, Salutes Oracle!
#IDUG 45 Final Thoughts…
#IDUG Scott Richard Hayes DBI Software Sales@DBISoftware. com Session: D 4 Title: Sage Advice: 20 Invaluable DB 2 LUW Performance Insights from Around the Globe Please fill out your session evaluation before leaving!
- Slides: 46