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
| 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';
|
