1 / 133

May 21, 2008 • 10:00 a.m. – 11:00 a.m. Platform: DB2 LUW

Session: G10. Identifying and Tuning Suboptimal SQL. Philip K. Gunning Gunning Technology Solutions, LLC. May 21, 2008 • 10:00 a.m. – 11:00 a.m. Platform: DB2 LUW. Objectives. Learn How to Identify suboptimal SQL Identifying the Top 10 SQL Ways you can rewrite or tune the SQL

nat
Download Presentation

May 21, 2008 • 10:00 a.m. – 11:00 a.m. Platform: DB2 LUW

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. Session: G10 Identifying and Tuning Suboptimal SQL Philip K. GunningGunning Technology Solutions, LLC May 21, 2008 • 10:00 a.m. – 11:00 a.m. Platform: DB2 LUW

  2. Objectives • Learn How to Identify suboptimal SQL • Identifying the Top 10 SQL • Ways you can rewrite or tune the SQL • Index solutions, DB2 Explain, Design Advisor • Implementing and Verifying the solution

  3. Outline • The Need to Identify and Tune suboptimal SQL • Suboptimal SQL impacts the business • Poor response time • Lost customers • If web-facing, customers don’t come back • Lost business opportunities • Lost enterprise-wide productivity • Lost revenue • Need for more resources • DBAs, CPU and IO resources, increased network bandwidth • IDENTIFY and TUNE

  4. Outline • Several means available for identifying and capturing suboptimal SQL • Convenience Views • I.E. LONG_RUNNING_SQL • DB2 Snapshots • Application snapshots • Dynamic SQL snapshots • List applications show detail • Administrative Routines

  5. Outline • db2pd (lowest overhead of all monitoring methods)* • Event monitors • Provided with DB2 • Third Party Vendor Tools • Many good tools now available

  6. Outline • OS monitoring tools • PS command with correlation to list applications output • TOPAS (PID correlated with list applications show detail command and application snapshot on the associated agentid) • TOP on other platforms • NMON

  7. Characteristics of Suboptimal SQL • Join predicates missing or not indexed • Local predicates (those in the select list) not indexed for potential index-only access • Order by predicates not indexed or indexes not created with “ALLOW REVERSE SCANS” • Note “ALLOW REVERSE SCANS” now default in DB2 9.5 • Foreign key indexes not defined • Note that EXPLAIN enhanced in DB2 9.5 to show use of FK (RI) • Misunderstanding of IXSCAN operator

  8. Characteristics of Suboptimal SQL • DB2 built-in functions such as UCASE causing IXSCAN of entire index • Generated column • Company culture does not allow time for explain of SQL before it goes into production • Nowadays, this is very prevalent • Developers not aware of explain capabilities and options • Design Advisor not used or misinterpreted

  9. Classes of Predicates • Range Delimiting • Index SARGable • Data SARGable • Residual

  10. Predicate Example Index • For the following predicate rule examples, assume that an index has been created on Col A, Col B, and Col C Asc as follows: • ACCT_INDX: Col A Col B Col C

  11. Predicates • Range Delimiting • Used to bracket an index scan • Uses start and stop predicates • Evaluated by the Index Manager

  12. Range Delimiting Example Col A Col B Col C

  13. Predicates • Index SARGable • Are not used to bracket an index scan • Can be evaluated from the index if one is chosen • Evaluated by the Index Manager

  14. Index SARGable Example Col A Col B Col C

  15. Predicates • Data SARGable • Cannot be evaluated by the Index Manager • Evaluated by Data Management Services • Requires the access of individual rows from the base table

  16. Data SARGable Example Col A Col B Col C

  17. Residual Predicates • Residual Predicates • Cannot be evaluated by the Index Manager • Cannot be evaluated by Data Management Services • Require IO beyond accessing the base table • Predicates such as those using quantified sub-queries (ANY, ALL, SOME, or IN), LONG VARCHAR, or LOB data which is stored separately from the table • Correlated Sub-queries • Are evaluated by Relational Data Services and are the most expensive type of predicates

  18. Residual Predicate Example

  19. Predicate Best Practice • Use Range Delimiting predicates whenever possible • Verify via Explain

  20. DEFAULT FILTER FACTORS

  21. SQL Coding Best Practicesfor Developers Platform: DB2 for Linux, UNIX, and Windows Phil Gunning Principal Consultant, Gunning Technology Solutions, LLC Session: G2 May 23, 2005 12:30 – 1:40

  22. Outline Best Practices Classes of Predicates Index SARGable Range Delimiting Data SARGable Predicate Best Practices Local, Order By, Join Predicates Restricting Results Restrict before joining Selectivity DB2 Catalog Queries/Explain

  23. Outline Index Design Local, Order By, Join predicates Include Columns Uniqueness DB2 Visual Explain/db2exfmt/Design Advisor Monitor and Evaluate Summary

  24. Best Practices 1. Use Range Delimiting and Index SARGable Predicates wherever possible 2. Understand DB2 predicate rules 3. Specify most restrictive predicates first 4. Select only columns that are needed 5. Adhere to proper index design techniques 6. Understand inputs to the Optimizer 7. Developers and DBAs collaborate to design proper indexes 8. Evaluate all SQL using Visual Explain/db2exfmt 9. Use Design Advisor to tune SQL/SQL Workloads 10. Consistently monitor and review application performance

  25. Application Relational Data Services Residual predicates COST Data Management Services Data SARGable predicates Range Delimiting Index SARGable Index Manager Data

  26. RULE#1 Use Range Delimiting and Index SARGable predicates whenever possible

  27. Query Rewrite The DB2 for Linux, UNIX and Windows optimizer contains significant query rewrite capability Still important to write predicates following the local, order by, join rule Query rewrite will take care of most transformations that need to be made However, if predicates are missing or indexes are not available to support the access paths, your SQL will not be able to take advantage of query rewrite

  28. Query Rewrite The DB2 for Linux, UNIX and Windows optimizer contains significant query rewrite capability Still important to write predicates following the local, order by, join rule Query rewrite will take care of most transformations that need to be made However, if predicates are missing or indexes are not available to support the access paths, your SQL will not be able to take advantage of query rewrite

  29. Index Review An index is a data structure that contains column values and a pointer to the table data Primary key – Unique Index If a primary key is defined, DB2 automatically creates a unique index to enforce the PK constraint Secondary Index Created to support access to frequently referenced columns Indexes provide efficient access (in terms of CPU and IO) to columns found in the table Just like an index entry in a book, an index in a database enables rapid lookup of associated table entries

  30. Index Characteristics Index entries are usually much smaller (subset) of all table columns Can fit more index entries on a page Allows for more efficient use of buffer pool Separate index buffer pool Enables often used index pages to remain in the buffer pool longer More logical IO than physical IO

  31. A Word About Index Structures B+ -tree used to store index entries Provides for a tree structure that is balanced to a constant depth from the root to the leaf blocks along every branch Usually more efficient (less costly) than a table scan

  32. Base Table

  33. Select deptnumb, deptname from db2admin.org Where deptnumb < 20

  34. Range Delimiting Example

  35. Table Scan Example Created this index and ran this SQL CREATE INDEX "DB2ADMIN". "YYZZ" ON "DB2ADMIN"."ORG" ("DEPTNUMB" ASC, "DEPTNAME" ASC, "DIVISION" ASC) Select deptnumb, deptname from db2admin.org Where deptnumb =20 and deptname like 'b%' or division = 'midwest' and manager = 88 or location like 'bo%'

  36. Table Scan Example

  37. Table Scan Rules of Thumb If > 20-25% of the rows will be read, good likelihood of table scan If 0.5 – 20% of the rows are read, likely index access but this can vary depending on numerous factors Exact formulas used are complex and not very useful for practical purposes

  38. Rule #2 Understand and apply DB2 predicate rules

  39. WITH DEPT_MGR AS ( SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPARTMENT D, EMPLOYEE E WHERE D.MGRNO=E.EMPNO AND E.JOB='MANAGER' ), DEPT_NO_MGR AS ( SELECT DEPTNO, DEPTNAME, MGRNO AS EMPNO FROM DEPARTMENT EXCEPT ALL SELECT DEPTNO, DEPTNAME, EMPNO FROM DEPT_MGR ), MGR_NO_DEPT (DEPTNO, EMPNO, LASTNAME, FIRSTNME, PHONENO) AS ( SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM EMPLOYEE WHERE JOB='MANAGER' EXCEPT ALL SELECT DEPTNO,EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPT_MGR ) SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM DEPT_MGR UNION ALL SELECT DEPTNO, DEPTNAME, EMPNO, CAST(NULL AS VARCHAR(15)) AS LASTNAME, CAST(NULL AS VARCHAR(12)) AS FIRSTNME, CAST(NULL AS CHAR(4)) AS PHONENO FROM DEPT_NO_MGR UNION ALL SELECT DEPTNO, CAST(NULL AS VARCHAR(29)) AS DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO FROM MGR_NO_DEPT ORDER BY 4

  40. A More “Complicated” Example

  41. Table Scan Example

  42. Created Two Indexes CREATE INDEX "DB2ADMIN"."AABB" ON "DB2ADMIN"."DEPARTMENT" ("DEPTNO" ASC, "DEPTNAME" ASC, "MGRNO" ASC) PCTFREE 10 CLUSTER MINPCTUSED 10 ALLOW REVERSE SCANS; CREATE INDEX "DB2ADMIN"."CCDD" ON "DB2ADMIN"."EMPLOYEE" ("EMPNO" ASC, "FIRSTNME" ASC, "MIDINIT" ASC, "LASTNAME" ASC, "WORKDEPT" ASC, "PHONENO" ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS;

  43. Index Scan Example Index Scan on AABB index

  44. Full Index Scan Index Scan of entire index then fetch from table

  45. Index on: DEPTNAME,DEPTNO MGRNO RIDS Base Table

  46. Selectivity Catalog Queries SELECT INDNAME, NPAGES, CARD, FIRSTKEYCARD AS FIRSTK, FIRST2KEYCARD AS F2KEY, FIRST3KEYCARD AS F3KEY, FIRST4KEYCARD AS F4KEY, FULLKEYCARD AS FULLKEY, NLEAF, NLEVELS AS NLEV, CLUSTERRATIO AS CR, CLUSTERFACTOR AS CF, UNIQUERULE AS U, T.COLCOUNT AS TBCOL, I.COLCOUNT AS IXCOL FROM SYSCAT.TABLES T, SYSCAT.INDEXES I WHERE T.TABSCHEMA = I.TABSCHEMA AND T.TABSCHEMA = ‘PGUNNING' AND T.TABNAME = I.TABNAME AND CARD >20000 ORDER BY CARD DESC, 1;

  47. NLEVELS > 3

More Related