This discussion is archived
4 Replies Latest reply: Nov 21, 2012 10:12 AM by 889367 RSS

dba_audit_trail session duration problem

889367 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points