4 Replies Latest reply: Nov 21, 2012 12:12 PM by 889367 RSS

    dba_audit_trail session duration problem

    889367
      11.2.0.2 standard edition

      audit_trail=db
      auditing create session by accesss

      I'm trying to determine session durations and the resource usage of those sessions, but I'm seeing that all the logoff records in dba_audit_trail have the same value for timestamp & logoff_time and I know that it's impossible to be true from the resource usage that those records are showing.

      for example, on our quad core machine I have the following records

      select sessionid,
      to_char(timestamp,'DD-MON-YY HH24:MI:SS'),
      to_char(logoff_time,'DD-MON-YY HH24:MI:SS'),
      to_char(extended_timestamp,'DD-MON-YY HH24:MI:SS'),
      action,action_name,
      logoff_lread,logoff_pread,logoff_lwrite,session_cpu*.01
      from dba_audit_trail
      where action in ( 100,101, 102 )
      order by sessionid,action;

      15750655     17-OCT-12 22:25:42     17-OCT-12 22:25:42     17-OCT-12 22:25:42     102     LOGOFF BY CLEANUP     1602201159     1238192717     941059     11113.38
      15790652     14-OCT-12 11:02:52     (null)                    14-OCT-12 11:02:52     100     LOGON                    
      15790652     14-OCT-12 11:03:03     14-OCT-12 11:03:03     14-OCT-12 11:03:03     101     LOGOFF     10357     16248     4     3.22

      you can see the first record shows the session didn't last over a second but yet it used 11113.38 seconds of cpu. The second and third records are the same session and the logon record shows the correct login time, but that didn't follow through to the logoff record.

      Not sure if I'm doing something wrong, or if I found a bug. I did search meta link and found nothing yet that looks related.
        • 1. Re: dba_audit_trail session duration problem
          889367
          Also, i found a post on askTom that mentions this as well, but no reply to the post...

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439#4190384800346372891
          • 2. Re: dba_audit_trail session duration problem
            Osama_Mustafa
            Hope this will help you
            Timestamp And Logoff_time Columns In Audit views Are In Different Time Zones in 10g [ID 271615.1]
            • 3. Re: dba_audit_trail session duration problem
              889367
              I had found that, this issue doesn't appear to have anything to do with different timezones though.
              • 4. Re: dba_audit_trail session duration problem
                889367
                I've come up with a solution that has satisfied what I wanted. I'm making an assumption for missing login records, I'm going to use the cpu seconds value for the duration. I know why those records are missing though and it will be corrected in time.

                for 11g to get a picture of historical resource usage out of the audit trail I'm using this now.

                select username
                ,userhost
                ,count(*) as CNT
                ,round(SUM(DURATION_SECONDS),2) as DURATION_SECONDS
                ,round(SUM(DURATION_SECONDS)/COUNT(*),2) SEC_PER_SESS
                ,sum(cpu_seconds) as cpu_seconds

                ,sum(logical_reads) as logical_reads
                ,round(sum(logical_reads)/
                case when SUM(DURATION_SECONDS) = 0 then 1
                else SUM(DURATION_SECONDS)
                end,2) as lr_per_sec


                ,sum(disk_reads) as disk_reads
                ,round(sum(disk_reads)/
                case when SUM(DURATION_SECONDS) = 0 then 1
                else SUM(DURATION_SECONDS)
                end,2) as pr_per_sec


                ,sum(logical_writes) as logical_writes
                ,round(sum(logical_writes)/
                case when SUM(DURATION_SECONDS) = 0 then 1
                else SUM(DURATION_SECONDS)
                end,2) as lw_per_sec

                from
                (
                select sessionid,username,userhost,action_name
                ,
                CASE
                lag(action_name,1) over (order by sessionid,action)
                WHEN 'LOGON'
                THEN
                lag(timestamp,1) over (order by sessionid,action)
                ELSE
                timestamp
                END AS LOGON_TMSTMP

                ,logoff_time as loggedoff

                ,CASE
                lag(action_name,1) over (order by sessionid,action)
                WHEN 'LOGON'
                THEN
                CEIL((TIMESTAMP - (lag(timestamp,1) over (order by sessionid,action) ))*1440*60)
                ELSE session_cpu*.01
                END AS DURATION_SECONDS
                ,session_cpu*.01 as cpu_seconds
                ,logoff_lread as logical_reads
                ,logoff_pread as disk_reads
                ,logoff_lwrite as logical_writes
                from dba_audit_trail
                where action in (100,101,102)
                order by sessionid,action
                )
                where action_name <> 'LOGON'
                GROUP BY USERNAME,USERHOST
                order by username,userhost
                ;

                here's what I was using for 10g:

                select username,userhost,COUNT(*) AS CNT
                ,SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60)) as DURATION_SECONDS
                ,round(SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60))/COUNT(*),2) SEC_PER_SESS
                ,sum(session_cpu)*.01 as cpu_seconds

                ,sum(logoff_lread) as logical_reads
                ,round(sum(logoff_lread)/
                case when SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60)) = 0 then 1
                else SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60))
                end,2) as lr_per_sec

                ,sum(logoff_pread) as disk_reads
                ,round(sum(logoff_pread)/
                case when SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60)) = 0 then 1
                else SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60))
                end,2) as pr_per_sec

                ,sum(logoff_lwrite) as logical_writes
                ,round(sum(logoff_lwrite)/
                case when SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60)) = 0 then 1
                else SUM(CEIL((LOGOFF_TIME - TIMESTAMP)*1440*60))
                end,2) as lw_per_sec

                from dba_audit_trail
                where action in ( 101, 102 )
                GROUP BY USERNAME,USERHOST
                order by username,userhost;