Performance Management: Using IBM InfoSphere Optim Performance Manager and Query Workload Tuner

Performance Management: Using IBM InfoSphere Optim Performance Manager and Query Workload Tuner

Released October 2013
Publisher(s): IBM Redbooks
ISBN: 9780738438450

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This IBM® Redbooks® publication describes the architecture and components of IBM InfoSphere® Optim™ Performance Manager Extended Edition. Intended for DBAs and those involved in systems performance, it provides information for installation, configuration, and deployment. InfoSphere Optim Performance Manager delivers a new paradigm used to monitor and manage database and database application performance issues. It describes product dashboards and reports and provides scenarios for how they can be used to identify, diagnose, prevent, and resolve database performance problems.

IBM InfoSphere Optim Query Workload Tuner facilitates query and query workload analysis and provides expert recommendations for improving query and query workload performance. Use InfoSphere Optim Performance Manager to identify slow running queries, top CPU consumers, or query workloads needing performance improvements and seamlessly transfer them to InfoSphere Optim Query Workload Tuner for analysis and recommendations. This is done using query formatting annotated with relevant statistics, access plan graphical or hierarchical views, and access plan analysis. It further provides recommendations for improving query structure, statistics collection, and indexes including generated command syntax and rationale for the recommendations.

Please note that the additional material referenced in the text is not available from IBM.

