Database Reference
In-Depth Information
aVeraGe NUMBer OF aCtIVe SeSSIONS
The average number of active sessions (aaS) is the rate at which the Db time increases at the system level.
For example, if the Db time of a database instance increases by 1,860 seconds in 60 seconds, the average
number of active sessions is 31 (1,860/60). This means that, during the 60-second period, on average 31
sessions were actively processing user calls.
The average number of active sessions at the system level is an important metric because it shows you how
much a system is loaded. as a rule of thumb, values much lower than the number of CPu cores mean that the
system is almost idle. Inversely, values much higher than the number of CPu cores mean that the system is quite
busy. That said, because it's an average, it can hide a lot of information. So, be careful when basing a judgment
solely on this statistic. This is especially true when the period of time covers more than a few minutes.
The rate at which the Db time increases can also be computed for a single session. In that case, it's a value
between 0 and 1 that indicates the degree to which the session is active. If the value is 0, it means that the
session is completely idle. In other words, the database engine is doing no processing. If the value is 1, it means
that the session is completely busy processing user calls.
Wait Classes and Wait Events
Based on time model statistics, you can determine not only how much processing time a database instance (or a
session or a container) is consuming, but also how much CPU it's using for that processing. When the two values are
equivalent, it means that the database instance isn't experiencing any wait because of things like disk I/O operations,
network round-trips, or locks. However, when the difference between the two values is significant, for analyzing a
performance issue, you need to know which waits the server processes are waiting for. This information is provided
through wait events.
Because the number of wait events is very high (there are more than 1,500 of them in version 12.1), to simplify the
production of resource usage profiles, wait events are organized into 13 wait classes (note that there are 12 in version 10.2).
Which wait events are available in the version you're using and the wait class they belong to is visible through the
v$event_name
view. For example, the following queries show which wait classes exist in version 12.1.0.1, how many
wait events they contain, and the wait events belonging to the wait class with fewer wait events (
Commit
):
SQL> SELECT wait_class, count(*)
2 FROM v$event_name
3 GROUP BY rollup(wait_class)
4 ORDER BY wait_class;
WAIT_CLASS COUNT(*)
-------------- --------
Administrative 57
Application 17
Cluster 57
Commit 4
Concurrency 34
Configuration 26
Idle 119
Network 28
Other 1123
