User performance Auditing

• audit.sql
• noaudit.sql

• trigger on aud$
• get statistics on logout event on aud$
• save v$sesstat
• save v$session_events
• use exectute dbms_application_info.set_module('MODULE', 'ACTION');

audit_trail=DB

To fill in the action and module fields use command:

  exectute dbms_application_info.set_module('MODULE', 'ACTION');

inside your user applicaitons.

You can change the CLIENT_INFO column though, using the stored procedure
DBMS_APPLICATION_INFO.SET_CLIENT_INFO().
See $ORACLE_HOME/rdbms/admin/dbmsutil.sql for more info.

Selecting the stats for users 
col event for A30
select * from act;
select * from act_sesevt;
select name,value from act_sesstat a, v$statname n where
a.statistic#=n.statistic#;

Clearing out the tables
truncate table act_sesevt;
truncate table act_sesstat;
truncate table act;
delete aud$;

example report generation

sqlplus -s internal << EOF
set feedback off
set heading off
set pagesize 0
set verify off
set serveroutput on
execute dbms_output.enable(1000000);
declare
id number;
value number;
proc varchar2(30);
  cursor sessions is
        select SESSIONID,ACTION
         from act;
begin
for session in sessions loop
  id:=session.SESSIONID;
  proc:=session.ACTION;
  dbms_output.put_line('proc: '||proc);
  dbms_output.put_line('. WAITS:');
  for sevt in (  select event,total_waits,time_waited
                 from act_sesevt where sessionid=id) LOOP
     dbms_output.put_line('.   '||sevt.event||','||sevt.total_waits||','||sevt.time_waited);
  end loop;
  dbms_output.put_line('. STATS:');  
  for stat in (  select name, value
                        from act_sesstat a, v\$statname n
                        where a.statistic#=n.statistic# and
                        sessionid=id) LOOP
     dbms_output.put_line('.   '||stat.name||','||stat.value);
  end loop;
end loop;
end;
/
exit;
EOF



stats for users connected
column name format a30
column action format a20
select action,name,value
from act_sesstat a, v$statname n , act s where
a.statistic#=n.statistic# and name = 'redo size'
and s.sessionid=a.sessionid
order by s.sessionid;

Looking at a session in action
select
        name,
        value
   from
        v$sesstat a, v$statname n, v$session s
   where
      a.statistic#=n.statistic# and
      s.audsid=userenv('SESSIONID') and
      s.sid=a.sid and
      n.name like 'redo size';





Questions or comments ? Send a note to Kyle Hailey