1 2 Previous Next 24 Replies Latest reply: Feb 2, 2013 1:13 PM by Catch_22 RSS

    Oracle direct path read IO size

    Kirill.Boyko
      Hello!

      I am confused a little with IO size. 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

      As a test I am running a simple query against large table with 1.5Bln rows.
      select /*+ PARALLEL (STOCK_DAY 10) */ count(*) from stock_day where cstast='NA'

      STAT #140582923121480 id=1 cnt=0 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
      STAT #140582923121480 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
      STAT #140582923121480 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)'
      STAT #140582923121480 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=27 us)'
      STAT #140582923121480 id=5 cnt=2301843 pid=4 pos=1 obj=0 op='PX PARTITION LIST ALL PARTITION: 1 762 (cr=68020 pr=68393 pw=0 time=29346414 us cost=303766 size=669864369 card=223288123)'
      STAT #140582923121480 id=6 cnt=2301843 pid=5 pos=1 obj=1464816 op='TABLE ACCESS FULL STOCK_DAY PARTITION: 1 762 (cr=68020 pr=68393 pw=0 time=24376609 us cost=303766 size=669864369 card=223288123)'


      when I am trying to measure disk io statistics with iostat it shows that Oracle is issuing a 32K direct path read requests to ASM LUNs. Why? Why not 1M?


      Thank you in advance!

      Regards,
      Kirill

      Edited by: Kirill.Boyko on Jan 31, 2013 12:53 PM
        • 1. Re: Oracle direct path read IO size
          sb92075
          Oracle NEVER issues any I/O request directly.
          Oracle passes every I/O request to the OS for completion.
          The Oracle blocksize is strictly a metadata construct, & does NOT directly affect OS I/O functionality.
          • 2. Re: Oracle direct path read IO size
            Hemant K Chitale
            If you can capture the 'direct path read' wait event from V$SESSION_WAIT, look at P3 which would show the number of blocks.

            I think that you wouldn't always see 1MB reads.


            Hemant K Chitale
            • 3. Re: Oracle direct path read IO size
              Kirill.Boyko
              This is a kind of histogram for one parallel session wait events
              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

              in fact as you can see almost all direct path reads are done with io size > 900K. Total size of information read by process~160M

              strace shows a lot of calls to read function. If we divide 160M/78000 reads we will get about 2K per request which is strange

              % time seconds usecs/call calls errors syscall
              ------ ----------- ----------- --------- --------- ----------------
              98.61 0.212144 3 78356 read
              0.93 0.002000 32 63 58 semtimedop
              0.29 0.000624 208 3 munmap
              0.12 0.000263 0 7407 gettimeofday

              according to iostat we see that we are reading from each LUN with requests of 32K

              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

              Why linux is splitting Oracle requests and how to avoid that?

              Regards,
              Kirill
              • 4. Re: Oracle direct path read IO size
                JohnWatson
                I believe it is important to align your segment extent boundaries with your file extent boundaries, because you will not get a multiblock read with blocks from two extents. Are you using uniform extent sizes? Set to a multiple of your allocation unit size?
                • 5. Re: Oracle direct path read IO size
                  Hemant K Chitale
                  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
                  Of the 172 occurrences 97 are for 1MB (128 blocks of 8KB). 41 are for 124Blocks or more.


                  Hemant K Chitale
                  • 6. Re: Oracle direct path read IO size
                    Kirill.Boyko
                    You are right. It means 133 requests > 126 blocks of 8K and 24 requests > 100 blocks of 8K
                    So two questions:
                    1. why IOSTAT constantly shows reads of ~ 32K for all parallel processes
                    2. why strace says that read function was called 78000 times for this particular parallel process?
                    • 7. Re: Oracle direct path read IO size
                      Kirill.Boyko
                      Concerning extent management

                      SELECT blocks, count(*)
                      FROM dba_extents
                      WHERE segment_name LIKE 'STOCK_DAY%' group by blocks
                      order by blocks

                      BLOCKS COUNT(*)
                      --------------------------------------- ---------------------------------------
                      8 5977
                      128 23716
                      256 63
                      384 39
                      512 20
                      640 13
                      768 13
                      896 9
                      1024 13652

                      To my knowledge it means that 90% of data is stored in 8M extents. AU size = 1M, I suppose database should care about alligning data inside dbfiles according to AU. What I can tell is that our disk partitions used by ASM are alligned to 1M.
                      In fact this problem is not really related with particular multiblock_read problem. RMAN is doing exactly same mess with backup validate. It is reading with thousands of small 32K requests.
                      • 8. Re: Oracle direct path read IO size
                        Kirill.Boyko
                        OK, it means that Linux doing some "optmization". How to trace and to control this?
                        • 9. Re: Oracle direct path read IO size
                          JohnWatson
                          Kirill.Boyko wrote:
                          SELECT blocks, count(*)
                          FROM dba_extents
                          WHERE segment_name LIKE 'STOCK_DAY%' group by blocks
                          order by blocks

                          BLOCKS COUNT(*)
                          --------------------------------------- ---------------------------------------
                          8 5977
                          128 23716
                          256 63
                          384 39
                          512 20
                          640 13
                          768 13
                          896 9
                          1024 13652
                          Your segment extent sizes are all over the place. I cannot comment on why you are are seeing 32K read requests at the OS level, but certainly the segment extent sizes will impact on the multiblock reads. You have nearly 6000 64K segment extents: these will not be read with a 128block multiblock read. Furthermore, depending on how the small segment extents are distributed thtough the file, they probably mean that the larger 8M segment extents do NOT align with the file extent boundaries, or even the allocation unit boundaries.
                          I am of course open to correction on this, but I believe that to optimize ASM I/O it is important to use a uniform extent size.
                          • 10. Re: Oracle direct path read IO size
                            Kirill.Boyko
                            When you say "uniform extent size" you mean that all extent size should be a multiple of 1AU? How could I be sure that my extents "align with the file extent boundaries, or even the allocation unit boundaries"?
                            Concerning 6000 64K segment extents - I completely agree but taking into account that each second Oracle is issuing about 10000 32K IOs it should swallow these 6000 extents in 1 second :) THis query runs for a few minutes and during all the time IOSTAT shows same figures. Again, this of course strictly my understanding of things.

                            I would correct myself, Linux on behalf of Oracle each second issuing more than 17000 32K IOs

                            Edited by: Kirill.Boyko on Feb 1, 2013 12:41 AM

                            Edited by: Kirill.Boyko on Feb 1, 2013 12:42 AM
                            • 11. Re: Oracle direct path read IO size
                              JohnWatson
                              Kirill.Boyko wrote:
                              When you say "uniform extent size" you mean that all extent size should be a multiple of 1AU? How could I be sure that my extents "align with the file extent boundaries, or even the allocation unit boundaries"?
                              I describe the technique here,
                              http://skillbuilders.com/webinars/webinar.cfm/73?title=How%20to%20tune%20your%20disc%20I/O:%20let%20ASM%20do%20it%20for%20you
                              • 12. Re: Oracle direct path read IO size
                                Hemant K Chitale
                                It could be something in your Linux Kernel or Drivers.



                                Hemant K Chitale
                                • 13. Re: Oracle direct path read IO size
                                  Catch_22
                                  iostat shows that average IO is 32K while reading from a large table.
                                  Please show what command you used. Also, how do you know that data is not in memory?
                                  • 14. Re: Oracle direct path read IO size
                                    Kirill.Boyko
                                    Hi Dude!

                                    iostat -mx 1

                                    What do you mean saying: "do you know that data is not in memory?" Which memory should I check?
                                    1 2 Previous Next