I am trying make sense of the DB load from the AWR report for a period handed over to me by DBA. Researching a bit, I see the "Average active sessions" is a metric widely used to measure DB load. It is calculated using (Total DB time/Total wall clock elapsed time). From the AWR, in my case it came a little over 7. Does this number indicate high DB activity? Or do I need to take into account anything else?
Oracle Performance – Average Active Sessions High Threshold
awroracleperformanceperformance-tuning
Related Solutions
I'm not sure that there is a problem.
A session in V$SESSION has a status of "INACTIVE" if it is not executing a SQL statement at that particular instant in time. If a user opens a dedicated session in a client-server application and is using the application intensively, it would not be at all unusual for the session to be "INACTIVE" 99% of the time because the vast majority of the time is spent waiting for the human to read and process the data on the screen. The fact that a session is "INACTIVE" doesn't imply that it wasn't "ACTIVE" a few seconds ago or that it won't be "ACTIVE" again a few seconds from now. The LAST_CALL_ET column in V$SESSION shows you the number of seconds since a session was last in an "ACTIVE" status if the session is currently in an "INACTIVE" status. That may give you some idea whether you are dealing with sessions that have been active for a while or not.
If you have a three-tier application, your middle tier servers will generally maintain a pool of database connections. The middle tier opens a number of connections and keeps them open more or less indefinitely. When users need to interact with the database, they get a connection from the pool, run a query, and return the connection to the pool. At peak workloads, connection pooling can concentrate a large number of application users into a relatively smaller number of database connections which will mean that the sessions spend a larger fraction of their time in an "ACTIVE" status. But during periods of low usage, the middle tier probably maintains a bunch of "INACTIVE" sessions.
In either of these cases, having a number of "INACTIVE" sessions is not a bad thing. And "cleaning up" those inactive sessions will cause errors in the applications that have opened them.
If you have a large number of inactive sessions because the client processes have failed (i.e. the client application crashed and the database is unaware of that fact), you could use the sqlnet.expire_time parameter on the server's sqlnet.ora file to periodically send a probe packet to the client machine. That may negatively impact performance but it will allow the database to more quickly discover that the client process has failed.
If you have a large number of inactive sessions because your middle tier servers have created too many connections to the database in their connection pools, you would need to talk with the middle tier admins to ask them to reduce the number of connections they open in their connection pools. If they have recently added more servers to the middle tier farm, they may need to reduce the number of connections each server maintains in its connection pool.
If your problem is that too much RAM is being allocated to all the dedicated server connections that need to be open (particularly common if you've got a lot of client server applications), you could configure Oracle to support shared server connections since that reduces the amount of memory that each connection requires.
Presumably they are active and doing things so looks like you might need to get more info about your sessions before killing them off. I usually use a query like this to get important info about active sessions including how long they have been logged on (oldest at top), and also how long they have been running the current SQL.
SELECT USERNAME,
TERMINAL,
PROGRAM,
SQL_ID,
LOGON_TIME,
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From v$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL
ORDER BY MINUTES_LOGGED_ON DESC;
Best Answer
To know whether 7 is high or low, you'd need to know something about the database server.
If, for example, you have a single CPU with 4 cores, 7 sessions would mean that, at any given time, 3 of your 7 threads were probably waiting to get scheduled for the CPU since your machine is only capable of running 4 sessions at any instant in time. That would imply that your server was under a pretty decent load. If, on the other hand, you have 4 CPUs each with 8 cores, then only 7 of your 32 cores were, on average, busy at any instant in time leaving 25 of your 32 cores free. That would imply that your server itself wasn't particularly heavily taxed.
You'd also want to know a bit about the application. A 15 minute spike where the average number of active sessions went up to 7 that was preceded and followed by a lower number would generally be less concerning than a report of a 24 hour period where the average number of active sessions was 7 because it's likely that there is a lot more variation over the 24 hour period (i.e. sometimes the number of active sessions was much higher than 7). Application history also comes in to play. If the application has always seen the average number of active sessions top out at 3 or 4 under peak load and it suddenly starts spiking to 7, that's more of a concern than if the application has always had those performance characteristics.