3 Replies Latest reply: Feb 12, 2013 7:31 AM by moreajays RSS

    ASM I/O wait times

    user11977218
      Hello ,

      Please suggest , acording to the query in 10.2.0.5v. :

      User I/O :

      select to_char(w.begin_time,'YYYY.MM.DD HH24:MI:SS') laikas,
      ROUND(10*w.time_waited/DECODE(w.wait_count,0,1,w.wait_count),2) "AVG_IO_MS", w.wait_count
      FROM v$WAITCLASSMETRIC_HISTORY w,V$SYSTEM_WAIT_CLASS c
      where w.wait_class_id=c.wait_class_id and c.wait_class='User I/O'
      order by w.begin_time,c.wait_class
      ;

      , averaged by 1 min. i am getting the random spikes :

      TIME AVG_IO_MS WAIT_COUNT

      2013.02.12 12:15:49 5,1 220
      2013.02.12 12:16:49 7,03 79
      2013.02.12 12:17:48 5,17 354
      2013.02.12 12:18:48 86,58 320
      2013.02.12 12:19:49 4,2 305
      2013.02.12 12:20:49 6,66 192
      2013.02.12 12:21:49 2,72 904
      2013.02.12 12:22:49 5 539
      2013.02.12 12:23:48 6,17 271
      2013.02.12 12:24:48 2,62 786
      2013.02.12 12:25:49 5,74 356
      2013.02.12 12:26:49 6,95 180
      2013.02.12 12:27:49 4,42 508
      2013.02.12 12:28:49 7,02 292
      2013.02.12 12:29:48 3,49 222
      2013.02.12 12:30:48 3,47 2226
      2013.02.12 12:31:49 95,92 841
      2013.02.12 12:32:49 5,08 1304
      2013.02.12 12:33:49 5,85 771
      2013.02.12 12:34:48 5,5 369
      2013.02.12 12:35:48 332,82 307
      2013.02.12 12:36:48 4,66 354

      Can i state from this query , that there is SAN Disk I/O problem .
      Because SAN sysadm. states that this is O.K .

      Thank's

      Edited by: user11977218 on Feb 12, 2013 12:41 PM

      Edited by: user11977218 on Feb 12, 2013 12:42 PM

      Edited by: user11977218 on Feb 12, 2013 12:48 PM
        • 1. Re: ASM I/O wait times
          moreajays
          Hi,

          Raising concerns against storage need strong evidence
          I would suggest take help of the AWR or stats-pack report
          Check if query any query is suffering/waiting on read/write db/tempfile ,Run any query manually (with huge row count) & track response time get AWR/statspack snap else mine existing Workload
          Starting with TOP 5 wait events , drill down further from Wait Events Statistics > IO Stats to get IO wait event
          Refer Grid Control graphs or any other third party System Health Monitoring tools

          Thanks,
          Ajay More
          http://www.moreajays.com
          • 2. Re: ASM I/O wait times
            user11977218
            But there is a concrete I/O time :

            2013.02.12 12:35:48 332,82 307


            Avg I/O wait time in one minute is : 332,82 ms .
            And acording to this time is impossible to say that there is or there is no Problems With SAN ?

            Edited by: user11977218 on Feb 12, 2013 3:05 PM

            In AWR :

            Top 5 Timed Events

            db file sequential read : Avg Wait(ms) : 33

            Acording to this it is impossible to say if SAN I/O is working well/bad or unknown ?

            Edited by: user11977218 on Feb 12, 2013 3:05 PM

            Acording me this time is bad , because time must be <20 ms .
            O i am wrong ?

            Edited by: user11977218 on Feb 12, 2013 3:11 PM

            Edited by: user11977218 on Feb 12, 2013 3:24 PM
            • 3. Re: ASM I/O wait times
              moreajays
              Hi,

              The best way to identify IO slowness is to run a long running/top IO consuming query reported in AWR or by your application/user
              Get the 10046 trace of the session & generate tkprof and review for waits.
              Get the storage type (High/low end, RAID level , write/read intensive) if possible from Storage Admin & ask if any other DB's deployed on same storage & compare the reports for metrics between two DB's.

              Thanks,
              Ajay More
              http://www.moreajays.com