12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Tue, 21 Jan 2020 18:58:55 -0500
Message-ID: <CAP79kiQwzcSLRmshy0392VJCYUOAryquSUyMquUB-OGDBk1xFA_at_mail.gmail.com>
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.
Date: Tue, 21 Jan 2020 18:58:55 -0500
Message-ID: <CAP79kiQwzcSLRmshy0392VJCYUOAryquSUyMquUB-OGDBk1xFA_at_mail.gmail.com>
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-lReceived on Wed Jan 22 2020 - 00:58:55 CET