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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 22 Jan 2020 15:28:04 +0000
Message-ID: <LNXP265MB15626F66841FCD2A88DD2DE5A50C0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>

I think I've seen the anomaly once before - 128 block read requests, but walking through the file one block at a time. I'm fairly sure it was an older version of Oracle and it may have been something to do with sorting for a window (over()) function . I'll see if I can find it in my library.

Which version of Oracle are you using here ? Is the TEMPFILE defined a bigfile tablespace or small file, and do you really have db_files = 4096 in the parameter file ?

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cary Millsap <cary.millsap_at_method-r.com> Sent: 22 January 2020 15:21
To: Chris Taylor
Cc: ORACLE-L
Subject: 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 nth 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<mailto: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<mailto: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<mailto: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

Received on Wed Jan 22 2020 - 16:28:04 CET