Read Slide 1 text version

Discover, Diagnose, Resolve:

Spotlight® on DB2 Linux, Unix, Windows

Jim Wankowski DB2 Technology Specialist Quest Software


· Defining performance · Monitoring methods · Identifying/resolving performance issues

­ Memory management

· Spotlight on DB2 Overview · Live Demo

What Is Performance?

· How does your company define performance?

­ System availability ­ Transaction throughput ­ Minimum response times (SLAs)

Instance Monitoring

All aspects of the DB2 instance must be monitored.

· Take a look at the big picture

­ Think of DB2 as an ecosystem

· Do not tune for the sake of tuning!

­ Where are your bottlenecks?

Monitoring Methods

Snapshot monitor

· Show status of database for an instant in time

­ ­ ­ ­ ­ ­ Sort Locks Table activity BP activity UOW SQL

· Monitor switches need to be turned on at the instance level to collect data · Low overhead (~5%)

Monitoring Methods (continued)

Event monitor

· Historical collection of data · More overhead (~10-20%) · Main focus on application statistics

­ Tables, Deadlocks, Tablespaces, BP, Connections, Statements, Transactions

Memory Management

· · · · · Catalog cache Package cache Sort heap Lock list Buffer pool

A key performance objective should be to minimize the amount of disk access.

Memory Usage

Catalog cache

· Minimizes I/O against catalog · Contains

­ SYSTABLES information ­ SYSROUTINES information ­ Authorization information

· SYSDBAUTH · Execute Privileges for routines

Possible Performance Implications-Catalog Cache

· Increased bind times · Increased compile times · Increased time to check DB and execution privileges

What to Monitor

Catalog cache hit ratio 80-90%

· Catalog cache lookups

­ Cat_cache_lookups

· Catalog cache inserts

­ Cat_cache_inserts

· Catalog cache overflows

­ Cat_cache_overflows

Package Cache

· Minimizes I/O against catalog

­ Loading packages ­ Having to prepare Dynamic SQL

· Possible performance implications

­ Slower response time with Dynamic SQL

What to Monitor

Package cache hit ratio

· Package cache overflows

­ Pkg_cache_num_overflows

· Package cache lookups

­ Pkg_cache_lookups

· Package cache inserts

­ Pkg_cache_inserts

· Package cache high water mark

­ pkg_cache_size_top


Sort heap

· Number of pages available for private or shared sorts

­ Used by Optimizer for determining access paths

· · · · Sorting Hash joins Index ANDing Dynamic bitmaps

Optimizing Sort

· Avoid sort overflow

­ If sortheap is too small, sort will overflow into temp database tables

· Avoid non-piped sorts

­ If sorted information must be stored in a temporary table vs. memory (sortheap) ­ Determined at time of optimization

Optimizing Sort

· · · · Proper indexing can minimize sorting Avoid ORDER by, Group By, Distinct Avoid sorting VARCHARs Only select required columns

What to Monitor

Sort heap

· Sortheap

Sort heap threshold

· Sheapthres

Proper indexing can minimize sorting.

Lock List

Amount of storage allocated to a database for locking

· Possible performance implications

­ Lock escalations

· Decrease in concurrency · Degradation of performance due to lock waits

­ Deadlocking ­ SQLCODE -912

· Maximum # locks reached in database

· How to avoid

­ Frequent COMMITs ­ LOCK TABLE for heavy updates

What to Monitor

· Lock list

­ Locklist

· Maximum locks

­ Maxlocks

· Lock escalations

­ lock_escals


Virtual storage for temporarily holding data and index pages

· · · · Available only to individual database IBMDEFAULTBP automatically created with database

­ Additional pools created with DDL

Uses memory from database-shared memory (database_memory) DB2 automatically creates small contingent bufferpools

­ 4K,8K,16K,32K ­ Hidden

· Not in catalog or BP system files

What to Monitor

· Overall hit ratio

­ Total # data/IX reads by BP

· Data hit rate · Index hit rate · Asynchronous page cleaners

­ Num_iocleaners

Effective Use of Buffer Pools

"Rules of Thumb" · Separate buffer pools for:

­ ­ ­ ­ ­ ­ System catalog Sequentially scanned tables Temporary table spaces Small frequently updated tables Small read-only tables Large tables w/random access

Spotlight® on DB2

· Improve overall response time

­ Quickly identify bottlenecks ­ Drilldowns into detailed metrics

· Database, user, SQL, buffer pools and O/S

· Reduce risk of database outage

­ Alarm on threshold conditions

· Historical review

­ Record/playback

Spotlight ® on DB2 LUW

Summary page shows totals for all database activity within an instance, including EEE/ESE clustered environments.

Database homepage provides an animated pictorial overview of activity for a specific database.

Detail drilldowns for: ·Database Mgr ·Database Summery ·Thread Details ·Bufferpools ·SQL Detail ·FCM ·O/S Detail

Customizable metrics for quick identification of potential performance issues.

Database Manager Summary

The Database Manager drilldown displays global summary information about all active databases and nodes in the DB2 UDB EE, EEE and ESE instances you are diagnosing.

Database Summary

The Databases drilldown displays a list of all active databases in the instance. The list provides high-level information about each database. This information, which you can Troubleshooting high I/O activity on possible problems quickly scan to locate the database

High rates depicted on the graphs for the following activities might be indications of performance degradation since these activities involve disk I/O and slow-downs in query processing: ·Physical read rates (compared to logical read rates) ·Synchronous read or write rates ·Direct I/O rates for a database

Thread Activity

The Client Applications drilldown displays a list of all applications currently connected to a database. When launched from the instance home page, the tab lists applications connected to all active databases and nodes in the instance. When launched from the home page of a specific database or node, the tab lists applications connected to that database or node only.

Tablespace Detail

The Tablespaces drilldown provides details and Statistics about the DMS and SMS tablespaces for the database you are currently diagnosing. This information includes the following:

· Tablespace identification, configuration, and status details

· Counts and percentages that describe the I/O and buffer pool activity associated with the tablespace during the last diagnostics interval.


Quickly identify the most resource-intensive SQL across entire instance or within a specific database.

EXPLAIN statement

Bufferpool Activity

Summary and detail information on all buffer pools within an instance or for a specific database.

FCM Analysis

The FCM drilldown provides a breakdown of message buffer traffic for the instance you are diagnosing. The fast communication manager (FCM) provides communication support between nodes for agent requests, and message buffers in clustered DB2 environments.

O/S Details

Operating System Details provides detailed information on all activity running on server.

Alarm Log

The Alarm Log keeps a record of all alarms that have been triggered during the monitoring session.

Alarm Log

Clicking on the "?" will provide help on the topic.


Record feature allows the capture of all activity for future review

Performance Scenario

· User calls in and complains that his transaction against the production database is hanging.

­ Response: Open Spotlight® to determine status of application.

Homepage is indicating a large number of locks pending on the user's database: Action Click on the "Pending Locks" icon

Select "Locking" tab to identify who is holding lock.

The user's transaction is waiting on a lock.

Agent ID 538 is holding the lock on the table that DB2USER1 needs.

User 538 issued a "Lock Table" on Employee table Action Right click and select "Force Application"

Contention issue resolved; all systems back to normal!

For More Information

Download Spotlight® on DB2 ·www.Quest.Com/DB2


Slide 1

41 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate


Notice: fwrite(): send of 202 bytes failed with errno=32 Broken pipe in /home/ on line 531