The 'physical read total IO requests' counts includes single blolck request and multiblock read request that chould bring in more than 1 block, it should be larger than physical reads(blocks read) in most of sessions, your query just confirmed this. I guess the long running session could have done some mutliblock reads before the session's last 2 queries(I assume you have checked that the last 2 queries dont have multiblock reads). -- Jinwen On Tue, Jul 29, 2014 at 10:39 PM, Carlos Sierra <carlos.sierra.usa@xxxxxxxxx > wrote: > Not sure if your goal is to understand those physical reads statistics or > actually find the root cause of your SQL performing poorly. If the latter, > then you may want to use SQLTXPLAIN (215187.1), SQLHC (1366133.1) or at the > very least the set of scripts attached. Then focus where the time is spent > and consider tuning your SQL. Most probably you have a suboptimal Execution > Plan. > > > Carlos Sierra > carlos.sierra.usa@xxxxxxxxx > Life is Good! > > > > > > On Jul 29, 2014, at 7:57, Sidney <huanshengchen@xxxxxxxxx> wrote: > > is it due to consistent read building? check the undo related statistics. > > -- > Sidney > > 在 2014年7月29日,下午2:38,Ls Cheng <exriscer@xxxxxxxxx> 写道: > > Hi > > I am talking abut IOPS is higher than the number of blocks read. > > It looks strange because usually to read a 8KB block 1 I/O should be > enough but it seems that it requieres many IOPS to read a block and that is > why my confusion. > > > Regards > > > > On Tue, Jul 29, 2014 at 7:48 AM, louis liu <ylouis83@xxxxxxxxx> wrote: > >> OK but you can't actually know what's oracle doing while long time >> running >> >> physical reads >> >> Total number of data blocks read from disk. This value can be greater >> than the value of "physical reads direct" plus "physical reads cache" as >> reads into process private buffers also included in this statistic. >> >> >> 2014-07-29 13:34 GMT+08:00 Ls Cheng <exriscer@xxxxxxxxx>: >> >> Hi >>> >>> Can you try a index range scan in a session (no scatter reads o direct >>> path reads) and check for your session statistics? >>> >>> In fact if you look my query output you can see most session have >>> similar valur for both statistics. >>> >>> >>> Thanks! >>> >>> >>> On Tue, Jul 29, 2014 at 3:29 AM, louis liu <ylouis83@xxxxxxxxx> wrote: >>> >>>> I checked these two statistics and I don't think physical read total >>>> IO requests should equal to physical reads in your case >>>> >>>> >>>> 2014-07-28 18:29 GMT+08:00 Ls Cheng <exriscer@xxxxxxxxx>: >>>> >>>> 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 >>>>> >>>>> >>>> >>>> >>>> -- >>>> Phone: +86 18666668061 >>>> Email & Gtalk: ylouis83@xxxxxxxxx >>>> Personal Blog: http://www.vmcd.org >>>> >>> >>> >> >> >> -- >> Phone: +86 18666668061 >> Email & Gtalk: ylouis83@xxxxxxxxx >> Personal Blog: http://www.vmcd.org >> > > > >