JL Computer Consultancy

Audit Connect - a cheap way to monitor the database

March 1999 / Sept 1992


One of the entertaining features of tidying up my library of Oracle oddments is discovering how old some of my little packages really are. This one dates back to 1992 and Oracle v6.

One of the tasks of the dba is to keep an eye on growth not only of the physical size of a databases, but also of the amount of use the database is suffering. An easy and cheap way to acquire a picture of the increasing use of the database, and to spot potentially severe demands on resources, is to switch on the audit feature of the database at the connection level only. This requires only three steps -

The audit trail

When auditing is set to database auditing, then rows are created in the table SYS.AUD$. The options available for auditing are fairly fine-grained - you can audit every time a particular table is accessed, for instance - but it will be no surprise that auditing introduces an overhead.

However, if you restrict auditing to just the session connect then one row is written to AUD$ every time a session connects and this row is updated as the session terminates. The (version independent) information of most interest tells you who connected to Oracle (both their Oracle ID and their O/S id, where they connected from, what time they logged on and off, and how much work they did in terms of logical reads, physical reads, and database changes. Each row typically uses about 100 to 110 bytes of data, although long names for users and machines could easily double this, so the cost is not too great.

If you have this information recorded then it is possible to analyze it in several ways by simple SQL directed at the DBA_AUDIT_SESSION view. Obviously a simple listing by time (using, say the mid-point between connect and logon time) will give you a rough pattern of usage during the day. Grouping logical, or physical, reads by user will alert you to a computer-hog on the system. Extreme numbers under logical reads may direct you to a user who has acquired an unauthorsied end-user query tool. Simply summing the total work done every 12 hours may give you a simple picture of increasing use of the database. Having a hard record of when extreme amounts of work were done may assist you in answering complaints about performance.

The best wasy to use this information is really up to you, the nature of your system, and the habits of your users. But it is not until you have tried it and run off a couple of quick reports that you can appreciate how helpful it can be.

Three peripheral points - First you must remember to delete the data regularly: the SYS.AUD$ table lives by default in the SYSTEM tablespace and you don't want to fill that up and stop the system. Secondly AUD$ table is the one SYS-owned table that the Oracle manuals specifically allow you to mess about with, to the extent that there is even a suggestion that you drop it and recreate it in a separate tablespace. Finally, if you are about to switch auditing on, it can be a nasty shock to discover that some low-level (hence expensive) audit options are already in place, so the last little script below checks how many objects are currently flagged for audit.


Sample query

rem
rem     Program:       see_aud1.sql
rem     Author (c):    J.P.Lewis
rem     Dated:         11th Sept 1992
rem
rem     Purpose:
rem            List summary read/write data on user connections
rem            Sorted by LOGICAL READS.
rem
rem     Input Parameters
rem            &1      userid of interest - use % for all#
rem            &2      Start time for report:  dd-mon-yyyy hh24:mi:ss
rem            &3      End time for report:   dd-mon-yyyy hh24:mi:ss
rem
set pagesize 58
set newpage 0
set termout off
set trimspool on
set verify off
define m_user=&1
define m_start=&2
define m_end=&3
column  username       format           a12   heading "Name"
column  start_time     format           a13   heading "Logon"
column  end_time       format            a6   heading "Logoff"
column  logoff_lread   format   999,999,990   heading "Logicals"
column  logoff_pread   format   999,999,990   heading "Physicals"
column  logoff_lwrite  format   999,999,990   heading "Writes"
break on report 
compute sum of logoff_lread on report
compute sum of logoff_pread on report
compute sum of logoff_lwrite on report
spool see_aud1
select
        username,
        to_char(timestamp,'dd-Mon: hh24:mi')  start_time,
        to_char(logoff_time,'hh24:mi') end_time,
        logoff_lread,
        logoff_pread,
        logoff_lwrite
  from
        dba_audit_session
 where
        logoff_time is not null
   and  username like upper('&&m_user') 
   and  timestamp > to_date('&&m_start','dd-mon-yyyy hh24:mi')
   and  logoff_time < to_date('&&m_end','dd-mon-yyyy hh24:mi')
order by
        logoff_lread,
        logoff_pread
;
spool off
set pagesize 22
set newpage 1
set termout off

Back to Main Index of Topics


Sample Output from given query

                                 Report of session audit statistics 
 
                                      Individual Logical Reads 
 
Name         Logon         Logoff     Logicals    Physicals       Writes 
------------ ------------- ------ ------------ ------------ ------------ 
SYS          15-Mar: 11:05 11:06           208            7           11 
SYS          15-Mar: 11:06 11:06           460           41            8 
SYS          15-Mar: 11:06 11:07         1,222        1,031            9 
SYS          15-Mar: 17:38 17:56         2,077          327          191 
SYS          15-Mar: 21:45 21:52         4,291        3,299            8 
SYS          15-Mar: 11:09 11:20        34,625        9,575           41 
                                  ------------ ------------ ------------ 
sum                                     42,883       14,280          268 

Back to Main Index of Topics


Query to see how extensive auditing is at present

rem
rem     Program:       chk_aud.sql
rem     Author (c):    J.P.Lewis
rem     Dated:         11th Sept 1992
rem
rem     Purpose:
rem            Checks number of auditing actions already in place.
rem
rem     Usage:
rem            Start chk_aud
rem            If output is ever NON-zero, then there are audits in place.
rem
rem     Notes:
rem            Has to be run by SYS or have access to various SYS tables.
rem            Version 8 only.  For version 7 delete the sections for 
rem            DIR$, LIBRARY$, and TYPE_MISC$
rem
set embedded off
set heading on
set newpage 0
set trimspool on
set feedback off
ttitle -
        skip 1  -
        center  'Check of audit settings' -
        skip 2  -
column  descrip format a20     heading "Type"
column  num     format 999,990 heading "Number set"
break on report skip 1
compute sum of num on report
select 
        'Users'        descrip, 
        count(*)       num
  from
        user$
 where 
        audit$ is not null
union all
select 
        'Tables'       descrip, 
        count(*)       num
  from
        tab$
 where 
        ltrim(audit$,'-') is not null
union all
select 
        'Views'        descrip, 
        count(*)       num
  from
        view$
 where 
        ltrim(audit$,'-') is not null
union all
select 
        'Sequences'    descrip, 
        count(*)       num
  from
        seq$
 where 
        ltrim(audit$,'-') is not null
union all
select
        'Procedures'   descrip, 
        count(*)       num
  from
        procedure$
 where 
        ltrim(audit$,'-') is not null
union all
select
        'Types'        descrip, 
        count(*)       num
  from
        type_misc$
 where 
        ltrim(audit$,'-') is not null
union all
select
        'Libraries'    descrip, 
        count(*)       num
  from
        library$
 where 
        ltrim(audit$,'-') is not null
union all
select
        'Directories'  descrip, 
        count(*)       num
  from
        dir$
 where 
        ltrim(audit$,'-') is not null
union all
select 
        'System'       descrip, 
        count(*)       num
  from
        audit$
;

Back to Main Index of Topics