Highly CPU-consumed Session in oracle
I noticed a heavy-load of CPU usage on my production server this morning. The CPU utilization hit 99% on peak time.
In order to identify the session that contributes high portion of CPU usage, the solaris command “prstat” is issued.
PROD> prstat
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
16061 oracle 1877M 1834M cpu2 20 0 0:21:12 21% oracle/1
13895 oracle 667M 624M sleep 60 0 1:20:03 3.1% oracle/11
13917 oracle 660M 614M sleep 60 0 1:03:54 0.6% oracle/11
15445 oracle 1878M 1836M sleep 59 0 0:03:17 0.6% oracle/11
23694 oracle 1879M 1828M sleep 59 0 1:23:32 0.6% oracle/11
19663 oracle 1879M 1839M cpu0 49 0 0:00:08 0.5% oracle/1
19341 oracle 1880M 1839M sleep 59 0 0:00:02 0.5% oracle/1
8309 oracle 1878M 1827M sleep 59 0 1:43:37 0.5% oracle/11
23734 oracle 1878M 1827M sleep 59 0 1:23:55 0.5% oracle/11
19580 oracle 1878M 1838M sleep 59 0 0:00:05 0.5% oracle/1
9377 oracle 1878M 1827M sleep 59 0 0:29:19 0.5% oracle/11
19891 oracle 1879M 1835M sleep 59 0 0:00:00 0.4% oracle/1
24987 oracle 1878M 1827M sleep 59 0 1:16:49 0.4% oracle/11
23738 oracle 1879M 1827M sleep 59 0 1:24:15 0.4% oracle/11
23723 oracle 1878M 1828M sleep 59 0 1:23:34 0.4% oracle/11
Total: 497 processes, 3783 lwps, load averages: 2.12, 2.30, 2.66
PROD>
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
16061 oracle 1877M 1834M cpu2 20 0 0:21:12 21% oracle/1
13895 oracle 667M 624M sleep 60 0 1:20:03 3.1% oracle/11
13917 oracle 660M 614M sleep 60 0 1:03:54 0.6% oracle/11
15445 oracle 1878M 1836M sleep 59 0 0:03:17 0.6% oracle/11
23694 oracle 1879M 1828M sleep 59 0 1:23:32 0.6% oracle/11
19663 oracle 1879M 1839M cpu0 49 0 0:00:08 0.5% oracle/1
19341 oracle 1880M 1839M sleep 59 0 0:00:02 0.5% oracle/1
8309 oracle 1878M 1827M sleep 59 0 1:43:37 0.5% oracle/11
23734 oracle 1878M 1827M sleep 59 0 1:23:55 0.5% oracle/11
19580 oracle 1878M 1838M sleep 59 0 0:00:05 0.5% oracle/1
9377 oracle 1878M 1827M sleep 59 0 0:29:19 0.5% oracle/11
19891 oracle 1879M 1835M sleep 59 0 0:00:00 0.4% oracle/1
24987 oracle 1878M 1827M sleep 59 0 1:16:49 0.4% oracle/11
23738 oracle 1879M 1827M sleep 59 0 1:24:15 0.4% oracle/11
23723 oracle 1878M 1828M sleep 59 0 1:23:34 0.4% oracle/11
Total: 497 processes, 3783 lwps, load averages: 2.12, 2.30, 2.66
PROD>
The output show several sessions that take large portion of CPU usage.
Next, I issued the following sql statement against production database
select ss.sid, se.command, ss.value CPU, se.username, se.program, sp.spid
from v$sesstat ss, v$session se, v$process sp
where ss.statistic# in
(select statistic#
from v$statname
where name = ‘CPU used by this session’) and se.sid = ss.sid and
se.sid > 6 and se.paddr=sp.addr and sp.spid in (16601, 13895, 13917, 15445, 23694, 19663)
order by CPU desc;
from v$sesstat ss, v$session se, v$process sp
where ss.statistic# in
(select statistic#
from v$statname
where name = ‘CPU used by this session’) and se.sid = ss.sid and
se.sid > 6 and se.paddr=sp.addr and sp.spid in (16601, 13895, 13917, 15445, 23694, 19663)
order by CPU desc;
We got output in reverse order of CPU usage, which is actually statistics value from v$sesstat. It’s the cumulative CPU usage of current active oracle session taken.
Then, I went to check the session listed in Toad and found several sessions is in status inactive. But, the CPU usage still keeps increasing. After investigation, I found that those several sessions are third-party application sessions. And, I doubted that there is bug on those sessions. The bug make it happens.Last, I killed those inactive sessions and the CPU utilization markedly dropped.
Last 3 posts in OraclePoint
- Upgrade Oracle Database from 10g to 11g with Data Pump - July 28th, 2010
- My Practical Approach to Create Domain Index - May 11th, 2010
- How to Verify Memory Leaks on Unix - May 10th, 2010
Popularity: 3% [?]
Tags:none
Leave a Reply