Snapshots: Database

You can access the database snapshot by calling the DBA Cockpit and choosing

Performance Snapshots Database on the Database tab page of the DBA Cockpit. Depending on the setup of your DBA Cockpit, one of the following snapshots is displayed on the Database screen:

Enhanced database snapshot with support of the data collection framework (DCF) Database snapshot without DCF support

For more information about the DCF, see Enabling the Database for the Data Collection

Framework [page 32].

Enhanced Database Snapshot with DCF Support

To analyze system performance, it is not sufficient to review the key figures provided by the database from startup until the current time. This data has been cumulated over a long time period and, therefore, cannot reflect a short-term degradation of performance or an

increasing or changing workload.

In addition, the interpretation of snapshot data mostly depends on configuration parameters that do not relate to a certain time frame either but describe only the current configuration of the database. Especially, if your database is using self-tuning memory management (STMM), you cannot correlate bad performance data with current storage parameters.

In contrast to the Database snapshot option without DCF support, the database snapshot with DCF support not only lets you view current global database snapshot data but also monitor data that is periodically collected by the DCF.

Note

To monitor data on the Database screen, you have to make sure that the DCF is set up correctly. If the DCF is not available or wrongly set up, an appropriate warning is displayed including a link to the Collector Configuration screen where you can perform the required steps.

For more information, see Data Collection Framework: Collector Configuration [page 227]. End of the note.

Features

The Database screen provides the following:

A selection area where you specify filter criteria for the monitoring data (only available in a distributed database)

An overview table displaying the retrieved monitoring data with the most important key figures that have been aggregated over the selected time period.

A detail view of selected monitoring data of the result list

An option to break down all history data that was collected for the selected monitoring data during the specified time frame.

Process

To analyze database snapshot data, you proceed as follows:

1. You identify the time period where you experienced performance problems, such as, long-running background jobs, threshold violations detected by workload

management, and bad user response time.

2. In the Selection area, you specify the appropriate time frame.

Note

If you are using a distributed database system and you want to retrieve a general snapshot, you set the member selection to All and you do not drill down by members. To retrieve a more detailed snapshot in a distributed database system, you can further drill down the selection by members. If a single member or a subset of members shows deviations compared to other members, you can also restrict the member selection.

End of the note.

3. To refresh the monitoring data, you choose the Apply Selection pushbutton. The following information is displayed for each partition:

o Number and total size of buffer pools o Number of data and index logical reads o Number of data and index physical reads

o Average physical read and write time that is required to read from and write data into the buffer pool

o Executed SQL statements o Package cache size o Package cache quality

4. To display detailed information, you select a line in the overview table.

For more information, see Displaying Detailed Database Snapshot Data [page 82]. 5. To display detailed history data, you select a line in the overview table and choose

the History pushbutton.

The data is displayed in the History Details content detail area.

6. To isolate the time frame during which the performance problem occurred, you use the data in the overview table in the History Details content area.

Database Snapshot Without DCF Support

This Database screen provides a selection of current performance data that is related to each partition. You can use the information to identify performance-critical partitions before starting a more detailed analysis of your database.

For each partition of your database system, information about the following performance indicators is displayed:

Number and total size of buffer pools Number of data and index logical reads Number of data and index physical reads

Average physical read and write time that is required to read from and write data into the buffer pool

Executed SQL statements Package cache size Package cache quality Note

If you choose a line in the overview table of the Database screen, detailed database

snapshot data is retrieved and displayed in the content detail area below the overview table. End of the note.

4.5.1.1 Displaying Detailed Database Snapshot Data

1. On the Database screen, choose a line in the overview table of the Database screen. Depending on the setup, data has been collected as follows:

o With DCF support, the displayed monitoring values refer to the time frame that was specified in the Selection area. The configuration values show the configuration that was valid during the specified time frame. All values are retrieved from the history that is stored by the DCF.

o Without DCF support, data has been collected since the database was started. The configuration values show the current configuration.

2. In the content detail area of the Database screen, choose the appropriate tab page to display an overview of the following critical database performance indicators:

o Buffer Pool [page 295] o Cache [page 297]

o Asynchronous I/O [page 298] o Direct I/O [page 299]

o Real-Time Statistics [page 300] o Locks and Deadlocks [page 300]

o Logging [page 302] o Calls [page 304] o Sorts [page 306]

o XML Storage [page 307]

The data displayed on these tab pages is described in more detail in the following sections.