This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Feb 2, 2013 11:13 AM by Dude! RSS

Oracle direct path read IO size

Kirill.Boyko Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    OK, it means that Linux doing some "optmization". How to trace and to control this?
  • 9. Re: Oracle direct path read IO size
    JohnWatson Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    It could be something in your Linux Kernel or Drivers.



    Hemant K Chitale
  • 13. Re: Oracle direct path read IO size
    Dude! Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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