0 Replies Latest reply: Feb 1, 2013 3:04 AM by Kirill.Boyko RSS

    Oracle DB direct path read IO size

    Kirill.Boyko
      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