audit trail 설정 vs audit trail 미설정 비교

1. audit trail 설정

현재 테스트하는 오라클의 버전을 확인

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

현재 audit_trail 파라미터의 값을 확인한다. 현재는 DB로 설정되어 있어
audit 정보가 테이블에 저장됨을 확인

SQL> show parameter audit_trail 

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_trail        string  DB


test 유저의 t1테이블에 select가 일어날 경우 audit을 실행하도록 설정한다.


SQL> audit select on test.t1;

그리고 sql_trace를 실행하기 위하여 test 유저에 alter session 권한을 부여한다.

SQL> grant alter session to test;

권한이 부여되었습니다.

test 유저로 접속

SQL> conn test/test

세션에 sql trace 설정.

SQL> alter session set sql_trace=true;

SQL> select * from t1;

      COL1 COL2
---------- ----------
  1 test
  2 test
..
..
..

sql trace를 disable 시켜준다.

SQL> alter session set sql_trace=false;

trace 파일이 쌓이는 user_dump_dest의 위치를 확인한다.

SQL> conn / as sysdba
SQL> show parameter user_dump_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest        string  /oracle/admin/jgh/udump

SQL> exit

user_dump_dest로 이동하여 tkprof 툴을 이용하여 트레이스 파일을 포맷팅한다.

# cd /oracle/admin/jgh/udump
# tkprof jgh_ora_15594.trc audit_on.out

결과는 아래와 같다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       93      0.24       1.37          0          0          0           0
Execute   1550      3.75       6.11         18       4620          0        1507
Fetch       43      0.14       0.40          4        346          0          42
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1686      4.14       7.88         22       4966          0        1549

Misses in library cache during parse: 9
Misses in library cache during execute: 7

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      172      0.19       0.21          0          0          0           0
Execute   3314      1.94       2.83         17        759        720         237
Fetch     3301      2.09       3.50         50       5533          0        1746
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6787      4.24       6.55         67       6292        720        1983

Misses in library cache during parse: 15
Misses in library cache during execute: 14
 
  122  user  SQL statements in session.
  179  internal SQL statements in session.
  301  SQL statements in session.


2. audit trail 설정 안되어있을 경우


audit_trail 파라미터를 none으로 설정한 후 DB 재시작

SQL> alter system set audit_trail=none scope=spfile;

SQL> startup force;

변경된 파라미터 확인

SQL> show parameter audit_trail

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_trail        string  NONE

SQL> conn test/test

SQL> alter session set sql_trace=true;

세션이 변경되었습니다.

SQL> select * from t1;

      COL1 COL2
---------- ----------
  1 test
  2 test
  3 test
..
..
..

SQL> alter session set sql_trace=false;

세션이 변경되었습니다.

SQL> conn / as sysdba

SQL> show parameter user_dump_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest        string  /oracle/admin/jgh/udump

SQL> exit

$ cd /oracle/admin/jgh/udump
$ tkprof jgh_ora_16172.trc audit_off.out

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       19      0.15       0.38          0          0          0           0
Execute    119      0.41       1.06         15        555          0         113
Fetch        6      0.02       0.47          4         50          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      144      0.59       1.92         19        605          0         118

Misses in library cache during parse: 9
Misses in library cache during execute: 7

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      163      0.14       0.23          0          0          0           0
Execute    398      0.38       0.50         13         69         45          19
Fetch      528      0.39       0.42         30       1048          0         338
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1089      0.92       1.16         43       1117         45         357

Misses in library cache during parse: 15
Misses in library cache during execute: 14

   48  user  SQL statements in session.
  160  internal SQL statements in session.
  208  SQL statements in session.

---

최종 비교

audit_trail = on일 경우  NON-RECURSIVE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       93      0.24       1.37          0          0          0           0
Execute   1550      3.75       6.11         18       4620          0        1507
Fetch       43      0.14       0.40          4        346          0          42
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1686      4.14       7.88         22       4966          0        1549

audit_trail = off일 경우  NON-RECURSIVE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       19      0.15       0.38          0          0          0           0
Execute    119      0.41       1.06         15        555          0         113
Fetch        6      0.02       0.47          4         50          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      144      0.59       1.92         19        605          0         118

NON-RECURSIVE SQL에서의 elapsed time은 7.88/1.92 약 4.1배 정도 차이가 난다.

audit_trail = on일 경우  RECURSIVE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      172      0.19       0.21          0          0          0           0
Execute   3314      1.94       2.83         17        759        720         237
Fetch     3301      2.09       3.50         50       5533          0        1746
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6787      4.24       6.55         67       6292        720        1983



audit_trail = off일 경우  RECURSIVE

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      163      0.14       0.23          0          0          0           0
Execute    398      0.38       0.50         13         69         45          19
Fetch      528      0.39       0.42         30       1048          0         338
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1089      0.92       1.16         43       1117         45         357

RECURSIVE SQL에서의 elapsed time은 6.55/1.16 약 5.64 배 정도 차이가 난다.

=> NON RECURSIVE SQL과 RECURSIVE SQL을 합산한 결과는
   (7.88 + 6.55) / (1.92 + 1.16) = 4.68배
   audit trail을 설정하여 해당 table에 select 시에 audit을 할 경우 테스트 결과로 약 4.68배 정도의 시간이
   소요된다는 것을 확인할 수 있었다.