Shulou(Shulou.com)06/01 Report--

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 999999999.99

Col LAST_CPU_SECS wra format 999999999.99

Col logon_secs wra format 999999999

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) * 100penny 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 6743707 17328.94 17328.94. 26

4 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 1104.15 1104.15. 02

248 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 757.68 757.68. 01

258 Oracle Process ACTIVE 23-SEP-17 16:34:01 6743684 545.56 545.56. 01

12 Oracle Process ACTIVE 23-SEP-17 16:34:00 6743685 506.23 506.23. 01

85 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 426.53 426.53. 01

96 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:47 513238 264.17. 00. 05

84 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 239.57 239.57. 00

246 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 181.17 181.17. 00

106 DBSNMP INACTIVE 09-DEC-17 04:13:33 135312 49.85. 00. 04

273 DBSNMP INACTIVE 04-NOV-17 18:11:31 3109034 38.04. 00. 00

173 Oracle Process ACTIVE 23-SEP-17 16:34:00 6743685 35.15 35.15. 00

166 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726 25.01 25.01. 00

172 Oracle Process ACTIVE 23-SEP-17 16:33:51 6743694 24.51 24.51. 00

193 SHEC_USER INACTIVE 09-DEC-17 21:46:15 72150 5.41. 02. 01

103 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190 2.41. 00. 00

34 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190 1.80. 00. 00

14 PUBLIC INACTIVE 05-NOV-17 14:52:38 3034567 1.71. 00. 00

20 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196 1.42. 00. 00

101 Oracle Process ACTIVE 09-DEC-17 00:50:37 147488. 67. 67. 00

192 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 60. 00. 00

270 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 51. 23. 00

196 SYS ACTIVE 10-DEC-17 17:29:12 1173. 40. 00. 03 ff0k8584k3x9j

99 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 27. 00. 00

190 SHEC_USER INACTIVE 09-DEC-17 21:46:05 72160. 14. 00. 00

268 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 12. 00. 00

271 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 12. 00. 00

187 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 10. 00. 00

89 Oracle Process ACTIVE 23-SEP-17 16:33:49 6743696. 09. 09. 00

263 SHEC_USER INACTIVE 09-DEC-17 21:45:28 72197. 08. 00. 00

255 SHEC_USER INACTIVE 09-DEC-17 21:46:09 72156. 06. 00. 00

6 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 06. 00. 00

182 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 05. 01. 00

98 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 04. 00. 00

177 SHEC_USER INACTIVE 09-DEC-17 21:45:28 72197. 04. 00. 00

272 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 04. 00. 00

264 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 04. 00. 00

23 SHEC_USER INACTIVE 09-DEC-17 21:45:29 72196. 03. 00. 00

184 SHEC_USER INACTIVE 09-DEC-17 21:45:54 72171. 02. 00. 00

28 SHEC_USER INACTIVE 09-DEC-17 21:45:56 72169. 02. 00. 00

19 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 02. 00. 00

256 Oracle Process ACTIVE 23-SEP-17 16:34:00 6743685. 01. 01. 00

266 SHEC_USER INACTIVE 09-DEC-17 21:45:55 72170. 01. 00. 00

105 SHEC_USER INACTIVE 09-DEC-17 21:46:13 72152. 01. 00. 00

91 Oracle Process ACTIVE 23-SEP-17 16:34:00 6743685. 01. 01. 00

93 SHEC_USER INACTIVE 09-DEC-17 21:46:03 72162. 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 72190. 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 72179. 01. 00. 00

278 Oracle Process ACTIVE 10-DEC-17 06:00:00 42525. 00. 00. 00

275 SHEC_USER INACTIVE 09-DEC-17 21:45:59 72166. 00. 00. 00

269 SHEC_USER INACTIVE 09-DEC-17 21:45:45 72180. 00. 00. 00

267 SHEC_USER INACTIVE 09-DEC-17 21:46:19 72146. 00. 00. 00

252 Oracle Process ACTIVE 23-SEP-17 16:33:41 6743704. 00. 00. 00

250 Oracle Process ACTIVE 23-SEP-17 16:33:38 6743707. 00. 00. 00

247 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

245 Oracle Process ACTIVE 23-SEP-17 16:33:18 6743727. 00. 00. 00

244 Oracle Process ACTIVE 23-SEP-17 16:33:25 6743720. 00. 00. 00

197 SHEC_USER INACTIVE 09-DEC-17 21:45:58 72167. 00. 00. 00

183 SHEC_USER INACTIVE 09-DEC-17 21:45:43 72182. 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 72196. 00. 00. 00

169 Oracle Process ACTIVE 23-SEP-17 16:33:38 6743707. 00. 00. 00

167 Oracle Process ACTIVE 23-SEP-17 16:33:27 6743718. 00. 00. 00

165 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

164 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

163 Oracle Process ACTIVE 23-SEP-17 16:33:18 6743727. 00. 00. 00

104 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 00. 00. 00

100 SHEC_USER INACTIVE 09-DEC-17 21:45:57 72168. 00. 00. 00

97 SHEC_USER INACTIVE 09-DEC-17 21:45:35 72190. 00. 00. 00

94 Oracle Process ACTIVE 23-SEP-17 16:39:05 6743380. 00. 00. 00

87 Oracle Process ACTIVE 23-SEP-17 16:33:38 6743707. 00. 00. 00

86 Oracle Process ACTIVE 23-SEP-17 16:33:31 6743714. 00. 00. 00

83 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

82 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

31 SHEC_USER INACTIVE 09-DEC-17 21:45:51 72174. 00. 00. 00

27 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:47 513238. 00. 00. 00

26 SHEC_USER INACTIVE 09-DEC-17 21:46:11 72154. 00. 00. 00

25 SHEC_USER INACTIVE 09-DEC-17 21:46:00 72165. 00. 00. 00

22 HIDS_AUDITOR INACTIVE 04-DEC-17 19:14:45 513240. 00. 00. 00

16 Oracle Process ACTIVE 05-NOV-17 14:45:13 3035012. 00. 00. 00

7 Oracle Process ACTIVE 23-SEP-17 16:33:38 6743707. 00. 00. 00

5 Oracle Process ACTIVE 23-SEP-17 16:34:00 6743685. 00. 00. 00

3 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

2 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 00. 00. 00

1 Oracle Process ACTIVE 23-SEP-17 16:33:19 6743726. 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 999999999.99

Col LAST_CPU_SECS wra format 999999999.99

Col logon_secs wra format 999999999

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) * 100penny 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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.