查询Oracle进程数和会话数

查询Oracle进程数和会话数

𝓓𝓸𝓷 Lv6
  2024-06-12 20:23:30 2024-06-12 20:23:30 Created   2026-08-21 14:40 2026-08-21 14:40 Updated    

统计Oracle数据库进程和会话数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
col name for a15 
col value for a15
select
  p.name,
  p.value,
  r.max_utilization
from
  v$resource_limit r,
  v$parameter p
where
  r.resource_name = p.name
  and p.name in ('processes', 'sessions');

v$resource_limit视图中的max_utilization参数可获取数据库启动以来的最大会话连接数.


set linesize 200 pagesize 200
col username for a15
col machine for a25
col osuser for a15

select distinct sysdate,
                osuser,
                username,
                machine,
                status,
                count(username) over(partition by username, status) user_status_count,
                count(username) over(partition by username) user_count,
                count(osuser) over() session_count,
                count(sysdate) over() process_count
  from (select sysdate,
               s.osuser,
               decode(osuser,
                      'oracle',
                      'oracle',
                      '',
                      'OracleProcess',
                      s.username) username,
               s.machine,
               s.status,
               p.program
          from v$session s
         right join v$process p
            on (s.paddr = p.addr))
 order by username;



set linesize 200 pagesize 200
col username for a15
col machine for a25
col osuser for a15
col max_session for a10
col max_process for a10

select distinct sysdate,
                osuser,
                username,
                machine,
                status,
                count(username) over(partition by username, status) user_status_count,
                count(username) over(partition by username) user_count,
                count(osuser) over() session_count,
                count(sysdate) over() process_count,
                ps.value max_session,
                pp.value max_process
  from (select sysdate,
               s.osuser,
               decode(osuser,
                      'oracle',
                      'oracle',
                      '',
                      'OracleProcess',
                      s.username) username,
               s.machine,
               s.status,
               p.program
          from v$session s
         right join v$process p
            on (s.paddr = p.addr)),
       v$parameter pp,
       v$parameter ps
 where pp.name = 'processes'
   and ps.name = 'sessions'
 order by username;


SELECT (SELECT Round((SELECT Count(*)
                      FROM   v$process) / value * 100)
        FROM   v$parameter
        WHERE  name = 'processes') p,
       (SELECT Round((SELECT Count(*)
                      FROM   v$session) / value * 100)
        FROM   v$parameter
        WHERE  name = 'sessions')  s
FROM   dual;


set linesize 200 pagesize 200
col username for a15
col machine for a25
col osuser for a15
col max_session for a10
col max_process for a10

select distinct sysdate,
                nvl(osuser,'N/A') osuser,
                username,
                nvl(machine,'N/A') machine, 
                nvl(status,'N/A') status,
                count(username) over(partition by username, status) user_status_count,
                count(username) over(partition by username) user_count,
                count(osuser) over() session_count,
                count(sysdate) over() process_count,
                ps.value max_session,
                pp.value max_process
  from (select sysdate,
               s.osuser,
               decode(osuser,
                      'oracle',
                      'oracle',
                      '',
                      'OracleProcess',
                      s.username) username,
               s.machine,
               s.status,
               p.program
          from v$session s
         right join v$process p
            on (s.paddr = p.addr)),
       v$parameter pp,
       v$parameter ps
 where pp.name = 'processes'
   and ps.name = 'sessions'
 order by username;



---统计进程数

select sysdate,s.osuser, decode(osuser,'oracle','OracleProcess','','OtherProcess',s.username) username,s.machine,s.program,p.spid,s.paddr,p.addr,s.sql_id from v$process p left join v$session s on (s.paddr = p.addr);


select sysdate,s.osuser,decode(substr(s.program,1,7),'oracle@','OracleProcess','','OtherProcess',s.username) username,s.machine,count(*) from v$process p left join v$session s on (s.paddr = p.addr)
group by sysdate,s.osuser,decode(substr(s.program,1,7),'oracle@','OracleProcess','','OtherProcess',s.username),s.machine


select sysdate,osuser,username,machine,sn count,sum(sn) over(partition by username) user_count,sum(sn) over() all_process from (
select sysdate,s.osuser, decode(osuser,'oracle','OracleProcess','','OtherProcess',s.username) username,s.machine,count(*) sn from v$process p left join v$session s on (s.paddr = p.addr)
group by sysdate,s.osuser, decode(osuser,'oracle','OracleProcess','','OtherProcess',s.username),s.machine);



---统计会话数

select PID,SPID,addr,USERNAME,PROGRAM from v$process;

select sid,serial#,PADDR,SADDR,USERNAME,PROGRAM,machine from v$session where paddr='&addr';

---统计历史最大会话数和最大进程数

set linesize 200 pagesize 200
col resource_name for a20
col limit_value for a20
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE  from v$resource_limit where resource_name='processes' or resource_name='sessions';

---查看历史快照会话和进程当前值及最大值

select b.snap_id,
       b.begin_interval_time,
       b.end_interval_time,
       a.RESOURCE_NAME,
       a.CURRENT_UTILIZATION,
       a.MAX_UTILIZATION,
       a.LIMIT_VALUE
  from DBA_HIST_RESOURCE_LIMIT a, dba_hist_snapshot b
 where a.snap_id = b.snap_id
   and a.dbid = b.dbid
   and a.resource_name in ('processes', 'sessions')
   and b.begin_interval_time >
       to_date('2014/07/27 08:30:00', 'yyyy/mm/dd hh24:mi:ss')
   and b.begin_interval_time <
       to_date('2014/07/27 10:30:00', 'yyyy/mm/dd hh24:mi:ss')
       
       
