Re: SMON "trick" to force cleanup of extent honeycombs/extent fragmentation?

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_at_gmail.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l