Oracle中如何评估真正的并发session

Oracle中可以通过dba_hist_active_sess_history视图查看并发的会话,可以通过以下脚本查询出每个用户最大并发,当前的连接数,及profile限制的最大连接数等。

SELECT AA.USERNAME, AA.MT, BB.CNT, CC.LIMIT, AA.MN, AA.AG
  FROM (select username, max(cnt) MT, min(cnt) MN, avg(cnt) AG
          from (select username, snap_id, count(1) cnt
                  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  MT  CNT  LIMIT  MN  AG 
APP_user1  14  100  100  1  1.790323 
APP_user2  6  100  100  1  1.571429 
APP_user3  3  100  100  1  1.328125 
APP_user4  6  100  100  1  1.425532 
APP_user5  6  100  100  1  1.753846 

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/90b639607ac112709132ce93532fcbb2.html