Table of contents

  1. Front cover
  2. Notices
    1. Trademarks
  3. Preface
    1. Authors
    2. Now you can become a published author, too!
    3. Comments welcome
    4. Stay connected to IBM Redbooks
  4. Part 1 Strategy for performance management
  5. Chapter 1. Guided approach to performance management
    1. 1.1 Overall IBM InfoSphere approach
      1. 1.1.1 Products in the InfoSphere Optim performance management solution
    2. 1.2 What is new in the products
      1. 1.2.1 InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows V5.1
      2. 1.2.2 InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows V5.2
      3. 1.2.3 InfoSphere Optim Query Workload Tuner for DB2 Linux, UNIX, and Windows
      4. 1.2.4 IBM InfoSphere Optim Configuration Manager for DB2 for Linux, UNIX, and Windows
      5. 1.2.5 IBM InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows V3.2
      6. 1.2.6 IBM Data Studio V3.2
    3. 1.3 InfoSphere Optim Performance Manager introduction
      1. 1.3.1 Features
      2. 1.3.2 Packaging
      3. 1.3.3 InfoSphere Optim Performance Manager architecture
    4. 1.4 InfoSphere Optim Query Workload Tuner
      1. 1.4.1 Packaging
    5. 1.5 Looking ahead
  6. Chapter 2. Planning your InfoSphere Optim Performance Manager deployment
    1. 2.1 Installation roadmaps
      1. 2.1.1 New or direct installation
      2. 2.1.2 Update installation
      3. 2.1.3 Migrating from Performance Expert
    2. 2.2 Prerequisites
      1. 2.2.1 InfoSphere Optim Performance Manager
      2. 2.2.2 Optim Performance Manager Extended Insight
      3. 2.2.3 Integration with InfoSphere Optim Query Workload Tuner
      4. 2.2.4 Integration with InfoSphere Optim Configuration Manager
    3. 2.3 Installation parameters
    4. 2.4 Capacity planning
      1. 2.4.1 Parameters that affect InfoSphere Optim Performance Manager resources
      2. 2.4.2 Estimating resource requirements
      3. 2.4.3 Concierge Program for InfoSphere Optim Performance Manager
      4. 2.4.4 Enabling compression
      5. 2.4.5 Network bandwidth
    5. 2.5 Storage options
      1. 2.5.1 Table space type selection
      2. 2.5.2 Table space naming and usage
      3. 2.5.3 Table space location
      4. 2.5.4 Table space DDL
    6. 2.6 User security
      1. 2.6.1 Web console users
      2. 2.6.2 Monitored database users
      3. 2.6.3 Objects in the monitored database
      4. 2.6.4 Special considerations for direct repository access
  7. Chapter 3. Installing and configuring Optim performance management tools
    1. 3.1 Installing and running InfoSphere Optim Performance Manager
      1. 3.1.1 Installing InfoSphere Optim Performance Manager
      2. 3.1.2 Activating the InfoSphere Optim Performance Manager license
      3. 3.1.3 Updating InfoSphere Optim Performance Manager
      4. 3.1.4 Starting and stopping InfoSphere Optim Performance Manager
    2. 3.2 Configuring InfoSphere Optim Performance Manager
      1. 3.2.1 Configuring console security for user access
      2. 3.2.2 Adding and configuring database monitoring
      3. 3.2.3 Monitoring templates and proven practices
      4. 3.2.4 Consider impact on the monitored database and applications
      5. 3.2.5 Other InfoSphere Optim Performance Manager configurations
    3. 3.3 Installing and configuring Extended Insight Client
    4. 3.4 Installing and configuring IBM InfoSphere OptimQuery Workload Tuner
      1. 3.4.1 InfoSphere Optim Query Workload Tuner information road map
      2. 3.4.2 Installing InfoSphere Optim Query Workload Tuner client
      3. 3.4.3 Upgrade InfoSphere Optim Query Workload Tuner and apply fix packs
      4. 3.4.4 Activate InfoSphere Optim Query Workload Tuner license
      5. 3.4.5 User security for InfoSphere Optim Query Workload Tuner
      6. 3.4.6 Configure database for query tuning
      7. 3.4.7 Deploying InfoSphere Optim Query Workload Tuner in a DMZ environment
      8. 3.4.8 Configuration to capture from InfoSphere Optim Performance Manager repository
      9. 3.4.9 InfoSphere Optim Query Workload Tuner client preference settings
  8. Part 2 Using IBM products to manage performance
  9. Chapter 4. Getting to know InfoSphere Optim Performance Manager
    1. 4.1 Getting to know the layout
      1. 4.1.1 Task launcher
      2. 4.1.2 Navigating within a dashboard
      3. 4.1.3 Time controls
    2. 4.2 Health summary dashboard and alerting
      1. 4.2.1 Health Summary
      2. 4.2.2 Alert list and filtering
      3. 4.2.3 Alert configuration
      4. 4.2.4 Alert notification (email and SNMP)
      5. 4.2.5 Alert actions
      6. 4.2.6 User-defined alerts
      7. 4.2.7 Operational alerts for InfoSphere Optim Performance Manager
    3. 4.3 Real-time monitoring and history monitoring
    4. 4.4 Inflight dashboards
      1. 4.4.1 Overview Dashboard
      2. 4.4.2 SQL Statements Dashboard
      3. 4.4.3 Stored Procedure monitoring
      4. 4.4.4 Buffer Pool and I/O Dashboard
      5. 4.4.5 Locking dashboard
      6. 4.4.6 Logging Dashboard
      7. 4.4.7 Memory Dashboard
      8. 4.4.8 System dashboard
      9. 4.4.9 Utilities Dashboard
      10. 4.4.10 Workload Dashboard
      11. 4.4.11 Connection Dashboard
    5. 4.5 Extended Insight Analysis Dashboard
      1. 4.5.1 Extended Insight Analysis Dashboard: Overview panel
      2. 4.5.2 Extended Insight Analysis Dashboard: Details panel
      3. 4.5.3 Workload groups and subgroups
      4. 4.5.4 InfoSphere Optim pureQuery Runtime integration
    6. 4.6 Reporting with InfoSphere Optim Performance Manager
  10. Chapter 5. Getting to know InfoSphere Optim Query Workload Tuner
    1. 5.1 Workflow for query analysis and tuning
    2. 5.2 Entry point of InfoSphere Optim Query Workload Tuner
    3. 5.3 Capture SQL statements from different sources
    4. 5.4 Run the tuning tools and advisors
    5. 5.5 SQL tuning report
    6. 5.6 Query formatter and annotation
    7. 5.7 Analyze the access plan
      1. 5.7.1 Access plan graph
      2. 5.7.2 Access Plan Explorer
    8. 5.8 Statistics recommendations
      1. 5.8.1 Statistics advisor for single query
      2. 5.8.2 Statistics advisor for workload
      3. 5.8.3 Statistical View recommendations for workload
    9. 5.9 Indexes recommendations
      1. 5.9.1 Indexes recommendations for single query
      2. 5.9.2 Indexes recommendations for workload
      3. 5.9.3 Test Candidate Indexes
    10. 5.10 Compare and lock down access plan
      1. 5.10.1 Single query access plan comparison
      2. 5.10.2 Workload access plan comparison
      3. 5.10.3 Edit optimization profile for single query lock down
      4. 5.10.4 Workload plan lock down
  11. Chapter 6. Finding and fixing database level bottlenecks
    1. 6.1 Disk-related bottlenecks
    2. 6.2 Identifying and diagnosing high I/O
      1. 6.2.1 Symptoms of high I/O utilization
      2. 6.2.2 Assessing database health-related problems using alerts
      3. 6.2.3 Diagnosing I/O problems using the Overview Dashboard
      4. 6.2.4 Drilling down to the table space and tables
      5. 6.2.5 Resolving high I/O problem to improve performance
      6. 6.2.6 Avoiding high I/O
    3. 6.3 CPU and memory related bottlenecks
      1. 6.3.1 Diagnosing high CPU time from the Overview Dashboard
      2. 6.3.2 Diagnosing memory bottlenecks
    4. 6.4 Monitoring statement execution
      1. 6.4.1 The SQL Statements dashboard
      2. 6.4.2 Using the SQL Baseline Comparison report
      3. 6.4.3 Resolving high CPU-consuming statements
    5. 6.5 Checking configuration changes
      1. 6.5.1 Launching the InfoSphere Optim Configuration Manager
  12. Part 3 Performance management in a DB2 Distributed Environment
  13. Chapter 7. Performance management: Distributed DB2 environments
    1. 7.1 Performance management in DB2 DPF environments
      1. 7.1.1 Data collection from DPF and viewing in web console
      2. 7.1.2 Detecting activity skews
      3. 7.1.3 Identifying skew at database partition level
      4. 7.1.4 Identifying most active objects
    2. 7.2 Performance management for a DB2 pureScale environment
  14. Part 4 Specific topics for DB2 performance management
  15. Chapter 8. Implementing workload management
    1. 8.1 Implementing a stage 2 configuration
      1. 8.1.1 Applying the best practices template configuration
      2. 8.1.2 Collect baseline monitoring for estimated cost distribution
      3. 8.1.3 Adjusting template definitions to better suit your environment
      4. 8.1.4 Collecting baseline monitoring for resource consumption
      5. 8.1.5 Adjusting concurrency thresholds
      6. 8.1.6 Defining thresholds to protect your system
      7. 8.1.7 Establishing a monitoring regime to ensure ongoing fitness
    2. 8.2 More best practices: Advanced configurations
      1. 8.2.1 Privileged and restricted users or applications
      2. 8.2.2 Divide resources among lines of business
    3. 8.3 Working with Workload Manager configurations
    4. 8.4 Autonomic performance objectives for workloads
  16. Chapter 9. Monitoring packaged database application systems
    1. 9.1 Monitoring SAP environments
      1. 9.1.1 Configure and monitor SAP workloads with Extended Insight
    2. 9.2 Integrate with Tivoli for enterprise level end-to-end monitoring
      1. 9.2.1 InfoSphere Optim Performance Manager and ITCAM integration
      2. 9.2.2 Integrate InfoSphere Optim Performance Manager alerts to Tivoli event manager with SNMP
    3. 9.3 Monitoring Cognos environments
      1. 9.3.1 Setting up for Cognos monitoring
      2. 9.3.2 Setting up the Cognos BI Server for Extended Insight
      3. 9.3.3 Setting up the Data Tools Runtime Client for Cognos
      4. 9.3.4 Viewing the Extended Insight monitoring data for Cognos reports
    4. 9.4 Monitoring WebSphere Application Server applications
      1. 9.4.1 Configure WebSphere Application Server for application monitoring
      2. 9.4.2 Monitor end-to-end performance for a WebSphere application
  17. Appendix A. Performance management differences: DB2 V9.7 or later and DB2 V9.5 databases
    1. A.1 InfoSphere Optim Performance Manager differences for monitoring DB2 V9.7 or later and DB2 V9.5 databases
    2. A.2 Optim Workload Query Tuner differences for DB2 V9.7 or later and DB2 V9.5 databases
  18. Appendix B. Additional material
    1. Locating the web material
    2. Using the web material
  19. Related publications
    1. IBM Redbooks
    2. Other publications
    3. Online resources
    4. Help from IBM
  20. Back cover

Product information

  • Title: Performance Management: Using IBM InfoSphere Optim Performance Manager and Query Workload Tuner
  • Author(s):
  • Release date: October 2013
  • Publisher(s): IBM Redbooks
  • ISBN: 9780738438450