|
Audit Trail
|
Hello everyone. In this issue, I will continue analyzing audit trail.
Last week, I set audit_trail parameter to DB to let all audit records be stored
to database audit trail (i.e. SYS.AUD$). Also, I determined LPS (Logon Per Second)
to examine how much LPS affected performance.
Security
This time, after auditing a session, I will list up the categories related to security
that must be checked. I will be viewing DBA_AUDIT_SESSION again.
Must see categories in DBA_AUDIT_SESSION
-------------------------- -------------------------------------------------------------
TIMESTAMP,LOGOFF_TIME :Make sure to audit if there is any session logging in for a
long time.
-------------------------- -------------------------------------------------------------
LOGOFF_LREAD,LOGOFF_PREAD :Audit logical and physical reads in a session and verify
the validity of the operation if it is too heavy.
-------------------------- -------------------------------------------------------------
RETURNCODE :Need to audit codes except 0
-------------------------- -------------------------------------------------------------
SESSION_CPU :By auditing CPU usage in a session, it might help finding
a loop process.
|
*************************************************************************
SQL> select os_username osuname
,username dbuname
,sessionid sesid
,(logoff_time-timestamp)*24*60*60 conn_time
,logoff_lread lread
,logoff_pread pread
,action_name act_name
,returncode retcd
,session_cpu ses_cpu
from dba_audit_session;
OSUNAME DBUNAME SESID CONN_TIME LREAD PREAD ACT_NAME RETCD SES_CPU
------- ------- ----- --------- -------- -------- -------- ----- -------
kshin SYSTEM 32857 4546 221647 12726 LOGOFF 0 2418 <-(1)
... ^^^^
oracle ORABM 32856 2330 12392624 1 LOGOFF 0 230963 <-(2)
^^^^^^
*************************************************************************
|
There is a session logging in for 77 minutes (4546 seconds) ->(1)
There is another session consuming CPU for 2300 seconds (230963 milliseconds) ->(2)
I need to find out what these sessions are trying to do.
Let's check the SQL statement loaded by the user ORABM and SYSTEM.
*************************************************************************
SQL> select vs.sql_text
,du.username
,vs.executions execs
,vs.loads loads
,vs.disk_reads d_reads
,vs.buffer_gets buf_gets
,vs.rows_processed row_proc
,vs.cpu_time cpu_t
from v$sql vs, dba_users du, dba_audit_session da
where da.sessionid in (32856,32857)
and vs.parsing_user_id = du.user_id
and du.username = da.username
and vs.last_load_time between to_char(da.timestamp
,'yyyy-mm-dd/hh24:mi:ss')
and to_char(da.logoff_time
,'yyyy-mm-dd/hh24:mi:ss');
SQL_TEXT DBUNAME EXECS LOADS D_READS BUF_GETS ROW_PROC CPU_T
------------------ ------- ----- ----- ------- -------- -------- ------
declare a varchar2 ORABM 1 1 1 12392590 0 230961
SELECT * FROM DBA_ SYSTEM 4 1 3955 4016 132 220
SELECT * FROM V$SE SYSTEM 1 1 1 60 1 4
...
(skipped)
*************************************************************************
|
This SQL statement is not capable of identifying the session but is
only capable of specifying when and by whom the SQL statement has been loaded.
We need further information besides DBA_AUDIT_SESSION to keep
track of operations made in a session.
(LAST_LOAD_TIME only indicates the time when the SQL statement is last
loaded.)
Even though I want to keep track of operation history in the session
other than user account (i.e. audit who, when, and where), I cannot
make it only with session audit.
It may be necessary to raise an audit level and to audit an object so that
I can audit the SQL statement loaded in a specific object.
Performance
LPS which I mentioned last time indicates the upper limit of concurrent
logons. Thus, LPS may not be useful to the cases where there are no heavy
concurrent logons.
I use TPC-C benchmark for measuring performance and scalability of
OLTP systems in fifty sessions. The test involves ten benchmarks
in total (five each for audit and non-audit) to determine average
TPS (Transaction Per Second).
This TPC-C benchmark makes Oracle server be overloaded (CPU usage reaches
100%.)
Results are as follows:
(1) with audit: 131 TPS
(2) without audit: 131 TPS
The results prove that session audit in normal OLTP process does not
affect performance at all.
Next week, I will talk about object audit.
That's it for today.
Koji Shinkubo
|
|
