title: Oracle 11g high IO Wait
body: <p><code>Oracle Version: 11.1.0.7.0</code></p>
  
  <p>We are having higher IO Wait in one of our Oracle RAC instance</p>
  
  <p>One SQL is having high elapsedtime by execution - 1452.57s per execution. This started happening suddenly one day. Previously, it was taking max 3-4 min to query 20k(:v4 parameter) records</p>
  
  <p>subscribeinfo records: 59 million (non - parallel)</p>
  
  <p>chargerate records : 2k - 3k</p>
  
  <p>The SQL is below</p>
  
  <blockquote>
    <p>select o.msisdn, o.spid, o.serviceid , o.ChargeReferenceID,
    o.channelID, o.nextchargetime , o.failtimestamp, o.lastmonfeeday,
    o.networkId, o.retryEndDateTime, o.trialType, o.subFlag, o.faultCode
    from subscribeinfo o, chargerate r where (o.monthbillid = :v1) and
    (((o.state = :"SYS_B_00") and (o.nextchargetime &lt; :v2) and
    ((o.IsAutoExtend &lt;> :"SYS_B_01") or ((o.IsAutoExtend = :"SYS_B_02")
    and (o.extendflag &lt;> :"SYS_B_03")))) or (o.subFlag = :"SYS_B_04" and
    o.state = :"SYS_B_05" and o.retryenddatetime > :v2)) and
    (o.ChargeClassForSub = r.chargeclassidx) and ((r.chargemode =
    :"SYS_B_06" and r.activetype = :"SYS_B_07" and o.nextchargetime !=
    :"SYS_B_08" ) or ( r.chargemode = :"SYS_B_09" and r.activetype &lt;>
    :"SYS_B_10") or (r.chargemode >= :"SYS_B_11" and r.chargemode &lt;=
    :"SYS_B_12" and r.basecharge >= :"SYS_B_13") or (r.chargemode =
    :"SYS_B_14") or (r.chargemode = :"SYS_B_15") or (r.chargemode =
    :"SYS_B_16") ) and (o.failtimestamp &lt;= :v3) and (rownum &lt;= :v4)</p>
  </blockquote>
  
  <p>According to AWR report Top 5 Timed Foreground Events</p>
  
  <p>Direct path read [ Avg Wait Time: 22 s, %DB Time: 50.75% ]
  DB file sequential read [ Avg Wait Time: 15 s, %DB Time: 38.00 ]</p>
  
  <p>I will not be able to post full AWR report, because it is restricted. So please ask details I'll post</p>
  
  <p>Please find the explain plan below :</p>
  
  <blockquote>
    <p>ID    Exec Ord    Operation   Go To   More    Peek Bind   Capt Bind   Cost2   Estim
    Card  LAST Starts LAST Output Rows    LAST Over/Under
    Estimate1 PStart  PStop   Work Area 0 7   SELECT STATEMENT<br>
      23335       1   2577                  1 6    COUNT STOPKEY       [+]     [+]<br>
    [+]   23335       1   2577                  2 5   . HASH JOIN      [+]     [+]<br>
    [+]   23335   20001   1   2577    8x over          [+] 3  1   .. TABLE ACCESS FULL
    CHARGERATE     [+]     [+]            68  3035    1   3036    1x            4 4   .. PARTITION LIST
    SINGLE         [+]            23266   25223   1   2577    10x over    KEY KEY   5 3   ... TABLE
    ACCESS BY LOCAL INDEX ROWID SUBSCRIBEINFO  [+]     [+]     [+]<br>
    [+]   23266   25223   1   2577    10x over    KEY KEY   6 2   .... INDEX RANGE SCAN
    IDX_FAILTIMESTAMP_NEW  [+]     [+]     [+]     [+]    2435        1   2100765     KEY KEY</p>
  </blockquote>
  
  <p>IOSTAT</p>
  
  <blockquote>
    <p>Linux 2.6.16.46-0.12-smp (mdspdb01)     11/16/12</p>
    
    <p>avg-cpu:  %user   %nice %system %iowait  %steal   %idle</p>
  
  <pre><code>       8.41    0.00    9.38   13.25    0.00   67.67
  </code></pre>
    
    <p>Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn</p>
    
    <p>sda               5.71        39.53       121.79  665679995 2051190222</p>
    
    <p>sdb              85.75       178.15       171.12 3000316741 2881953582</p>
    
    <p>sdc             111.05       161.69        43.96 2723201251  740429949</p>
  </blockquote>
  
  <hr>
  
  <p>We created an index for the fields monthbillid, nextchargetime and failtimestamp... Eventhough it improved a lot in cardinality by 1/6th, it increased the cost by 4-5 times. But oracle takes the new index by default</p>
  
  <blockquote>
    <p>create index IDX_MONTHBILLQUERY on subscribeinfo(monthbillid,
    nextchargetime, failtimestamp) local tablespace IMUSE_INDEX;</p>
    
    <p>dbms_stats.gather_index_stats('IMUSE01', 'IDX_MONTHBILLQUERY');</p>
  </blockquote>
  
  <p>We have hard parses = 0 in AWR reports. And also we changed the cursor_sharing = FORCE</p>
  
  <p>Now IO is under control. Still feels, this not the root cause. And also, we made the instance dedicate for this query which happens more than 10 times an hour also, it takes approximately 100 seconds to retrieve 20k records. </p>
  
  <p>Can anyone suggest whether it is a good decision if I go for optimizer mode as first_rows or use an hint first_rows(20000).</p>
  
  <p>As of now, we have disbled stats job, can we enable the samething only for some tables or some indexes. Is this possible?</p>