Re: IOPS vs Physica Block Reads statistics

From: Jinwen Zou <zjworacle_at_gmail.com>
Date: Thu, 31 Jul 2014 13:08:03 +1000
Message-ID: <CAOEQsPJsmJtVfhJMP0USBHb8yk1Cq_ta-ntPKU76funDL8HOjA_at_mail.gmail.com>

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_at_gmail.com
> 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_at_gmail.com
> Life is Good!
>
>
>
>
>
> On Jul 29, 2014, at 7:57, Sidney <huanshengchen_at_gmail.com> wrote:
>
> is it due to consistent read building? check the undo related statistics.
>
> --
> Sidney
>
> 在 2014年7月29日，下午2:38，Ls Cheng <exriscer_at_gmail.com> 写道：
>
> 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_at_gmail.com> 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_at_gmail.com>:
>>
>> 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_at_gmail.com> 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_at_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
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Phone: +86 18666668061
>>>> Email & Gtalk: ylouis83_at_gmail.com
>>>> Personal Blog: http://www.vmcd.org
>>>>
>>>
>>>
>>
>>
>> --
>> Phone: +86 18666668061
>> Email & Gtalk: ylouis83_at_gmail.com
>> Personal Blog: http://www.vmcd.org
>>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 31 2014 - 05:08:03 CEST