[스크랩] audit trail 설정 VS 미설정 비교

 

http://aozjffl.tistory.com/412

 

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배 정도의 시간이
소요된다는 것을 확인할 수 있었다.