1 Reply Latest reply: Sep 5, 2012 8:54 AM by kuljeet singh - RSS

    ash and dba_hist_active_

    925887
      Hi,

      Version 10.2.0.1 and 11.2.0.1
      Os : Aix

      We executed followings

      SQL> select
      sh.snap_id ,count(sh.session_id)
      from
      dba_hist_active_sess_history sh,dba_hist_snapshot sp
      where
      trunc(sp.BEGIN_INTERVAL_TIME)=to_date('05-09-12','dd-mm-yy') and sh.snap_id =sp.snap_id
      group by
      sh.snap_id
      order by 1 ;

      SNAP_ID COUNT(SH.SESSION_ID)
      ---------- --------------------
      7571 80
      7572 569
      7573 547
      7574 629
      7575 85
      7576 288
      7577 6516
      7578 3858
      7579 3376
      7580 3361
      7581 3160

      SNAP_ID COUNT(SH.SESSION_ID)
      ---------- --------------------
      7582 3514
      *7583* *3851*

      13 rows selected.

      I06-SQL> select count(*) from v$session where type='USER';

      COUNT()*
      ----------
      *385*

      when i executed second query t,otal sessions in the db is 385 last one hour.
      when check ,total no of session of snap_id(7583) is showing 3851.7583 is taken just two minute difference between the first query and snapshot taken (7583)

      why the difference between first query output and second query output?


      1,Is MMNL populate to dba_hist_active_sess_history only active sessions and including background process?

      Any other suggestion


      Thanks
        • 1. Re: ash and dba_hist_active_
          kuljeet singh -
          when check ,total no of session of snap_id(7583) is showing 3851.
          Since ash collecting and storing the data of active user so one session could be active for few mins thats why it showing high value due to repeat session_id value.


          you'r query showing below detail on my db
          so better generate ash report to get actual no of session detail inplace of this query



          SNAP_ID| COUNT(SH.SESSION_ID)
          ---------------| --------------------
          30490| 5882
          30491| 6844
          30492| 5990
          30493| 6060
          30494| 6150
          30495| 6224
          30496| 4716
          30497| 6000
          30498| 7368
          30499| 15498
          30500| 20226
          30501| 22774
          30502| 25030
          30503| 13724
          30504| 12768
          30505| 14500
          30506| 18374
          30507| 16058

          18 rows selected.

          select count(*) from v$session;

          COUNT(*)
          ---------------
          539

          1 row selected.