오라클 세션수 조회

* 동접 세션 이전 최대 개수

SQL> select SESSIONS_HIGHWATER from v$license;

SESSIONS_HIGHWATER
------------------
                25

* 현재 세션 중 Active한 세션 개수 조회

SQL> select to_char(sysdate, 'YYYY/MM/DD hh24:mi:ss') "Time",
  2         count(*) "Total Sessions",
  3         count(decode(status, 'ACTIVE', 1) ) "Active Sessions"
  4  from   v$session;

Time                Total Sessions Active Sessions
------------------- -------------- ---------------
2015/04/28 22:27:49             21              18

* 현재 사용 세션 개수, 최대 사용 세션 개수 및 제한 세션 개수

select resource_name, current_utilization, max_utilization, limit_value 
    from v$resource_limit 
    where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
------------- ------------------- --------------- -----------
processes                      96             309         500
sessions                      104             323         792

* 사용자 세션 상세

select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'order by spid;

Average Active Sessions (AAS)

[출처] : http://www.dba-scripts.com/scripts/diagnostic-and-tuning/oracle-active-session-history-ash/average-active-sessions-aas/

The Average Active Sessions (AAS) metric is a very good indicator of the database activity.
This metric represents the number of sessions, either working or waiting for a resource at a specific point in time.
Idle sessions are not included in the calculation of this metric.
To calculate AAS, we need another metric called “DB Time” which represents the total time spent in the database by sessions, either working or waiting (The real work done in the database).
This DB time is divided by the clock elapsed time to obtain the Average Active Sessions.
The following script will calculate the Average Active Sessions from v$active_session_history view.

Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it.




* Average Active Sessions from v$active_session_history

1

select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS from (select min(sample_time) sample_time from  v$active_session_history ash ) start_time, (select max(sample_time) sample_time from  v$active_session_history ) end_time, v$active_session_history ash where ash.sample_time between start_time.sample_time and end_time.sample_time group by end_time.sample_time,start_time.sample_time;

You can restrict period of time analyzed by filtering with the sample_time column. Here is AAS from the last hour :

1 2 3 4 5 6 7 8 9 10 11

select round((count(ash.sample_id) / ((CAST(end_time.sample_time AS DATE) - CAST(start_time.sample_time AS DATE))*24*60*60)),2) as AAS from (select min(sample_time) sample_time from  v$active_session_history ash where sample_time between sysdate-1/24 and sysdate) start_time, (select max(sample_time) sample_time from  v$active_session_history where sample_time between sysdate-1/24 and sysdate) end_time, v$active_session_history ash where ash.sample_time between start_time.sample_time and end_time.sample_time group by end_time.sample_time,start_time.sample_time;

This metric can be correlated with the Graph from v$active_session_history

This query returns the top 5 wait events for the last hour from the v$active_session_history view.


Be careful, this view is part of the diagnostic pack, you should not query this view if you not licensed for it.

* Top 5 wait events from v$active_session_history

1 2 3 4 5 6 7 8 9 10 11

select * from ( select WAIT_CLASS , EVENT, count(sample_time) as EST_SECS_IN_WAIT from v$active_session_history where sample_time between sysdate - interval '1' hour and sysdate group by WAIT_CLASS,EVENT order by count(sample_time) desc ) where rownum <6 ;

This is obviously an approximation, because v$active_session_history contains only 1 second samples, and who knows what happens during each second sample. If you compare the time given by this query with information from v$system_event it will not exactly match but you should be close if you choose a sufficiently long period.