Hi oracle friends : )
I would be grateful for some comments about the following situation.. maybe i forgot some important oracle fundamentals so
i'm kind of lost currently here, without ideas, and work overloaded to take additional investigations...
OS is Solaris, db is 11.2.0.1
When i do prstat , i am seeing oracle PID 3472 as the process which is using the highest amount of the CPU time...
SQL> !prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
3472 oracle 2497M 681M sleep 10 0 0:03:03 8.1% oracle/11
28578 adc 915M 881M cpu6 53 0 1:10:16 4.9% java/65
1864 oracle 2375M 543M sleep 40 0 0:12:50 3.5% oracle/11
1882 oracle 2373M 541M sleep 50 0 0:16:04 3.3% oracle/1
3047 oracle 2373M 535M sleep 20 0 0:02:18 3.3% oracle/1
2668 oracle 2373M 540M sleep 53 0 0:04:49 3.1% oracle/1
1191 oracle 2382M 534M sleep 59 0 0:22:48 2.6% oracle/258
1868 oracle 2375M 542M sleep 52 0 0:19:41 2.3% oracle/11
2413 oracle 2373M 535M sleep 53 0 0:10:08 2.1% oracle/1
1193 oracle 2384M 526M sleep 54 0 0:10:10 1.6% oracle/11
2670 oracle 2373M 535M sleep 59 0 0:06:47 1.4% oracle/1
1195 oracle 2373M 525M sleep 59 0 0:02:24 0.4% oracle/11
5 root 0K 0K sleep 99 -20 0:46:41 0.4% zpool-rpool/51
4971 adc 149M 142M sleep 59 0 2:11:59 0.3% java/96
28606 adc 5788K 4676K sleep 59 0 0:03:33 0.2% rsi_lnk/1
1858 oracle 2375M 544M sleep 59 0 0:14:52 0.1% oracle/11
1850 oracle 2375M 543M sleep 59 0 0:14:18 0.1% oracle/11
4500 adc 528M 521M sleep 59 0 8:31:50 0.1% java/98
28561 adc 5804K 4788K sleep 59 0 0:01:18 0.1% rsi/1
3045 oracle 2373M 535M sleep 59 0 0:02:08 0.0% oracle/1
28577 adc 3220K 1932K sleep 59 0 0:00:23 0.0% wrapper/2
3593 oracle 4084K 3340K cpu4 49 0 0:00:00 0.0% prstat/1
4493 adc 3700K 2124K sleep 59 0 0:04:25 0.0% wrapper/2
1880 oracle 2373M 544M sleep 59 0 0:14:39 0.0% oracle/1
3619 oracle 2372M 536M sleep 59 0 0:00:00 0.0% oracle/1
1175 oracle 2371M 522M sleep 101 - 0:00:06 0.0% oracle/1
14389 adc 297M 250M sleep 59 0 0:01:26 0.0% java/42
1872 oracle 2375M 537M sleep 59 0 0:18:05 0.0% oracle/11
3061 oracle 62M 17M sleep 59 0 0:01:20 0.0% tnslsnr/3
1668 root 104M 91M sleep 59 0 0:08:15 0.0% java/37
3631 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3633 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3627 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3629 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3623 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3617 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3611 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
3615 oracle 2371M 531M sleep 59 0 0:00:00 0.0% oracle/1
11161 noaccess 146M 117M sleep 59 0 0:01:40 0.0% java/18
1187 oracle 2372M 523M sleep 59 0 0:00:03 0.0% oracle/1
Total: 280 processes, 2957 lwps, load averages: 3.81, 3.96, 3.61
Next that came to my mind was to join v$process and v$session to see what session relates to that PID..
SQL> select s.sid, s.serial#, p.spid
from
v$session s,
v$process p
where
p.spid=3472 ;
SID SERIAL# SPID
---------- ---------- ------------------------
1 1 3472
2 1 3472
4 3 3472
6 5 3472
7 10 3472
8 11 3472
9 518 3472
10 1687 3472
32 1 3472
33 5 3472
34 192 3472
SID SERIAL# SPID
---------- ---------- ------------------------
36 10 3472
37 10 3472
38 292 3472
39 805 3472
63 1 3472
64 1 3472
65 7 3472
66 271 3472
67 8 3472
68 10 3472
69 11 3472
SID SERIAL# SPID
---------- ---------- ------------------------
71 168 3472
94 1 3472
95 1 3472
96 90 3472
97 7 3472
99 2 3472
100 13 3472
102 10 3472
103 817 3472
125 1 3472
126 1 3472
SID SERIAL# SPID
---------- ---------- ------------------------
127 393 3472
128 2 3472
129 2 3472
131 1 3472
133 19 3472
134 920 3472
156 169 3472
157 1 3472
158 1 3472
159 5 3472
160 2 3472
SID SERIAL# SPID
---------- ---------- ------------------------
161 4 3472
164 21 3472
167 621 3472
187 1 3472
188 1 3472
190 850 3472
191 3 3472
192 2 3472
193 2 3472
195 8 3472
196 82 3472
SID SERIAL# SPID
---------- ---------- ------------------------
218 1 3472
219 1 3472
221 358 3472
222 13 3472
223 55 3472
224 7 3472
226 850 3472
227 373 3472
..and it turned out that this OS process belongs to many oracle sessions.. so that would mean that killing the 3472 PID would also kill all that db related sessions..
I guess i am wrong when i expect relation of the oracle user sessions to the OS processes would be one to one.