Ok I managed to forget which events you saw as I read through the thread :-)
There are also V$SYSSTAT/V$SESSTAT metrics related to ASSM segment fixing:
SQL> @sys ASSM%fix
NAME
VALUE
----------------------------------------------------------------
--------------------------
ASSM bg: segment fix monitor
1845
ASSM fg: submit segment fix task
0
ASSM bg:mark segment for fix
0
ASSM bg:create segment fix task
0
ASSM bg:slave fix one segment
0
ASSM bg:slave fix state
0
You could look into AWR and check if you have any unusual numbers there -
or run Snapper on the Wnnn processes when the problem happens again.
Also, assuming that the Wnnn slaves haven't exited, you can just check the
current V$SESSTAT values (@ses2 shows any matching metrics with non-zero
values):
SQL> @ses2 "select sid from v$session where program like '%(W%'" ASSM%fix
SID NAME
VALUE
---------- ----------------------------------------------------------------
----------
124 ASSM bg: segment fix monitor
40
851 ASSM bg: segment fix monitor
41
1213 ASSM bg: segment fix monitor
41
1578 ASSM bg: segment fix monitor
42
...
Tanel
https://tanelpoder.com
On Wed, May 13, 2020 at 5:16 PM Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx>
wrote:
Thank you Jonathan and Tanel for your invaluable inputs – learned so much
from them.
Find my answers below:
- It isn’t CTAS – last_ddl_time is from long time ago.
- I haven’t seen any “buffer busy waits”. It was “free buffer
waits” instead – block class is not shown with event2 for them.
- I’ll setup the short stacks with wait events and wait for the
next occurrence.
Best regards,
Nenad
https://nenadnoveljic.com/blog
