Finding Sessions with High CPU Usage

If database server CPU usage is showing 100%, or high 90%, DBA needs to find out which session is hogging the CPU(s) and
take appropriate action.

I will explain in the following that how can we find out the session(s) which are excessively using CPU.

v$statname and v$sesstat dictionary view contains different metrics values, and in this example we will
be checking values of 2 metrics highlighted in red bellow.

These metrics would help to find out CPU usage by the sessions.

SQL> set linesize 150
col name format a70
select statistic#,name
from v$statname
where upper(name) like ‘%CPU%’;

STATISTIC# NAME
———- ———————————————————————-
0 OS CPU Qt wait time
10 recursive cpu usage
16 CPU used when call started
17 CPU used by this session
61 IPC CPU used by this session
64 global enqueue CPU used by this session
229 gc CPU used by this session
248 cell physical IO bytes sent directly to DB node to balance CPU
581 parse time cpu

9 rows selected.

STATISTIC# NAME
———- ———————————————————————-
0 OS CPU Qt wait time
10 recursive cpu usage
18 CPU used when call started
19 CPU used by this session
77 IPC CPU used by this session
80 global enqueue CPU used by this session
253 gc CPU used by this session
275 cell physical IO bytes sent directly to DB node to balance CPU
622 parse time cpu

9 rows selected.

If we want to check which session is top consumer of the CPU currently, we can use following script to find it out.
Script for 11g and bellow

alter session set nls_date_format=’Dd-MON-YY HH24:MI:SS’;

set lines 250
set pages 2000
col name format a26
col sid format 99999
col username format a15
col program format a40
col SESS_CPU_SECS wra format 999,999,999.99
col LAST_CPU_SECS wra format 999,999,999.99
col logon_secs wra format 999,999,999
col Percent wra format 999.99

select sess_cpu.sid,
NVL(sess_cpu.username, ‘Oracle Process’) username,
sess_cpu.status,
sess_cpu.logon_time,
round((sysdate – sess_cpu.logon_time)*1440*60) logon_SECS,
sess_cpu.value/100 SESS_CPU_SECS,
(sess_cpu.value – call_cpu.value)/100 LAST_CPU_SECS,
round ((sess_cpu.value/100)/round((sysdate – sess_cpu.logon_time)*1440*60)*100,2) Percent,
sess_cpu.sql_id
from (select se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value
from v$session se, v$sesstat ss, v$statname sn
where se.sid=ss.sid
and sn.statistic#=ss.statistic#
and sn.name in (‘CPU used by this session’) ) sess_cpu,
(select ss.statistic#,se.sid, ss.value, value/100 seconds from v$session se, v$sesstat ss, v$statname sn
where se.sid=ss.sid
and sn.statistic#=ss.statistic#
and sn.name in (‘CPU used when call started’) ) call_cpu
where sess_cpu.sid=call_cpu.sid
order by SESS_CPU_SECS desc;

