3 Replies Latest reply: Jan 27, 2013 8:03 PM by Hemant K Chitale RSS

    processes and sys.aud$

    vuatsc
      Sometimes the number of processes of my server ( standard - 11gr2 ) peaks to the maximum number (500) for about 5-10 minutes then goes down.I would like to track what applications on the server contribute to this so I turn on the audit for session and connection. The command I use to figure out which applications are connecting to the server is as follows :
      select userid, CAST((FROM_TZ(ntimestamp# ,'+00:00') AT TIME ZONE 'US/Central') AS DATE) time#
              from sys.aud$
             where  returncode = 0 and      
             sessionid not in ( select sessionid from sys.aud$ where action# = 101) and  -- 101 is logoff
             CAST((FROM_TZ(ntimestamp# ,'+00:00') AT TIME ZONE 'US/Central') AS DATE) < sysdate - N/24
             order by time#
      with N is some constant to adjust the time up to that I want to track the open connections
      The idea is to find all the sessionids that do not have the logoff records.

      I believe the above command has some flaws because it returns a much smaller number (200) than the maximum number of processes (500), at the time that the processes got to the maximum numbers, but I am not sure what they are.

      Any advice is greatly appreciated.

      Thanks,
      Vu
        • 1. Re: processes and sys.aud$
          Rob_J
          Initial quick thoughts are:
          you can check the listener log to see where these connections are coming from?
          And look at v$active_session_history and dba_active_session_history to see what the sessions were doing, although this is sampled so might not show them all.
          If you have the enterprise manager you can look historically at what sessions were in the DB (probably this is based on the same views previously mentioned)
          • 2. Re: processes and sys.aud$
            vuatsc
            Rob_J wrote:
            Initial quick thoughts are:
            you can check the listener log to see where these connections are coming from?
            And look at v$active_session_history and dba_active_session_history to see what the sessions were doing, although this is sampled so might not show them all.
            If you have the enterprise manager you can look historically at what sessions were in the DB (probably this is based on the same views previously mentioned)
            My server version is Oracle Standard. It does not have any rows in for ASH.

            I also found the flaw in my SQL. It lacks comparing the time in the inside SQL that gets all the session ids for logoff.
            So the (closer to the) correct one is
            select userid, CAST((FROM_TZ(ntimestamp# ,'+00:00') AT TIME ZONE 'US/Central') AS DATE) time#
                    from sys.aud$
                   where  returncode = 0 and      
                   sessionid not in ( select sessionid from sys.aud$ where action# = 101
                                           and CAST((FROM_TZ(ntimestamp# ,'+00:00') AT TIME ZONE 'US/Central') AS DATE) < sysdate - N/24) -- << add this >>
                   and 
                   CAST((FROM_TZ(ntimestamp# ,'+00:00') AT TIME ZONE 'US/Central') AS DATE) < sysdate - N/24
                   order by time#
            The revised version shows a better result. There are some things that make the output from aud$ and v$ssession different such as the background processes are logged in the v$session but not in the aud$. I also found there are sessionids that exist in the aud$ but not in v$session.audsid.

            Could someone please explain why there are those open sessionids logged in AUD$ but not v$SESSION ? I use this command find those sessions
            select * from aud$ where sessionid not in ( select audsid from v$session )
                                     and sessionid not in ( select sessionid from aud$ where action# =101)
            Thanks,
            Vu
            • 3. Re: processes and sys.aud$
              Hemant K Chitale
              Could someone please explain why there are those open sessionids logged in AUD$ but not v$SESSION ?
              Abnormal disconnects / killed sessions --- which mean that SYS.AUD$ does not get updated on logout.


              Hemant K Chitale