11 Replies Latest reply on Jul 20, 2015 11:53 AM by Mohamed Houri

    Why populating ASH (v$active_session_history) is delayed?

    Mohamed Houri

      Dears,

       

      I have one situation which is tormenting me; it occurred this couple of months at least into two different running production databases. The last one happens today. Look below:

       

      SQL> select * from v$version ;

       

      BANNER

      --------------------------------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      PL/SQL Release 11.2.0.4.0 - Production

      CORE    11.2.0.4.0      Production

      TNS for Linux: Version 11.2.0.4.0 - Production

      NLSRTL Version 11.2.0.4.0 - Production

       

       

      I’ve executed the following query againts gv$active_session_history at 8h20

       

      select event, count(1)

      from gv$active_session_history

      where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')

      and     to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')

      group by event

      order by 2 desc;

       

      • No rows selected

       

      Then re-executed the same query at 8h30

      select event, count(1)

      from gv$active_session_history

      where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')

      and     to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')

      group by event

      order by 2 desc;

       

      • No rows selected

       

      Then re-executed the same query at 9h30

       

      select event, count(1)

      from gv$active_session_history

      where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')

      and     to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')

      group by event

      order by 2 desc;

       

      • No rows selected

       

      And starting from 10h30 the same query begins returning data

       

      select event, count(1)

      from gv$active_session_history

      where sample_time between to_date('15072015 07:30:00', 'ddmmyyyy hh24:mi:ss')

      and     to_date('15072015 09:30:00', 'ddmmyyyy hh24:mi:ss')

      group by event

      order by 2 desc;

       

      EVENT                            COUNT(1)

      -------------------------------- ----------

                                        2209

      control file parallel write       444

      db file sequential read           249

      log file parallel write           150

      db file scattered read            148

      Backup: MML create a backup piece 108

      Backup: MML write backup piece    101

      control file sequential read      64

      db file parallel write            56

       

      What can impeach MNON to write in v$active_session_history for more than an hour?

       

      Thanks

       

      Mohamed Houri