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

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