User performance Auditing


These two scripts allow each users statistics to be audited. (need audit_trail=DB in init.ora)








Questions or comments ? Send a note to oraperf@yahoo.com
To put the database audit mode first before running scripts, ie in init.ora
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