My Oracle Support Banner

Sql Execution Stuck on Wait Event "Disk File Operations I/O" after adding New DiskGroup (Doc ID 2446479.1)

Last updated on FEBRUARY 21, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
IBM AIX on POWER Systems (64-bit)

Symptoms

AWR report shows that Foreground Events are hugely waiting for "Disk file operations I/O"

 

Vmstat shows that CPU usage of sys is increasing from 43 to 54 CPU usage.

1. findings  : CPU Usage (%)         

System Configuration: lcpu=144 mem=245760MB

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
181 0 45569445 1889850 0 0 0 0 0 0 17087 383598 283803 36 47 15 2 <<<<<
81 0 45611894 1847088 0 0 0 0 0 0 17401 352654 284975 35 50 14 2
121 0 45600510 1858173 0 0 0 0 0 0 19224 364870 299954 35 50 14 2
zzz ***Wed Sep 5 16:20:08 KORST 2018

System Configuration: lcpu=144 mem=245760MB

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
85 0 45856168 1594872 0 0 0 0 0 0 19667 400954 312172 33 44 20 3
227 0 45854018 1596282 0 0 0 0 0 0 18834 394872 296244 38 43 16 3
194 0 45837573 1612078 0 0 0 0 0 0 19630 410906 292502 33 52 13 2
zzz ***Wed Sep 5 16:20:52 KORST 2018

System Configuration: lcpu=144 mem=245760MB

kthr memory page faults cpu
----- ----------- ------------------------ ------------ -----------
r b avm fre re pi po fr sr cy in sy cs us sy id wa
77 0 46244354 1183930 0 0 0 0 0 0 18568 410423 302666 33 49 16 2
139 1 46239592 1187903 0 0 0 0 0 0 19890 380224 311857 27 54 16 3 <<<<
263 1 46257921 1168812 0 0 0 0 0 0 17771 409597 275859 38 45 14 2                      

2. findings : Disk High Busy (%)


DEVICE: hdisk9

SnapTime Value
-------------------------------------------
Tue Sep 04 14:20:26 GMT 2018 80.7
Tue Sep 04 14:21:27 GMT 2018 84.1
Tue Sep 04 14:22:29 GMT 2018 88.1
Tue Sep 04 14:24:45 GMT 2018 83.0
Tue Sep 04 14:25:36 GMT 2018 97.3
Tue Sep 04 14:27:32 GMT 2018 95.3
Tue Sep 04 14:29:08 GMT 2018 83.2
Tue Sep 04 14:30:27 GMT 2018 82.1
Tue Sep 04 14:32:11 GMT 2018 98.1
Tue Sep 04 14:33:08 GMT 2018 98.1
Tue Sep 04 14:34:04 GMT 2018 92.5
Tue Sep 04 14:35:06 GMT 2018 91.6
Tue Sep 04 14:43:27 GMT 2018 82.4
Tue Sep 04 15:10:51 GMT 2018 100.0  <<<<

DEVICE: hdisk10
SnapTime Value
-------------------------------------------
Tue Sep 04 14:43:27 GMT 2018 82.4
Tue Sep 04 14:44:06 GMT 2018 83.0
Tue Sep 04 14:44:46 GMT 2018 90.7
Tue Sep 04 14:48:32 GMT 2018 98.1  <<<
Tue Sep 04 14:49:11 GMT 2018 91.7
Tue Sep 04 15:18:26 GMT 2018 88.5
Tue Sep 04 15:18:56 GMT 2018 94.2
Tue Sep 04 15:19:27 GMT 2018 86.5

3. findings : Disk High Service Time (mSec)


DEVICE: hdisk1378
SnapTime Value
-------------------------------------------
Tue Sep 04 14:26:34 GMT 2018 140.0
Tue Sep 04 15:49:06 GMT 2018 20.9

DEVICE: hdisk1377
SnapTime Value
-------------------------------------------
Tue Sep 04 14:00:41 GMT 2018 90.1
Tue Sep 04 14:13:27 GMT 2018 21.7

DEVICE: hdisk796
SnapTime Value
-------------------------------------------
Tue Sep 04 14:00:41 GMT 2018 101.5
Tue Sep 04 14:04:46 GMT 2018 29.9


4. findings :  Top Event P1/P2/P3 Values from ASH report.

Waiting for 'Disk file operations I/O' .. FileOperation=2

Top Event P1/P2/P3 Values

Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
Disk file operations I/O 51.89 "2","3117","2" 0.27 FileOperation fileno filetype <<<<<------

  

 

Changes

 Add a New DiskGroup with incorrect RPM configurations.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.