---查看7天内session和process使用情况

select b.snap_id,
       b.begin_interval_time,
       b.end_interval_time,
       a.RESOURCE_NAME,
       a.CURRENT_UTILIZATION,
       a.MAX_UTILIZATION,
       a.LIMIT_VALUE
  from DBA_HIST_RESOURCE_LIMIT a, dba_hist_snapshot b
 where a.snap_id = b.snap_id
   and a.dbid = b.dbid
   and a.resource_name in ('processes', 'sessions')
   and b.begin_interval_time > sysdate - 7;

---历史会话(AWR)
SELECT 
    c.username,
    a.sample_time,
    a.session_id,
    a.sql_id,
    b.sql_text,
    a.program,
    a.machine
FROM 
    DBA_HIST_ACTIVE_SESS_HISTORY a,
    DBA_HIST_SQLTEXT b,
    DBA_USERS c
WHERE 
    a.sql_id = b.sql_id(+)
    AND a.user_id = c.user_id(+)
    AND a.sample_time > SYSDATE - 1
ORDER BY 
    a.sample_time DESC;
    
注意：AWR数据默认保留7天，可通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS调整保留周期

---查看历史活动会话数
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
select trunc(sample_time, 'mi'), count(1)
  from gv$active_session_history
 where sample_time > to_date('20260821 14:20:00', 'yyyymmdd hh24:mi:ss')
   and sample_time < to_date('20260821 14:27:00', 'yyyymmdd hh24:mi:ss')
 group by trunc(sample_time, 'mi')
 order by 1;

select trunc(sample_time, 'mi'), count(1)
  from dba_hist_active_sess_history
 where sample_time > to_date('20260821 08:30:00', 'yyyymmdd hh24:mi:ss')
   and sample_time < to_date('20260821 12:30:00', 'yyyymmdd hh24:mi:ss') and event is not null
 group by trunc(sample_time, 'mi')
having count(1) > 2
 order by 1;
 
 $active_session_history查询的会话数与select count(*) from v$session where status='ACTIVE'的会话数结果会有差异，因为它们统计的‌数据源‌、‌统计机制‌和‌时间维度‌完全不同:。
 
 ‌统计对象与范围不同‌

v$session 是‌实时全量‌视图：它记录了当前数据库中所有存在的会话（Session）。status='ACTIVE' 筛选的是此刻状态标记为“活跃”的会话总数，这是一个精确的瞬时快照。
v$active_session_history (ASH) 是‌采样历史‌视图：它不记录所有会话，而是每秒对正在执行 SQL 或等待非空闲事件的会话进行一次‌采样‌。如果会话处于空闲（IDLE）状态，或者在采样间隔之间状态发生了变化，可能不会被记录或记录行数不同 。‌
‌时间维度不同‌

v$session 反映的是‌当前这一刻‌（Current Time）的并发情况。
v$active_session_history 默认反映的是‌过去一段时间‌（通常内存中保留约 1 小时）的累积采样记录。直接 count(*) 会得到历史采样行的总数，而非当前瞬间的会话数 。‌
‌状态定义细节不同‌

在 v$session 中，STATUS='ACTIVE' 依赖于会话当前的状态标志。
在 ASH 中，只有当会话处于 ON CPU 或 WAITING（非空闲等待类）时才会被采样记录。某些短暂的活跃状态可能在两次采样之间被遗漏，导致 ASH 的计数通常小于或等于同一时刻的 v$session 活跃数，但在聚合历史数据时数值会远大于当前活跃数 。‌


---等待事件和等待次数多的会话
select trunc(sample_time, 'mi'), event, count(1)
  from dba_hist_active_sess_history
 where sample_time > to_date('20260821 08:30:00', 'yyyymmdd hh24:mi:ss')
   and sample_time < to_date('20260821 12:30:00', 'yyyymmdd hh24:mi:ss')
   and event is not null
 group by trunc(sample_time, 'mi'), event
having count(1) > 2
 order by 1, 3;


with ash as
 (select instance_number,
         session_id,
         event,
         blocking_session,
         program,
         to_char(sample_time, 'yyyymmdd hh24miss') sample_time,
         sample_id,
         blocking_inst_id
    from dba_hist_active_sess_history
   where sample_time > to_date('20260821 08:30:00', 'yyyymmdd hh24:mi:ss')
     and sample_time < to_date('20260821 12:30:00', 'yyyymmdd hh24:mi:ss'))
select *
  from (select sample_time,
               blocking_session final_block,
               sys_connect_by_path(session_id, ',') sid_chain,
               sys_connect_by_path(event, ',') event_chain
          from ash
         start with session_id is not null
        connect by prior blocking_session = session_id
               and prior instance_number = blocking_inst_id
               and sample_id = prior sample_id) a
 where instr(sid_chain, final_block) = 0
   and not exists (select 1
          from ash b
         where a.final_block = b.session_id
           and b.blocking_session is not null)
 order by sample_time;
  • Title: 查询Oracle进程数和会话数
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-06-12 20:23:30
  • Updated at : 2026-08-21 14:40:00
  • Link: https://www.zhangdong.me/oracle-session-process.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
查看Oracle等待事件 查看Oracle等待事件 Oracle批量杀会话 Oracle批量杀会话 查看Oracle会话 查看Oracle会话
推荐文章
查看Oracle等待事件 查看Oracle等待事件 Oracle批量杀会话 Oracle批量杀会话
评论