This section includes the following topics:
■ About the Statistics tab
■ How the Statistics tab is structured
■ About the entities you can examine
■ How the Statistics tab can help you identify performance problems
About the Statistics tab
The Statistics tab displays database statistical information on your DB2 databases. All information is derived from Precise for DB2's own statistics and loaded into the PMDB to provide you with a long-term historical view. Analyzing database and I/O statistics over a long period of time is the only way to predict future resource consumption of the DB2 database and identify I/O patterns. It also provides a performance-tracking tool that gives you the ability to assess database statistics trends and analyze changes in database activity.
The Statistics tab is primarily intended for database tuning. You can also use the tab to periodically monitor the health of your database. It lets you provide answers to the following types of questions: Is DB2 using resources efficiently? Are the memory structures correctly sized? Are we performing too many logical I/Os?
Note: This section describes how to use the Statistics tab and which DB2 statistics are available in Precise for DB2. It is not within the scope of this chapter to define the meaning of each DB2 statistic or how to tune them. For additional information on DB2 statistics, see the relevant DB2 documentation.
How the Statistics tab is structured
The Statistics tab is divided into two areas-the Main area and the Details area. The Main area lists all the databases or database partitions that are monitored by Precise for DB2. The Details area provides summarized or overtime information regarding the selected database partition.
When you open the Statistics tab from another tab, you will be focused on an individual database partition. The tab is always launched at the database partition level, even if the currently selected entity is not a database partition.
The Main area
The database table displayed in the Main area lists all the databases or partitions that are monitored by Precise for
DB2. Each row corresponds to a database or database partition.
The following table describes the information displayed in the Main area table.
Table 9-1 Main area table
Column Description
Data Partition <instance>.<database>[.number]
Reads & Writes Total reads and total writes within the selected time frame
Logical Reads Total logical reads within the selected time frame
Timeouts & Deadlocks Number of timeouts and number of deadlocks occurred within the selected time frame
Hit Ratio The buffer cache hit ratio
SQL Types Total number of Selects, Inserts, Updates and Deletes occurred within the selected time frame
Lock Escalations Number of lock escalations and exclusive lock escalation occurred within the selected time frame
About the Details area
The Details area displays statistics information on the database or database partition selected in the Main area. It has seven tabs: An Overview tab, a Prefetchers tab, a Cleaners tab, a Logging tab, a Package Cache tab, a Sorts tab, and a Locks tab.
About the entities you can examine
The Statistics tab displays information on different entities in the Details area. This section provides an overview of all entities (tabs), their meaning, and their views.
The following entities can be examined in the Statistics tab:
■ Overview
■ Prefetchers
■ Cleaners
■ Logging
■ Package Cache
■ Sorts
■ Locks
Overview
The Overview tab displays information in five views (only first three views are opened by default):
Table 9-2 Overview tab
Item Description
Overview Displays database summed statistics like timeouts and deadlocks.
Hit Ratios Displays an overtime graph of the hit ratios of the database, package cache, and catalog cache.
Timeouts & deadlock Displays overtime graph of timeouts and deadlocks.
Table 9-2 Overview tab
Item Description
I/O Activity Displays overtime stacked-bar graph of the I/O activity of the database. The I/O activity is displayed as an over time stacked bar of synchronic reads and writes, a-synchronic reads and writes, and direct reads and writes.
Connections & Agents Displays an overtime graph of the connections and agents activity. It shows the high water mark of connection, application executing and application connected (idle) as well as associated agents, high water mark of coordinator agents, and high water mark of associated agents.
Prefetchers
The Prefetchers tab displays information in four views (only the first three views are opened by default):
Table 9-3 Prefetchers tab
Item Description
Bufferpools Hit Ratio This graph displays the bufferpool’s hit ratio over time. It displays separate graphs for database hit ratio, data pages hit ratio, and index pages hit ratio.
Physical Reads This graph displays a stacked bar of the physical reads over time. The stacked bar is divided into synchronic, a-synchronic (for data and for index), and direct reads.
Read Response Time This graph displays overtime line graphs of asynchronous read response time, synchronous read response time and direct read response time. Also displayed is a line graph for the average prefetch wait time.
Read Time This graph displays a stacked bar of read time over time. The stacked bar is divided into synchronic, a-synchronic, and direct read time.
Cleaners
The Cleaners tab displays information in four views (only the first three views are opened by default):
Table 9-4 Cleaners tab
Item Description
Physical Writes This graph displays a stacked bar of the physical writes over time. The stacked bar is divided into synchronic, a-synchronic (for data and for index), and direct writes.
Cleaners Efficiency This graph displays overtime line graphs for Victim Page Cleaners Execution [calculated as (bp victim page cleaners triggered) / (bp victim page cleaners triggered+ bp thresh cleaners triggered+ bp log space cleaners triggered)] and synchronous writes to asynchronous writes ratio.
Write Response Time This graph displays overtime line graphs of asynchronous write response time, synchronous write response time, and direct write response time.
Write Time This graph displays a stacked bar of write time over time. The stacked bar is divided into synchronic, a-synchronic, and direct write time.
Logging
The Logging tab displays information in five views (only the first three views are opened by default):
Table 9-5 Logging tab
Item Description
Log Utilization This graph displays an overtime stacked bar of Primary log used, secondary log used, and log space available.
Log Pages Time (ns) This graph displays a stacked bar of log read time and log write time (in nanoseconds)
over time.
Commits & Rollbacks This graph displays a stacked bar of commits and rollbacks over time.
Log Buffer Reads - Hits & Misses This graph displays the log buffer reads ratio between hit and misses.
Log Pages Count This graph displays a stacked bar of log pages reads and log pages writes over time.
Package Cache
The Package Cache tab displays information in four views (only the first three views are opened by default):
Table 9-6 Package Cache tab
Item Description
Package Cache Hit Ratio This graph displays the package cache hit ratio, the package cache overflows and the application section hit ratio over time, in a graph with two Y-axis (the left Y-axis is for hit ratio, and the right Y-axis is for overflows)
SQL Types This graph displays a stacked bar of Selects, DMLs and DDLs over time. This graph can be used for findings a correlation between package cache low hit ratio to the high number of DDL.
Compile & Recompile This graph displays a stacked bar of compile time and recompile time over time, as taken from Precise for DB2 collector. This indicate the amount of parsing time the application is spending while running.
Dynamic vs. Static This graph displays a stacked bar of dynamic SQL attempts and Static SQL attempts over time.
Sorts
The Sorts tab displays information in three views:
Table 9-7 Sorts tab
Item Description
Sort Heap Overflows (To disk) Ratio This graph displays the sort ratio { (Sort overflow) / (total sorts)} and hash joins ratio { (hash join overflows) / (total hash joins)} over time. This graph can indicate whether operations are performed on disk (sorts, hash joins), and whether it is needed to adjust the sort heap size.
Overflow Count This graph displays a stacked bar of sort overflows, hash joins overflows, and small hash joins overflows over time.
Sort Time This graph displays the sort time over time. This graph shows data only if the DB2 sort switch is on.
Locks
The Sorts tab displays information in three views:
Table 9-8 Locks tab
Item Description
Timeouts & Deadlock This graph displays a stacked bar of timeouts and deadlocks over time.
Lock Escalations This graph displays a stacked bar of shared lock escalations and exclusive lock escalations over time.
Lock Time (Avg) This graph displays the average lock time {(lock wait time)/(lock waits)} over time. This graph will have values only in case DB2 Lock Switch is on.
Lock List in Use This graph displays lock list in use over time.
How the Statistics tab can help you identify performance problems
To determine whether DB2 is performing optimally, it is necessary to monitor the health of your database and database partitions, and at times to tune them. The Statistics tab provides many performance counters grouped into several predefined graphs that display related counters that enable you to locate performance problems in your system.
You can identify a performance problem by doing one or more of the following:
■ Examining Prefetchers
■ Examining Sorts
■ Examining Locks
■ Examining Logging
Examining Prefetchers
You can examine if your prefetchers are working properly by viewing the Prefetchers tab.
1 In the Time Frame list, choose the period of time you want to analyze.
2 In the Database Partition list, choose the item you want to analyze.
3 Check the relation between the different reads (synchronic, asynchronic and direct) by viewing the Physical Reads view. Check the average response time of the different reads by viewing the Response Time (Avg) view. This may indicate whether your I/O related parameters are configured well.
Examining Sorts
You can examine if your application is sorting optimally, and if your sort-related parameters are configured adequately by viewing the Sorts tab.
1 In the Time Frame list, choose the period of time you want to analyze.
2 In the Database Partition list, choose the item you want to analyze.
3 Check whether sorts overflows and hash joins overflows occur in the Overflows Count view. Check the ratio between total sorts and sort overflows and the ratio between hash joins and hash joins overflows in Sort Heap OVerflows (To Disk) Ratio graph. If the ratio is high then your sort-related parameters are probably not configured adequately.
Examining Locks
You can examine if your application is suffering from locks and if your lock-related parameters are configured adequately by viewing the Locks tab.
1 In the Time Frame list, choose the period of time you want to analyze.
2 In the Database Partition list, choose the item you want to analyze.
3 Check whether lock escalation occurs in your database by viewing the Lock Escalations view.
4 Check whether deadlocks and timeouts occur by viewing the Timeouts and Deadlocks view. This may indicate that one or more applications are holding locks for long durations or that lock-related parameters are not configured adequately.
Examining Logging
You can examine if your logging is performing well by viewing the Logging tab.
1 In the Time Frame list, choose the period of time you want to analyze.
2 In the Database Partition list, choose the item you want to analyze.
3 Check whether you allocated enough log space in the Log Space view. Identify whether secondary log space is used. If so, you probably need to allocate more primary log space.
4 Check whether your log read and write time are adequate by looking in the Log Time view. This may indicate whether the current disk is adequate for logging.