SID USERNAME STATUS LOGON_TIME LOGON_SECS SESS_CPU_SECS LAST_CPU_SECS PERCENT SQL_ID
—— ————— ——– ——————– ———— ————— ————— ——- ————-
168 Oracle Process ACTIVE 23-SEP-17 16:33:38 6,743,707 17,328.94 17,328.94 .26
4 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 1,104.15 1,104.15 .02
248 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 757.68 757.68 .01
258 Oracle Process ACTIVE 23-SEP-17 16:34:01 6,743,684 545.56 545.56 .01
12 Oracle Process ACTIVE 23-SEP-17 16:34:00 6,743,685 506.23 506.23 .01
85 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 426.53 426.53 .01
96 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:47 513,238 264.17 .00 .05
84 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 239.57 239.57 .00
246 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 181.17 181.17 .00
106 DBSNMP INACTIVE 09-DEC-17 04:13:33 135,312 49.85 .00 .04
273 DBSNMP INACTIVE 04-NOV-17 18:11:31 3,109,034 38.04 .00 .00
173 Oracle Process ACTIVE 23-SEP-17 16:34:00 6,743,685 35.15 35.15 .00
166 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 25.01 25.01 .00
172 Oracle Process ACTIVE 23-SEP-17 16:33:51 6,743,694 24.51 24.51 .00
193 SHEC_USER INACTIVE 09-DEC-17 21:46:15 72,150 5.41 .02 .01
103 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 2.41 .00 .00
34 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 1.80 .00 .00
14 PUBLIC INACTIVE 05-NOV-17 14:52:38 3,034,567 1.71 .00 .00
20 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 1.42 .00 .00
101 Oracle Process ACTIVE 09-DEC-17 00:50:37 147,488 .67 .67 .00
192 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .60 .00 .00
270 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .51 .23 .00
196 SYS ACTIVE 10-DEC-17 17:29:12 1,173 .40 .00 .03 ff0k8584k3x9j
99 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .27 .00 .00
190 SHEC_USER INACTIVE 09-DEC-17 21:46:05 72,160 .14 .00 .00
268 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .12 .00 .00
271 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .12 .00 .00
187 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .10 .00 .00
89 Oracle Process ACTIVE 23-SEP-17 16:33:49 6,743,696 .09 .09 .00
263 SHEC_USER INACTIVE 09-DEC-17 21:45:28 72,197 .08 .00 .00
255 SHEC_USER INACTIVE 09-DEC-17 21:46:09 72,156 .06 .00 .00
6 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .06 .00 .00
182 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .05 .01 .00
98 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .04 .00 .00
177 SHEC_USER INACTIVE 09-DEC-17 21:45:28 72,197 .04 .00 .00
272 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .04 .00 .00
264 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .04 .00 .00
23 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .03 .00 .00
184 SHEC_USER INACTIVE 09-DEC-17 21:45:54 72,171 .02 .00 .00
28 SHEC_USER INACTIVE 09-DEC-17 21:45:56 72,169 .02 .00 .00
19 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .02 .00 .00
256 Oracle Process ACTIVE 23-SEP-17 16:34:00 6,743,685 .01 .01 .00
266 SHEC_USER INACTIVE 09-DEC-17 21:45:55 72,170 .01 .00 .00
105 SHEC_USER INACTIVE 09-DEC-17 21:46:13 72,152 .01 .00 .00
91 Oracle Process ACTIVE 23-SEP-17 16:34:00 6,743,685 .01 .01 .00
93 SHEC_USER INACTIVE 09-DEC-17 21:46:03 72,162 .01 .00 .00
24 Oracle Process ACTIVE 10-DEC-17 17:35:53 772 .01 .01 .00
195 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .01 .00 .00
186 DBSNMP INACTIVE 10-DEC-17 17:43:12 333 .01 .00 .00
21 SHEC_USER INACTIVE 09-DEC-17 21:45:46 72,179 .01 .00 .00
278 Oracle Process ACTIVE 10-DEC-17 06:00:00 42,525 .00 .00 .00
275 SHEC_USER INACTIVE 09-DEC-17 21:45:59 72,166 .00 .00 .00
269 SHEC_USER INACTIVE 09-DEC-17 21:45:45 72,180 .00 .00 .00
267 SHEC_USER INACTIVE 09-DEC-17 21:46:19 72,146 .00 .00 .00
252 Oracle Process ACTIVE 23-SEP-17 16:33:41 6,743,704 .00 .00 .00
250 Oracle Process ACTIVE 23-SEP-17 16:33:38 6,743,707 .00 .00 .00
247 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
245 Oracle Process ACTIVE 23-SEP-17 16:33:18 6,743,727 .00 .00 .00
244 Oracle Process ACTIVE 23-SEP-17 16:33:25 6,743,720 .00 .00 .00
197 SHEC_USER INACTIVE 09-DEC-17 21:45:58 72,167 .00 .00 .00
183 SHEC_USER INACTIVE 09-DEC-17 21:45:43 72,182 .00 .00 .00
181 SHEC_USER INACTIVE 10-DEC-17 17:45:57 168 .00 .00 .00
180 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72,196 .00 .00 .00
169 Oracle Process ACTIVE 23-SEP-17 16:33:38 6,743,707 .00 .00 .00
167 Oracle Process ACTIVE 23-SEP-17 16:33:27 6,743,718 .00 .00 .00
165 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
164 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
163 Oracle Process ACTIVE 23-SEP-17 16:33:18 6,743,727 .00 .00 .00
104 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .00 .00 .00
100 SHEC_USER INACTIVE 09-DEC-17 21:45:57 72,168 .00 .00 .00
97 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72,190 .00 .00 .00
94 Oracle Process ACTIVE 23-SEP-17 16:39:05 6,743,380 .00 .00 .00
87 Oracle Process ACTIVE 23-SEP-17 16:33:38 6,743,707 .00 .00 .00
86 Oracle Process ACTIVE 23-SEP-17 16:33:31 6,743,714 .00 .00 .00
83 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
82 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
31 SHEC_USER INACTIVE 09-DEC-17 21:45:51 72,174 .00 .00 .00
27 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:47 513,238 .00 .00 .00
26 SHEC_USER INACTIVE 09-DEC-17 21:46:11 72,154 .00 .00 .00
25 SHEC_USER INACTIVE 09-DEC-17 21:46:00 72,165 .00 .00 .00
22 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:45 513,240 .00 .00 .00
16 Oracle Process ACTIVE 05-NOV-17 14:45:13 3,035,012 .00 .00 .00
7 Oracle Process ACTIVE 23-SEP-17 16:33:38 6,743,707 .00 .00 .00
5 Oracle Process ACTIVE 23-SEP-17 16:34:00 6,743,685 .00 .00 .00
3 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
2 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00
1 Oracle Process ACTIVE 23-SEP-17 16:33:19 6,743,726 .00 .00 .00

