Re: IOPS vs Physica Block Reads statistics

From: Jinwen Zou <zjworacle_at_gmail.com>
Date: Thu, 31 Jul 2014 13:10:51 +1000
Message-ID: <CAOEQsPKKTW=_tpNDDgV768h7SntEO4h81qHfmMhkDjjatT+-tA_at_mail.gmail.com>



Sorry, I mean the 'physical read total IO requests' should be LOWER than physical reads(blocks read) in most of sessions.

On Thu, Jul 31, 2014 at 1:08 PM, Jinwen Zou <zjworacle_at_gmail.com> wrote:

> 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:10:51 CEST

Original text of this message