How busy was the database today? - Proper monitoring of database activity

Trying to estimate the database load on a given day can be quite a challenge. A simple question may not yield an insightful answer if it is out of context, littered with arbitrary numbers, and/or missing key details. So what is the right way to solve this problem? How easy can it be to monitor database activity?

Solution

The solution is more visual than numerical - a single image is enough to capture the essence of the daily activity of the database. Here's how you can visualize a single database day using a process simplified by DBPLUS PEROFORMANCE MONITOR:

1. Navigate to the selected database in the program.
2. Go to Database Analysis > Sessions > Session/ Sort/ Undo history.
3. Switch the view from grid to graph.
4. Select the appropriate date and click “Refresh”.

This image presents a comprehensive picture of the activity of a database instance during the selected day - a quick overview at a glance. Of course, it takes a little more effort to fully understand this data.

Delving into the data

By selecting a specific point in the image, say point (1), you will get eight metric values. A further check can be done by accessing the “Sessions” tab, where information about the active sessions at the time of the snapshot is available. Sorting these sessions by “Blocking sessions” can distinguish each session stopped during this period.

To make the monitoring of database activity even more in-depth, you can refer to the following image:

SQL Text Locking Sessions

Pointing to (1) takes us back to the snapshot without displaying metric values until you hover over it. The “Hash Value” field, when hovering over it, displays the SQL text, which in this case consists of obtaining new values for the sequence. Clicking on the “+” symbol under point (3) displays the SQL text in the “SQL Details” tab for a more detailed check.

Peak period analysis - sorting sessions

This image focuses on activities during peak sorting periods. It marks the analyzed timeframe and highlights active tabs such as “Sort”. It suggests sorting by the “Space Usage” column to identify relevant operations, as shown in point (3). Hovering over “Hash Value” reveals the SQL text associated with the sort operation.

Analysis of Sessions Undo

lFinally, let's take a look at peak usage times for Sessions Undo. The selected snapshot shows when the number of “Undo” was highest, leading us to sort by “Used space” to find meaningful operations, such as the delete command, which usually consumes a significant UNDO space.

Wider application of this analysis

This method is not just for analyzing typical days; it can be effectively used to analyze more problematic days by applying appropriate filters. This allows you to focus on specific programs, modules and even machines, making it a versatile tool in database management.

This approach to database analysis with DBPLUS PERFORMANCE MONITOR simplifies understanding how busy the database is on a given day. It replaces guesswork with visual insight, making it easier for database administrators to make truly informed decisions and deal with optimizations. This means having data from a longer period, which allows analysis over weeks or months.