87 rows selected.

Script for 12c and above
Column CON_ID is added in the script to list the container id where sessions are connected. If CON_ID is 0, it would mean that this is an internal process/session. If value is 1, it would mean that this session is from container database. Any value other than 0 or 1 would represent a pluggable database sessions. Check v$containers to match container ID with your pluggable database.

alter session set nls_date_format=’Dd-MON-YY HH24:MI:SS’;

set lines 250
set pages 2000
col name format a26
col username format a15
col program format a40
col SESS_CPU_SECS wra format 999,999,999.99
col LAST_CPU_SECS wra format 999,999,999.99
col logon_secs wra format 999,999,999
col Percent wra format 999.99

select sess_cpu.con_id,
sess_cpu.sid,
NVL(sess_cpu.username, ‘Oracle Process’) username,
sess_cpu.status,
sess_cpu.logon_time,
round ((sysdate-sess_cpu.logon_time)*1440*60) logon_SECS,
sess_cpu.value/100 SESS_CPU_SECS,
(sess_cpu.value – call_cpu.value)/100 LAST_CPU_SECS,
round((sess_cpu.value/100)/round((sysdate – sess_cpu.logon_time)*1440*60)*100,2) Percent,
sess_cpu.sql_id
from (select se.con_id,se.sql_id,ss.statistic#,se.sid, se.username, se.status, se.program, se.logon_time, sn.name, ss.value
from v$session se, v$sesstat ss, v$statname sn
where se.sid=ss.sid
and sn.statistic#=ss.statistic#
and sn.name in (‘CPU used by this session’) ) sess_cpu,
(select se.con_id, ss.statistic#,se.sid, ss.value, value/100 seconds
from v$session se, v$sesstat ss, v$statname sn
where se.sid=ss.sid
and sn.statistic#=ss.statistic#
and sn.name in (‘CPU used when call started’) ) call_cpu
where sess_cpu.sid=call_cpu.sid
and sess_cpu.con_id=call_cpu.con_id
order by SESS_CPU_SECS ;

LOGON_SECS column shows total time in seconds this session is connected to the database.
SESS_CPU_SECS columns shows number of seconds this session has spent using CPU.
LAST_CPU_SECS column shows the CPU taken by last execution call C a single SQL may call CPU several times during its execution.
PERCENT column shows the percent of time this session has spent using CPU since this session has connect to the database.

Points to note
While on CPU, LAST_CPU_SECS would show zero (session status would be ACTIVE). During execution of SQL(s), session may be switching between CPU and IO – and hence we can see value changing under SESS_免费云主机域名CPU_SECS and LAST_CPU_SECS columns as soon as session switches from CPU to IO.
If a session is on CPU, status would be ACTIVE and SESS_CPU_SECS would be showing last value captured for this session, and LAST_CPU_DECS would be 0.
If a session is on IO, status would be ACTIVE and SESS_CPU_SECS would be showing total seconds of CPU taken by the session until now.
LAST_CPU_SECS would show CPU seconds taken during last call to the CPU. I have also seen value of zero under this column even if session is not ACTIVE. I can’t figure it out why, but most important thing is total time of CPU taken by the session which is under SESS_CPU_SECS.
See the following example where I have used ORDER BY SESS_CPU_SECS to display sessions which are at the top CPU consumption since log in.

If you see session bellow highlighted in red, it is connected to the database for 799 seconds
and out of which it has spent 260 seconds on CPU which accounts to 32.55 percent of total session time.

In real time scenario, if you see a session spending huge amount time on CPU, have a look at the SQL(s) it is executing (last column showing SQL_ID) and investigate further.
You can modify this script to ?ORDER BY PERCENT? to see which session has spent most of its connected time on CPU.

相关推荐: X86平台RAC安装最佳实践

ORACLE数据库X86化是发展的一种趋势，通过多年的工作实践，整理了这篇X86平台RAC安装最佳实践。希望大家对X86平台的最佳实践有一定了解。文档从以下几个方面进行信息描述。硬件架构更多详细内容，可免费云主机域名以阅读GitHub上分享的PDF。https…