I tried to emulate this on a 12.1 single-instance system, but the log file
and instanace stats didn't indicate any significant need for undo - though
obviously the redo log showed all the space allocation (file header updates
etc,( being unwound which MIGHT have been looking at undo biocks without
reporting it correctly. Maybe there's also something about extents being
detached from segment headers that doesn't get reported correctly in
session stats (but again) appears in the redo log.
The DFS lock handle waits will show you what type of enqueue they're
waiting on if you convert the p1 parameter to hex then take the top 4
digits and read them as ASCII values: it;s fairly likely to be mainly TT
(0x5454), TX (0x5458) or HW (0x4857).
I'd be interested in seeing the ASH spreadsheets if you want to email them
to me.
Regards
Jonathan Lewis
On Thu, 8 Apr 2021 at 22:53, Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
wrote:
Ok I isolated the SMON session and its correlated PX processes from
dba_hist_active_Sess_history. There are no SQL_IDs for any of them (which
is fine).
EVENTS are primarily "wait for a undo record" (tons of these) and some
cluster waits and a ton of "DFS Lock Handle" waits and quite a few NULL
Events with a file# and block#.
Interesting Factoid that may interest only me:
I isolated the time to the exact time the SMON kicked off the parallel
sessions indicating the cleanup start/stop times: (NOTE: I've saved these
results to an excel sheet if anyone is interested in taking a look)
- SMON owned 60 parallel sessions per GV$PX_SESSION at the time
Start: 3/30/2021 9:11:18.593 AM
End: 3/30/2021 10:17:06.858 AM
*1 Hour and 6 minutes to cleanup.*
Index rebuild into wrong tablespace that failed (and triggered the SMON
cleanup)
alter index ehr.REV_CODE_SRC_ID_TABLEOID_IDX rebuild partition
REV_CODE_HOSP_1042 tablespace ehr_data online parallel 8
Start: 3/30/2021 8:51:55.134 AM
End/Fail: 3/30/2021 8:54:26.952 AM
*Index rebuild ran for **3 minutes* *and* *then failed*
So, it took 1 hours 6 minutes to cleanup an online index partition rebuild
that only ran for 3 minutes?
Chris
