book

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

by Chuck Ballard, Ute Baumbach, Holly Hayes, Marcia Miskimen, Lakshmi Palaniappan, Marichu Scanlon, Yong Hua Zeng

October 2013

Intermediate to advanced

414 pages

8h 57m

English

IBM Redbooks

Read now

Unlock full access

Trademarks
AuthorsNow you can become a published author, too!Comments welcomeStay connected to IBM Redbooks
1.1 Overall IBM InfoSphere approach1.1.1 Products in the InfoSphere Optim performance management solution1.2 What is new in the products1.2.1 InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows V5.11.2.2 InfoSphere Optim Performance Manager for DB2 for Linux, UNIX, and Windows V5.21.2.3 InfoSphere Optim Query Workload Tuner for DB2 Linux, UNIX, and Windows1.2.4 IBM InfoSphere Optim Configuration Manager for DB2 for Linux, UNIX, and Windows1.2.5 IBM InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows V3.21.2.6 IBM Data Studio V3.21.3 InfoSphere Optim Performance Manager introduction1.3.1 Features1.3.2 Packaging1.3.3 InfoSphere Optim Performance Manager architecture1.4 InfoSphere Optim Query Workload Tuner1.4.1 Packaging1.5 Looking ahead
2.1 Installation roadmaps2.1.1 New or direct installation2.1.2 Update installation2.1.3 Migrating from Performance Expert2.2 Prerequisites2.2.1 InfoSphere Optim Performance Manager2.2.2 Optim Performance Manager Extended Insight2.2.3 Integration with InfoSphere Optim Query Workload Tuner2.2.4 Integration with InfoSphere Optim Configuration Manager2.3 Installation parameters2.4 Capacity planning2.4.1 Parameters that affect InfoSphere Optim Performance Manager resources2.4.2 Estimating resource requirements2.4.3 Concierge Program for InfoSphere Optim Performance Manager2.4.4 Enabling compression2.4.5 Network bandwidth2.5 Storage options2.5.1 Table space type selection2.5.2 Table space naming and usage2.5.3 Table space location2.5.4 Table space DDL2.6 User security2.6.1 Web console users2.6.2 Monitored database users2.6.3 Objects in the monitored database2.6.4 Special considerations for direct repository access
3.1 Installing and running InfoSphere Optim Performance Manager3.1.1 Installing InfoSphere Optim Performance Manager3.1.2 Activating the InfoSphere Optim Performance Manager license3.1.3 Updating InfoSphere Optim Performance Manager3.1.4 Starting and stopping InfoSphere Optim Performance Manager3.2 Configuring InfoSphere Optim Performance Manager3.2.1 Configuring console security for user access3.2.2 Adding and configuring database monitoring3.2.3 Monitoring templates and proven practices3.2.4 Consider impact on the monitored database and applications3.2.5 Other InfoSphere Optim Performance Manager configurations3.3 Installing and configuring Extended Insight Client3.4 Installing and configuring IBM InfoSphere OptimQuery Workload Tuner3.4.1 InfoSphere Optim Query Workload Tuner information road map3.4.2 Installing InfoSphere Optim Query Workload Tuner client3.4.3 Upgrade InfoSphere Optim Query Workload Tuner and apply fix packs3.4.4 Activate InfoSphere Optim Query Workload Tuner license3.4.5 User security for InfoSphere Optim Query Workload Tuner3.4.6 Configure database for query tuning3.4.7 Deploying InfoSphere Optim Query Workload Tuner in a DMZ environment3.4.8 Configuration to capture from InfoSphere Optim Performance Manager repository3.4.9 InfoSphere Optim Query Workload Tuner client preference settings
4.1 Getting to know the layout4.1.1 Task launcher4.1.2 Navigating within a dashboard4.1.3 Time controls4.2 Health summary dashboard and alerting4.2.1 Health Summary4.2.2 Alert list and filtering4.2.3 Alert configuration4.2.4 Alert notification (email and SNMP)4.2.5 Alert actions4.2.6 User-defined alerts4.2.7 Operational alerts for InfoSphere Optim Performance Manager4.3 Real-time monitoring and history monitoring4.4 Inflight dashboards4.4.1 Overview Dashboard4.4.2 SQL Statements Dashboard4.4.3 Stored Procedure monitoring4.4.4 Buffer Pool and I/O Dashboard4.4.5 Locking dashboard4.4.6 Logging Dashboard4.4.7 Memory Dashboard4.4.8 System dashboard4.4.9 Utilities Dashboard4.4.10 Workload Dashboard4.4.11 Connection Dashboard4.5 Extended Insight Analysis Dashboard4.5.1 Extended Insight Analysis Dashboard: Overview panel4.5.2 Extended Insight Analysis Dashboard: Details panel4.5.3 Workload groups and subgroups4.5.4 InfoSphere Optim pureQuery Runtime integration4.6 Reporting with InfoSphere Optim Performance Manager
5.1 Workflow for query analysis and tuning5.2 Entry point of InfoSphere Optim Query Workload Tuner5.3 Capture SQL statements from different sources5.4 Run the tuning tools and advisors5.5 SQL tuning report5.6 Query formatter and annotation5.7 Analyze the access plan5.7.1 Access plan graph5.7.2 Access Plan Explorer5.8 Statistics recommendations5.8.1 Statistics advisor for single query5.8.2 Statistics advisor for workload5.8.3 Statistical View recommendations for workload5.9 Indexes recommendations5.9.1 Indexes recommendations for single query5.9.2 Indexes recommendations for workload5.9.3 Test Candidate Indexes5.10 Compare and lock down access plan5.10.1 Single query access plan comparison5.10.2 Workload access plan comparison5.10.3 Edit optimization profile for single query lock down5.10.4 Workload plan lock down

