Re: 12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)

So, there are at least *two* really weird things going on:

1. Why are we seeing evidence of 128-block reads happening upon adjacent blocks? (The *n*th read would have obtained the first 127 blocks of the ( *n* + 1)th read...)
2. If each of these reads really were a 1-block read, it would not be a *db file scattered read* call (which, to the best of my knowledge is *always* a multi-block read; otherwise, there'd be no blocks to scatter [into the SGA]). Yes, a *db file sequential read* can be a multiblock read (back in the old days), but I don't understand why a *db file scattered read* would ever be a single-block read.

I think it's time for *strace*!

Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>* and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition <https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*

On Wed, Jan 22, 2020 at 9:14 AM Cary Millsap <cary.millsap_at_method-r.com> wrote:

> Ah. Now I see the block# incrementing by one. (Thank you, Keith Moore, for
> spotting that.)
>
>
> Cary Millsap
> Method R Corporation
> Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>*
> and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition
> <https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*
>
>
>
> On Wed, Jan 22, 2020 at 8:41 AM Cary Millsap <cary.millsap_at_method-r.com>
> wrote:
>
>> Hi Chris,
>>
>> What in your trace file is giving you the idea that the process is doing
>> 1-block reads?
>>
>>
>> Cary Millsap
>> Method R Corporation
>> Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>*
>> and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition
>> <https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*
>>
>>
>>
>> On Tue, Jan 21, 2020 at 6:01 PM Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> So we use an ADG standby db for reporting and we've got some problem
>>> SQLs that I'm putzing around with because they work fine in all our other
>>> non-standby databases.
>>>
>>> Looking deeper into the tracefile, I see it doing 1 block at a time
>>> reads , even though it says its reading 128.
>>>
>>> It does this on both of our ADG Standby Databases which are on
>>> completely different hardware.
>>>
>>> WAIT #140196872952648: nam='db file scattered read' ela= 1588 file#=4097
>>> block#=579715946 blocks=128 obj#=-39778567 tim=17263910670242
>>> WAIT #140196872952648: nam='db file scattered read' ela= 1495 file#=4097
>>> block#=579715947 blocks=128 obj#=-39778567 tim=17263910672065
>>> WAIT #140196872952648: nam='db file scattered read' ela= 1671 file#=4097
>>> block#=579715948 blocks=128 obj#=-39778567 tim=17263910674042
>>> WAIT #140196872952648: nam='db file scattered read' ela= 1094 file#=4097
>>> block#=579715949 blocks=128 obj#=-39778567 tim=17263910675443
>>>
>>> File# in this case is the TEMP Segment from V$SORT_SEGMENT.
>>>
>>> If I check V$SORT_SEGMENT, I don't see any object that has that many
>>> blocks.
>>>
>>> I'm considering dropping & recreating the TEMPFILE in the two standby
>>> databases to see if that will resolve the issue.
>>>
>>> Anyone seen anything like that before?
>>>
>>> Chris
>>>
>>>

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