This discussion is archived
0 Replies Latest reply: Feb 1, 2013 1:04 AM by Kirill.Boyko RSS

Oracle DB direct path read IO size

Kirill.Boyko Newbie
Currently Being Moderated
Hello!

I switching to this forum with greatings from this thread :) Re: Oracle direct path read IO size
I am confused a little with IO size my database is doing.
Does anybody have any idea about what is Linux doing to optimize oracle direct path read requests?

I am running 11.2.0.3 on Oracle Linux x64 6.2. Block_size=8K, MULTIBOCK_READ_COUNT=128
Database is Single Instance and is using ASM grid. ASM AU =1M

iostat shows that average IO is 32K while reading from a large table.

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
xvda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
xvdb 4.00 0.00 2946.00 1.00 92.01 0.00 63.94 90.60 30.93 0.33 96.00
xvde 1.00 0.00 2466.00 0.00 77.00 0.00 63.95 49.84 20.64 0.33 80.90
xvdf 5.00 0.00 2694.00 1.00 84.15 0.01 63.95 69.68 25.37 0.32 87.20
xvdg 2.00 0.00 2798.00 0.00 87.41 0.00 63.98 91.95 33.81 0.35 97.40
xvdj 3.00 0.00 2676.00 1.00 83.45 0.03 63.87 38.83 14.72 0.31 82.10
xvdk 4.00 0.00 2951.00 0.00 92.14 0.00 63.95 100.21 32.42 0.31 91.00
xvdl 3.00 0.00 2735.00 1.00 85.45 0.03 63.98 56.04 21.14 0.32 86.50


In fact most of Oracle requests are large than 800K

select session_state,event,p3,count(1) from V$active_session_history ash where ash.session_id=1904 and ash.session_serial#=24381
group by session_state,event,p3
order by session_state,event,p3

session
state event P3 COUNT
ON CPU     5     1
ON CPU     9     8
ON CPU     15     1
ON CPU     115     10
ON CPU     123     1
ON CPU     124     2
ON CPU     126     23
ON CPU     128     77
ON CPU     512     1
WAITING     direct path read     5     2
WAITING     direct path read     8     2
WAITING     direct path read     9     7
WAITING     direct path read     15     3
WAITING     direct path read     67     1
WAITING     direct path read     101     1
WAITING     direct path read     115     18
WAITING     direct path read     124     5
WAITING     direct path read     126     35
WAITING     direct path read     127     1
WAITING     direct path read     128     97

How could I diagnose that?
Just to be clear. Oracle Linux 6.2 is running on Oracle VM 3.1.1

Regards,
Kirill

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points