6.1 Disk-related bottlenecks6.2 Identifying and diagnosing high I/O6.2.1 Symptoms of high I/O utilization6.2.2 Assessing database health-related problems using alerts6.2.3 Diagnosing I/O problems using the Overview Dashboard6.2.4 Drilling down to the table space and tables6.2.5 Resolving high I/O problem to improve performance6.2.6 Avoiding high I/O6.3 CPU and memory related bottlenecks6.3.1 Diagnosing high CPU time from the Overview Dashboard6.3.2 Diagnosing memory bottlenecks6.4 Monitoring statement execution6.4.1 The SQL Statements dashboard6.4.2 Using the SQL Baseline Comparison report6.4.3 Resolving high CPU-consuming statements6.5 Checking configuration changes6.5.1 Launching the InfoSphere Optim Configuration Manager
7.1 Performance management in DB2 DPF environments7.1.1 Data collection from DPF and viewing in web console7.1.2 Detecting activity skews7.1.3 Identifying skew at database partition level7.1.4 Identifying most active objects7.2 Performance management for a DB2 pureScale environment
8.1 Implementing a stage 2 configuration8.1.1 Applying the best practices template configuration8.1.2 Collect baseline monitoring for estimated cost distribution8.1.3 Adjusting template definitions to better suit your environment8.1.4 Collecting baseline monitoring for resource consumption8.1.5 Adjusting concurrency thresholds8.1.6 Defining thresholds to protect your system8.1.7 Establishing a monitoring regime to ensure ongoing fitness8.2 More best practices: Advanced configurations8.2.1 Privileged and restricted users or applications8.2.2 Divide resources among lines of business8.3 Working with Workload Manager configurations8.4 Autonomic performance objectives for workloads
9.1 Monitoring SAP environments9.1.1 Configure and monitor SAP workloads with Extended Insight9.2 Integrate with Tivoli for enterprise level end-to-end monitoring9.2.1 InfoSphere Optim Performance Manager and ITCAM integration9.2.2 Integrate InfoSphere Optim Performance Manager alerts to Tivoli event manager with SNMP9.3 Monitoring Cognos environments9.3.1 Setting up for Cognos monitoring9.3.2 Setting up the Cognos BI Server for Extended Insight9.3.3 Setting up the Data Tools Runtime Client for Cognos9.3.4 Viewing the Extended Insight monitoring data for Cognos reports9.4 Monitoring WebSphere Application Server applications9.4.1 Configure WebSphere Application Server for application monitoring9.4.2 Monitor end-to-end performance for a WebSphere application
A.1 InfoSphere Optim Performance Manager differences for monitoring DB2 V9.7 or later and DB2 V9.5 databasesA.2 Optim Workload Query Tuner differences for DB2 V9.7 or later and DB2 V9.5 databases
Locating the web materialUsing the web material
IBM RedbooksOther publicationsOnline resourcesHelp from IBM

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

Getting to know InfoSphere Optim Query Workload Tuner

This chapter introduces the basic features in InfoSphere Optim Query Workload Tuner, and how to use those features to assist the SQL analysis and performance tuning. It describes the workflow and best practices for how to analyze and tune SQL statements using InfoSphere Optim Query Workload Tuner. The chapter shares the overall steps to use this product and to speed the query analysis and tuning process. The chapter also examines how to find the most interesting SQL statements to tune and how ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance (70-444): Study Guide

Publisher Resources

ISBN: 0738438456Purchase book

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

by Chuck Ballard, Ute Baumbach, Holly Hayes, Marcia Miskimen, Lakshmi Palaniappan, Marichu Scanlon, Yong Hua Zeng

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

You might also like

MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance (70-444): Study Guide

Linux® Quick Fix Notebook

Getting Started with IBM InfoSphere Optim Workload Replay for DB2

Expert Hadoop® Administration

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,and much more.

You might also like

MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance (70-444): Study Guide

Linux® Quick Fix Notebook

Getting Started with IBM InfoSphere Optim Workload Replay for DB2

Expert Hadoop® Administration

Publisher Resources

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.