Home -> Mailing Lists -> Oracle-L -> Re: reads by KTSJ

Re: reads by KTSJ

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Wed, 13 May 2020 17:34:07 -0400
Message-ID: <CAMHX9JL9QXE6wgGNDRDG9kk5sye+qVsNsJ-KqF9+oT0NwvyHdQ_at_mail.gmail.com> 








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> _at_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 (_at_ses2 shows any matching metrics with non-zero
values):



SQL> _at_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_at_vontobel.com>
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

>

>

>

>

>



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


Received on Wed May 13 2020 - 23:34:07 CEST












Original text of this message