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