IOPS vs Physica Block Reads statistics

From: Ls Cheng <exriscer_at_gmail.com>
Date: Mon, 28 Jul 2014 12:29:27 +0200
Message-ID: <CAJ2-Qb8EHKeqnJfzo_g9-W_rFZbY77pHsP-s5jwbB_vfpB8BcQ_at_mail.gmail.com>



Hi All

I am monitoring a Siebel Database and noticed that some sessions have much higher physical read total IO requests than physical reads, it i rare because in Siebel all queries are indexed therefore data are accessed using index range scans, in this case physical read total IO requests should equal to physical reads. In fact it is true for most sessions except a few with long running queries (over 2, 3 hours) which shows this behaviour:

select sid, iops, preads, iops - preads diff_iops   from (select sid,

               max(case
                   when name = 'physical read total IO requests' then value
                   else null
               end) IOPS,
               max(case
                   when name = 'physical reads' then value
                   else null
               end) PREADS
          from (select a.sid, name, value
                  from v$sesstat a, v$statname b, v$session c
                 where a.statistic# = b.statistic#
                   and a.sid = c.sid
                   and b.name in ('physical read total IO requests',
'physical reads')
                   and c.username = 'LDAPUSER'
               )
        group by sid)

where IOPS != PREADS
order by 1;

   SID IOPS PREADS DIFF_IOPS

------ ---------- ---------- ----------
   481      21961      21965         -4
   514      16797      16801         -4
   542      54678      54719        -41
   550       9669       9673         -4
   552       2672       2673         -1
   561       4945       4949         -4
   588      77506      77614       -108
   591       7963       7967         -4
   595      25758      25783        -25
   616       2907       2911         -4
   653      13490      13494         -4
*   672    1682079    1792498    -110419*
   689     208179     208199        -20
   711       4132       4136         -4

Session 672 has a long running query, around 7200 seconds already.

Anyone know what ca cause such behaviour? It is running Siebel 7.8 in Oracle 10.2.0.3 in AIX.

Thanks

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jul 28 2014 - 12:29:27 CEST

Original text of this message