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
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
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$ ;