OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.5.0.24.07 ai for Oracle Cloud and Engineered Systems
방법 : 오라클 23ai 신기능 sql 이력 확인뷰 v$sql_history
일반적으로 sql 실행이력을 확인하기 위해선 v$sql 이나 dba_hist_sql~ 뷰를 이용함
하지만 이 뷰들에는 실패한 sql은 남지 않음
23ai에 나온 신기능을 사용하면 성공한 sql 뿐만아니라 실패한 sql 까지 모니터링 가능함
이 기능은 sql_history_enabled 파라미터에 의해 제어되고 기본값은 false 임, true 로 설정 시 v$sql_history 에서 기록을 확인할 수 있음
참고로 v$sql_history의 sql_text 컬럼은 sql 전체가 아닌 100자까지만 표시됨
본문에서는 sql_history_enabled 파라미터를 설정한 뒤 sql 을 실행하여 기록들을 확인해봄
테스트
접속한 컨테이너 및 유저 확인
|
1
2
3
4
5
6
7
8
|
SQL> show con_name
CON_NAME
------------------------------
ORACLE23PDB1
SQL> show user
USER is "SYS"
|
ORACLE23PDB1 의 SYS 유저로 접속함
sql_history_enabled 파라미터 true 로 변경
|
1
2
3
|
SQL> alter system set sql_history_enabled = true scope = both;
System altered.
|
일반 유저 접속 후 파라미터 확인
|
1
2
3
4
5
6
7
8
|
SQL> conn imsi/imsi@localhost:1521/oracle23pdb1
Connected.
SQL> show parameter sql_history_enabled
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sql_history_enabled boolean TRUE
|
true임
SID 확인
|
1
2
3
4
5
|
SQL> select sid from v$mystat where rownum = 1;
SID
----------
29
|
쿼리 여러개 실행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
SQL> select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss');
TO_CHAR(SYSDATE,'YY
-------------------
2024/08/29 20:02:48
SQL> select test from test;
select test from test
*
ERROR at line 1:
ORA-00942: table or view "IMSI"."TEST" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL> select * from t1;
COL1
----------
1
2
3
4
5
SQL> create table big_emp as select * from emp connect by level <= 3;
Table created.
SQL> select max(sal) from big_emp;
MAX(SAL)
----------
5000
SQL> select sum(sal) from big_emp;
SUM(SAL)
----------
6124275
SQL> select summ(sal) from big_emp;
select summ(sal) from big_emp
*
ERROR at line 1:
ORA-00904: "SUMM": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
|
일부 쿼리는 정상 실행되었고 일부 쿼리는 테이블 미존재, 오타 등으로 인해 에러가 발생함
v$sql_history 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SQL>
set lines 200 pages 1000
col sql_text for a40
select to_char(sql_exec_start, 'yyyy/mm/dd hh24:mi:ss') sql_exec_start, sql_id, substr(sql_text, 1, 40) sql_text
, elapsed_time, cpu_time, buffer_gets, error_number
from v$sql_history
where sid = 29;
SQL_EXEC_START SQL_ID SQL_TEXT ELAPSED_TIME CPU_TIME BUFFER_GETS ERROR_NUMBER
------------------- ------------- ---------------------------------------- ------------ ---------- ----------- ------------
2024/08/29 20:02:01 6u5zqzz2nm55c SELECT DECODE(USER, 'XS$NULL', XS_SYS_C 8 8 0 0
2024/08/29 20:02:09 bfqu55mwuxk4x SELECT NAME_C 7 7 0 0
2024/08/29 20:02:48 3394kzda8paaw select to_char(sysdate, 'yyyy/mm/dd hh24 6 6 0 0
0q4mphpa8nk1a select test from test 0 0 0 942
2024/08/29 20:03:02 27uhu2q2xuu7r select * from t1 8 8 1 0
2024/08/29 20:04:08 4gspafp9wh7jm create table big_emp as select * from em 0 0 0 0
2024/08/29 20:04:25 4nfdhks9ks4sz select max(sal) from big_emp 884 288 20 0
2024/08/29 20:04:32 553k73suscq77 select sum(sal) from big_emp 152 0 20 0
76dxr42ytkzdp select summ(sal) from big_emp 0 0 0 904
2024/08/29 20:06:48 dun0h7chf2t8s select sid from v$mystat where rownum = 3 3 0 0
10 rows selected.
|
이 세션에서 직접 실행한것이 아닌 내부 쿼리도 포함되어 있지만 세션에서 실행한 쿼리들이 모두 표시됨
흥미로운점은 실행에 실패한 sql들의 정보가 남아있다는 점임
sql_exec_start 는 비어있지만 error_number 컬럼에 에러 번호가 표시되어 특정 쿼리 문제가 발생했을때 이를 추적하기 좋은 기능으로 보임
나열한 컬럼 외에도 다양한 정보(컬럼)들이 있어 유용함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
SQL> desc v$sql_history
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
KEY NUMBER
SQL_ID VARCHAR2(13)
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
PLSQL_EXEC_TIME NUMBER
JAVA_EXEC_TIME NUMBER
CLUSTER_WAIT_TIME NUMBER
CONCURRENCY_WAIT_TIME NUMBER
APPLICATION_WAIT_TIME NUMBER
USER_IO_WAIT_TIME NUMBER
IO_CELL_UNCOMPRESSED_BYTES NUMBER
IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER
SQL_TEXT VARCHAR2(100)
PLAN_HASH_VALUE NUMBER
SQL_EXEC_ID NUMBER
SQL_EXEC_START DATE
LAST_ACTIVE_TIME DATE
SESSION_USER# NUMBER
CURRENT_USER# NUMBER
CHILD_NUMBER NUMBER
SID NUMBER
SESSION_SERIAL# NUMBER
MODULE_HASH NUMBER
ACTION_HASH NUMBER
SERVICE_HASH NUMBER
IS_FULL_SQLTEXT VARCHAR2(1)
ERROR_SIGNALLED VARCHAR2(1)
ERROR_NUMBER NUMBER
ERROR_FACILITY VARCHAR2(4)
STATEMENT_TYPE VARCHAR2(5)
IS_PARALLEL VARCHAR2(1)
CON_ID NUMBER
|
참고로 이 파라미터를 system 레벨에서 false 로 변경한 다음 세션 레벨로 true 로 변경한뒤 테스트 해보았지만
v$sql_history 에 쿼리들이 쌓이지 않았음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> alter session set sql_history_enabled = true;
System altered.
SQL> select sysdate;
SYSDATE
---------
04-SEP-24
SQL> select ssysdate;
select ssysdate
*
ERROR at line 1:
ORA-00904: "SSYSDATE": invalid identifier
Help: https://docs.oracle.com/error-help/db/ora-00904/
SQL> select * from v$sql_history;
no rows selected
|
결론 :
sql history 기능으로 특정 세션에서 실행한 모든 쿼리들을 모니터링 할 수 있게됨
뿐만아니라 문제가 생겼을때 이력 확인용으로도 좋을것 같음
다만 파라미터가 system 레벨로 적용하는거라 트랜잭션이 많은 사이트에서는 이 작업이 부하가 되진 않을까 싶음
추후 유저나 세션레벨로 설정하는 기능이 나오면 더 좋을듯함
참조 :
https://positivemh.tistory.com/1163
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1671
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/monitoring-database-operations.html#GUID-2B171F73-E4AA-404E-841D-5A1AC16B096D
https://blogs.oracle.com/coretec/post/easy-sql-statement-tracking-in23c
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 스테이징 테이블(Staging) (0) | 2024.11.08 |
|---|---|
| 오라클 23ai 신기능 스키마 레벨 권한 부여 (0) | 2024.11.05 |
| 오라클 23ai 신기능 bigfile 테이블스페이스 shrink (0) | 2024.10.28 |
| 오라클 23ai 신기능 SQL 도메인(Domain) (0) | 2024.10.22 |
| 오라클 23ai 신기능 테이블 주석 기능(Annonation) (0) | 2024.10.18 |