Oracle 10g - Determine the average of concurrent connections

Question

Hello all,

Is it possible to determine the average of concurrent connections on a 10g large database installation?

Any ideas??

Answer 1

This is probably more of a ServerFault question.

On a basic level, you could do this by regularly querying v$session to count the number of current sessions, store that number somewhere, and average it over time.

But there are already good utilities available to help with this. Look into STATSPACK. Then look at the scripts shown here to get you started.

Alternatively you could install a commercial monitoring application like Spotlight on Oracle.

Answer 2

If you have Oracle Enterprise Manager set up you can create a User Defined Metric which records SELECT COUNT(*) FROM V$SESSION. Select Related Links -> User Defined Metrics to set up a new User Defined Metric. Once it collects some data you can get the data out in raw form or it will do some basic graphing for you. As a bonus you can also set up alerting if you want to be e-mailed when the metric reaches a certain value.

Answer 3

The tricky bit is recording the connections. Oracle doesn't do this by default, so if you haven't got anything in place then you won't have a historical record.

The easiest way to start recording connections is with Oracle's built in audit functionality. It's as simple as

audit session
/

We can see the records of each connection in a view called dba_audit_session.

Now what? The following query uses a Common Table Expression to generate a range of datetime values which span 8th July 2009 in five minute chunks. The output of the CTE is joined to the audit view for that date; A count is calulated for each connection which spans a five minute increment.

with t as
     ( select to_date('08-JUL-2009') + ((level-1) * (300/86400)) as five_mins
       from dual connect by level <= 288)
select to_char(t.five_mins, 'HH24:MI') as five_mins
       , sum(case when t.five_mins between timestamp and logoff_time 
                  then 1 
                  else 0 end) as connections  
from t
     , dba_audit_session ssn  
where trunc(ssn.timestamp) = to_date('08-JUL-2009')
group by to_char(t.five_mins, 'HH24:MI')
order by t.five_mins
/

You can then use this query as the input into a query which calculates the average number of connections.

This is a fairly crude implementation: I choose five minute increments out of display considerations , but obviously the finer grained the increment the more accurate the measure. Be warned: if you make the increments too fined grained and you have a lot of connections the resultant cross join will take a long time to run!