개요
바인드 변수 값을 확인할 수 있는 방법에 대해 설명합니다.
•
view 이용
•
ilog 이용
방법
view를 이용한 확인
•
BIND_VARIABLE_CAPTURE 파라미터가 YES로 되어 있어야 합니다.
•
V$SQL_BIND_CAPTURE_ALL에서 확인 가능합니다.
SQL> select * from V$SQL_BIND_CAPTURE_ALL;
SQL_HASH_VALUE PLAN_HASH_VALUE SQL_ID CHILD_NUMBER USER_ID NAME POSITION TYPE
-------------- --------------- ------------- ------------ ---------- -------------------- ---------- ----------
TYPE_NAME PRECISION SCALE LENGTH TIME_CAPTURED VALUE
-------------------- ---------- ---------- ---------- --------------- --------------------
3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 2 5
DATE 0 0 8 2025/03/12 2025/03/12
3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 1 3
VARCHAR2 0 0 32 2025/03/12 PARK
3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 0 1
NUMBER 38 -128 22 2025/03/12 1
3 rows selected.
SQL
복사
#. SQL_TEXT와 함께 확인하는 구문
SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b
where a.SQL_ID = b.SQL_ID;
#. 예시)
SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b
where a.SQL_ID = b.SQL_ID;
SQL_ID USER_ID NAME TYPE_NAME VALUE SQL_TEXT
------------- ---------- ---------- --------------- -------------------- --------------------------------------------------
9a1kqxzqn8hy4 18 ? DATE 2025/03/12 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD')
9a1kqxzqn8hy4 18 ? VARCHAR2 PARK SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD')
9a1kqxzqn8hy4 18 ? NUMBER 1 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD')
3 rows selected.
SQL
복사
ilog를 이용한 확인
Tibero에 접속해서 아래의 TAG 리스트를 활성화합니다.
•
TAG 활성화는 System 단위로 하며 활성화 Level은 Detail로 합니다.
SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_MSG_CSR_BIND_PARAM_INFO' LEVEL DETAIL;
SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_UNKNOWN' LEVEL DETAIL;
SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_SQL_PROCESS' LEVEL DETAIL;
SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_NULL' LEVEL DETAIL;
SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG' LEVEL DETAIL;
SQL
복사
$ cd /$TB_HOME/instance/$TB_SID/log/ilog
$ ls -alt
합계 12844
-rw-r--r-- 1 jinhwa jinhwa 65536 3월 13 13:16 tibero7_2_2_287079-91.ilg
-- 중략 --
$ tbiv tibero7_2_2_287079-91.ilg
-- 중략 --
03/13 13:16:40.5 0-0091 tbsvr_sq:277 SQL PROCESS: sql=SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?, 'YYYY/MM/DD'), csr=2
03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #0, param_kind=IN, param_type=TB_TYPE_NUMBER
03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #0: param_val_len=1, param_val: [1]
03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #1, param_kind=IN, param_type=TB_TYPE_VARCHAR
03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #1: param_val_len=4, param_val: [PARK]
03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #2, param_kind=IN, param_type=TB_TYPE_DATE
03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #2: param_val_len=10, param_val: [2025/03/13]
Completed.
SQL
복사