在Oracle中可以通过 dba_hist_active_sess_history视图查看并发的会话,可以通过以下脚本查询出每个用户最大并发,当前的连接数,及profile限制的最大连接数等。
SELECT AA.USERNAME, AA.Max_s, AA.Min_s, AA.AG_s, BB.CNT, CC.LIMIT
FROM (select username,
max(curr_user) Max_s,
min(curr_user) Min_s,
round(avg(curr_user)) AG_s
from (select username, snap_id, count(1) curr_user
from dba_hist_active_sess_history a, dba_users b
where --user_id = 65
-- sample_time >= to_date('2014-06-27', 'yyyy-mm-dd')
--and sample_time < to_date('2014-10-29', 'yyyy-mm-dd')
----and snap_id>=37306 and snap_id < 37401
--snap_id>=36062 and snap_id < 38073 285240710
a.user_id = b.user_id
group by username, snap_id)
group by username) AA,
(SELECT USERNAME, COUNT(1) CNT FROM V$SESSION GROUP BY USERNAME) BB,
(SELECT B.USERNAME, A.LIMIT
FROM dba_profiles A, DBA_USERS B
WHERE A.PROFILE = B.PROFILE
and b.account_status = 'OPEN'
AND A.resource_name = 'SESSIONS_PER_USER') CC
WHERE AA.USERNAME = BB.USERNAME
AND BB.USERNAME = CC.USERNAME;
结果如下:
USERNAME | MAX_S | MIN_S | AG_S | CNT | LIMIT |
APP_user1 | 19 | 1 | 2 | 50 | 200 |
APP_user2 | 4 | 1 | 1 | 10 | 300 |
APP_user3 | 1 | 1 | 1 | 10 | 70 |
APP_user4 | 16 | 1 | 3 | 60 | 70 |
APP_user5 | 3 | 1 | 1 | 40 | 50 |
APP_user6 | 11 | 1 | 2 | 80 | 161 |
APP_user7 | 850 | 7 | 87 | 465 | 1000 |
APP_user8 | 11 | 1 | 2 | 80 | 90 |
APP_user9 | 7 | 1 | 2 | 17 | 80 |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26390465/viewspace-1824559/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26390465/viewspace-1824559/