I'm an Oracle developper.
I started working for a new client and the I/O seems kind of slow as opposed to what I am used to see at other clients.
The application is a 6 terrabytes datawarehouse using 8k block size.
The client is using a two nodes RAC 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production on Linux x86 64-bit.
They are using Oracle ACFS for storage.
I do not have the details about the RAC servers but the CPU is idle most of the time over a 24h period.
This comes from the AWR report (inst#2 only) for a whole day...
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
db file sequential read 37,827,310 199. 5 28.7 User I/O
direct path read 5,020,635 160. 32 23.0 User I/O
DB CPU 105. 15.2
direct path write temp 490,363 38K 78 5.5 User I/O
enq: SS - contention 3,559 24.1 6784 3.5 Configurat
db file scattered read 8,335,282 23.7 3 3.4 User I/O
buffer busy waits 741,334 22K 30 3.2 Concurrenc
local write wait 755,975 16.7 22 2.4 User I/O
log file switch (checkpoint in 33 11.6 352093 1.7 Configurat
direct path read temp 393,627 10.4 26 1.5 User I/O
Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
User I/O 54,820,587 470,058 9 67.4 5.4
DB CPU 105,636 15.2 1.2
Configuration 7,567 39,590 5232 5.7 0.5
System I/O 7,279,202 28,682 4 4.1 0.3
Concurrency 18,065,060 25,699 1 3.7 0.3
Cluster 47,148,125 16,839 0 2.4 0.2
Other 1,143,489,348 15,547 0 2.2 0.2
Application 188,463 3,535 19 .5 0.0
Network 63,875,516 205 0 .0 0.0
Commit 506,007 158 0 .0 0.0
Administrative 64 1 18 .0 0.0
Host CPU
~~~~~~~~ Load Average
CPUs Cores Sockets Begin End %User %System %WIO %Idle
----- ----- ------- --------- --------- --------- --------- --------- ---------
8 4 2 2.03 3.82 16.6 3.3 15.2 79.9
This comes from the AWR report (inst#2 only) for an off peak hour...
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
direct path read 224,253 15.2 68 37.6 User I/O
direct path write temp 23,357 9060 388 22.5 User I/O
DB CPU 4228 10.5
db file sequential read 203,412 3609 18 9.0 User I/O
db file scattered read 245,323 2675 11 6.6 User I/O
direct path read temp 38,124 2156 57 5.4 User I/O
local write wait 412 623. 1513 1.5 User I/O
enq: RO - fast object reuse 3,111 351. 113 .9 Applicatio
cursor: pin S wait on X 441 307. 697 .8 Concurrenc
db file parallel read 6,909 244. 35 .6 User I/O
Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
User I/O 776,627 33,834 44 84.0 9.4
DB CPU 4,229 10.5 1.2
System I/O 399,510 2,065 5 5.1 0.6
Other 47,688,419 453 0 1.1 0.1
Application 4,365 396 91 1.0 0.1
Concurrency 379,174 385 1 1.0 0.1
Cluster 1,044,871 172 0 .4 0.0
Network 5,060,324 35 0 .1 0.0
Commit 163,849 29 0 .1 0.0
Configuration 378 2 4 .0 0.0
Administrative 1 0 16 .0 0.0
Host CPU
~~~~~~~~ Load Average
CPUs Cores Sockets Begin End %User %System %WIO %Idle
----- ----- ------- --------- --------- --------- --------- --------- ---------
8 4 2 9.05 4.39 16.2 3.3 34.7 80.2
This comes from the AWR report (inst#2 only) for one of the busiest hour of this day...
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
db file sequential read 13,613,066 44.9 3 63.2 User I/O
direct path read 317,884 12.9 41 18.2 User I/O
DB CPU 6584 9.3
db file scattered read 49,278 1269 26 1.8 User I/O
db file parallel read 36,580 1208 33 1.7 User I/O
direct path write temp 7,978 694. 87 1.0 User I/O
gc cr grant 2-way 6,372,860 428. 0 .6 Cluster
gc current block 2-way 2,498,088 268. 0 .4 Cluster
direct path read temp 14,485 258 18 .4 User I/O
latch free 3,280 208 63 .3 Other
Wait Classes by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg Avg
Total Wait Wait % DB Active
Wait Class Waits Time (sec) (ms) time Sessions
---------------- ---------------- ---------------- -------- ------ --------
User I/O 14,074,198 61,605 4 86.8 17.1
DB CPU 6,585 9.3 1.8
System I/O 764,748 1,700 2 2.4 0.5
Cluster 9,912,000 1,028 0 1.4 0.3
Other 74,595,416 708 0 1.0 0.2
Concurrency 2,431,932 293 0 .4 0.1
Application 3,364 80 24 .1 0.0
Network 7,035,885 21 0 .0 0.0
Commit 35,961 12 0 .0 0.0
Configuration 414 11 27 .0 0.0
Administrative 1 0 0 .0 0.0
Host CPU
~~~~~~~~ Load Average
CPUs Cores Sockets Begin End %User %System %WIO %Idle
----- ----- ------- --------- --------- --------- --------- --------- ---------
8 4 2 5.74 7.33 22.0 6.1 20.8 70.9
1) Are theses I/O too long?
2) If so, How would you go about improving this? What should the dba's/sysdamin check first?
3) How would you go about improving Direct path reads I/O? We do use parallel queries.
Thanks!