1 / 108

Patrick Bossman IBM Silicon Valley Lab

®. Tuning with Optimization Service Center Part I. Patrick Bossman IBM Silicon Valley Lab. Columbia, MD. September 12, 2007. Agenda. Overview of Optimization Service Center Connect and configure OSC Workload (application) Tuning Query tuning (Part II). Overview.

materia
Download Presentation

Patrick Bossman IBM Silicon Valley Lab

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. ® Tuning with Optimization Service Center Part I Patrick Bossman IBM Silicon Valley Lab Columbia, MD September 12, 2007

  2. Agenda • Overview of Optimization Service Center • Connect and configure OSC • Workload (application) Tuning • Query tuning (Part II)

  3. Overview • Optimization Service Center (OSC) • New no charge product supported for connections to DB2 9. • Support for DB2 for z/OS V8 connections in open beta • Workstation tool for monitoring and tuning of queries • Facilitates the identification and tuning of workloads (sets of queries) as well as individual queries • New powerful query diagnostic tools enable faster deep analysis of queries

  4. Overview • OSC feature list • Support for workload tuning • Stand-alone workload creation • Push-out model (monitoring) • Visual Explain query graphing capabilities • Query formatting and annotation • Query report • Visual Plan Hint • Statistics Advisor - query and workload

  5. Connect and configure • Launch OSC • Initial screen is connection and configuration • Review look and feel • Review layout of screen

  6. Launch OSC

  7. Project navigator area

  8. Project navigator • Navigate through open projects

  9. Top navigation tabs

  10. Top navigator tabs close-up

  11. Connection / configuration

  12. Connection close-up • Connection • Subsystems cataloged in DB2 Connect listed • Use subsystem menu option to add / remove subsystems

  13. Add Subsystem • Add DB2 subsystem connection • Select subsystem button • Choose Add

  14. Connection information • Add DB2 subsystem connection • Subsystem alias is a meaningful name to you • Location, hostname, port • Can be found in <ssid>MSTR address space DDF startup message DSNL004I

  15. DSNL004I • DSNL004I message • Location = location • Domain = hostname • TCPPORT = port

  16. Jeez Pat, Connect already! • Connect to subsystem • Choose Connection, connect • You can connect to more than one subsystem and perform activities on more than one subsystem at a time.

  17. Login information…

  18. Bind packages

  19. Create explain tables

  20. Create explain tables

  21. Create explain tables

  22. Create alias to explain tables • Create aliases • You can use OSC to create ALIASes to the explain tables also.

  23. Create alias to explain tables

  24. Subsystem status

  25. Subsystem status

  26. Explain-enabled Authid’s

  27. EXPLAIN enabled authid’s

  28. Connection complete.Now what?(Welcome menu…)

  29. Go to welcome page…

  30. Welcome page

  31. Welcome page close-ups

  32. Welcome page close-ups

  33. Welcome page close-ups

  34. Application tuning • Application tuning • Creating workloads • Workload options • Workload tuning features

  35. Application tuning process • What is application tuning? • Identify what the application workload is • Individual SQL statements • Get an understanding of the applications behavior • How often are individual SQL statements executing? • What is the performance of individual SQL statements? • Determine statistics for workload • Workload statistics advisor • More workload analysis features coming… (?) • Remeasure workload • Identify top tuning candidates • Use query based tools to further analyze (next session)

  36. Creating workloads • Creating a workload • Tune a workload • View workloads • Monitoring functions

  37. Tune a workload

  38. Workload sources • Workload sources • Snap statement cache • Catalog (static SQL) • QMF / QMF HPO • File • Categories • Other workloads

  39. Workload sources

  40. Catalog source…

  41. Catalog source

  42. Package filter options • Package filter options • Collection id, name, owner, … • Can use equals, like, in, etc.

  43. Package filter options

  44. Plan filter options • Plan filter options • Plan name, plan creator, etc. • Can use equals, like, in, etc.

  45. Plan filter options

  46. Cost & Object filter options • Cost and Object filters • Filters SQL within package / plan filter • Requires the static SQL be bound with explain yes. • DSN_STATEMNT_TABLE must already be populated. • Show only SQL with PROCSU > … • Show SQL which uses table… • Show SQL which uses index… • Choice to return rows which qualify for ANY or ALL of the cost / object filter conditions

  47. Cost & Object filter options

  48. Access path filters • Access path filters • Filters SQL within package / plan filter • Requires the static SQL be bound with explain yes. • PLAN_TABLE must already be populated. • Show SQL which performs… • Tablespace scan • Sort • Non-matching index scan • List prefetch • Outer join • ….

  49. Cost & Object filter options

  50. Capture the workload • After selecting packages / plans / filters… • Click finish to capture workload • OSC goes about the business of collecting the statements… • Queue Jeopardy music, get some coffee.

More Related