What are Average Active Sessions in a database agent workload? (4228410)

Foglight database agents use a metric called Average Active Sessions (AAS) to show how busy a database is. It helps you understand how much work the database is doing at any moment.

AAS measures how many sessions are actively working or waiting for something (like CPU or disk) at a specific time. It does not count sessions that are idle or doing nothing.

The graph shows this using a unit called seconds per second (seconds/s). This means how many seconds of work the database is doing in one second of real time. For example, if multiple sessions are working at the same time, the database might be doing 5 seconds of work in just 1 second, thanks to parallel processing.

The unit seconds per second (seconds/s) may sound unusual, but it’s accurate for what’s being measured. It shows how much time is being used by active sessions in each second of real time. While threads are involved in doing the work, the metric is not counting threads, it’s measuring how much time those threads are consuming. Threads are execution units, but this graph is focused on total workload time, not the number of threads running.

This type of measurement is also used in Oracle and other database systems.

Summary

• An Active Session is a session that is currently running a query or waiting for a resource like CPU, disk, or memory.
• Average Active Sessions (AAS) is calculated by dividing the total time spent in the database (CPU, I/O, waits) by the actual clock time. This shows how busy the database is, not how many sessions exist.
• The unit seconds/s means how many seconds of work the database is doing in one second of real time. This can be more than 1 second if multiple sessions are working at the same time.
• The metric is not counting threads. It’s measuring how much time is being used by all active sessions. Threads help perform the work, but the graph shows total workload time, not how many threads are running.

When AAS is high:

Compare the AAS value to the number of CPU cores on the server.

• If AAS is higher than the CPU count, the system may be overloaded.
• This can lead to slow performance or delays in query execution.

To troubleshoot:

1. Identify the type of wait:

   • Is the system waiting on I/O, CPU, locks, or something else?
   • Foglight provides breakdowns of wait types to help pinpoint the issue.

2. Review top SQL statements:

   • Look at which queries are using the most resources.
   • Check for inefficient queries, missing indexes, or long-running operations.

3. Check system capacity:

   • Is the server under-provisioned for the workload?
   • Consider scaling resources or optimizing queries.

4. Look at trends over time:

   • Is this a one-time spike or a regular pattern?
   • Use historical data to understand usage patterns and plan improvements.