2018년 11월 19일에 작성한 "바인드 변수 값 조회" 글을 보완한 글입니다.
Oracle 데이터베이스는 V$SQL_BIND_CAPTURE 뷰, V$SQL 뷰의 bind_data 칼럼, V$SQL_PLAN 뷰의 other_xml 칼럼, V$SQL_MONITOR 뷰의 binds_xml 칼럼 등에 바인드 변수 관련 데이터를 저장합니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1, c2, c3, c4) AS
SELECT 1, 'A', DATE '2050-01-01', TIMESTAMP '2050-01-01 00:00:00.123456' FROM DUAL;
아래와 같이 다양한 데이터 타입의 바인드 변수를 사용한 쿼리를 수행하겠습니다.
-- 2
SET SERVEROUT ON
DECLARE
v1 NUMBER := 1;
v2 VARCHAR2(1) := 'A';
v3 DATE := DATE '2050-01-01';
v4 TIMESTAMP := TIMESTAMP '2050-01-01 00:00:00.123456';
v5 NUMBER;
v_sql_id VARCHAR2(13);
BEGIN
SELECT /*+ MONITOR */
c1
INTO v5
FROM t1
WHERE c1 = v1
AND c2 = v2
AND c3 = v3
AND c4 = v4;
SELECT prev_sql_id
INTO v_sql_id
FROM v$session
WHERE sid = SYS_CONTEXT ('USERENV', 'SID');
DBMS_OUTPUT.PUT_LINE (v_sql_id);
END;
/
1utbpv9wvpkxz
PL/SQL procedure successfully completed.
아래는 V$SQL_BIND_CAPTURE 뷰를 조회한 결과입니다. DATE 타입의 바인드 변수 값은 MM/DD/YYYY HH24:MI:SS 포맷으로 저장되며, TIMESTAMP 타입의 바인드 변수 값은 value_anydata 칼럼에 저장된 값을 ANYDATA.ACCESSTIMESTAMP 함수로 변환하여 조회할 수 있습니다.
-- 3
SELECT position
, name
, datatype_string
, value_string
, ANYDATA.ACCESSTIMESTAMP (value_anydata) AS value_anydata_ts
FROM v$sql_bind_capture
WHERE sql_id = '1utbpv9wvpkxz'
AND child_number = 0;
POSITION NAME DATATYPE_STRING VALUE_STRING VALUE_ANYDATA_TS
-------- ---- --------------- ------------------- -----------------------------
1 :B4 NUMBER 1
2 :B3 VARCHAR2(32) A
3 :B2 DATE 01/01/2050 00:00:00
4 :B1 TIMESTAMP 2050-01-01 00:00:00.123456000
4 rows selected.
아래는 V$SQL 뷰의 bind_data 칼럼을 조회한 결과입니다. name 칼럼에 데이터가 저장되지 않으며, 바인드 값은 V$SQL_BIND_CAPTURE 뷰와 동일한 방식으로 바인드 변수 값을 조회할 수 있습니다.
-- 4
SELECT b.position
, b.name
, b.datatype_string
, b.value_string
, ANYDATA.ACCESSTIMESTAMP (b.value_anydata) AS value_anydata
FROM v$sql a
, TABLE (DBMS_SQLTUNE.EXTRACT_BINDS (a.bind_data)) b
WHERE a.sql_id = '1utbpv9wvpkxz'
AND a.child_number = 0;
POSITION NAME DATATYPE_STRING VALUE_STRING VALUE_ANYDATA
-------- ---- --------------- ------------------- -----------------------------
1 NUMBER 1
2 VARCHAR2(32) A
3 DATE 01/01/2050 00:00:00
4 TIMESTAMP 2050-01-01 00:00:00.123456000
4 rows selected.
아래는 V$SQL 뷰의 bind_data 칼럼을 조회한 결과입니다. 바인드 값이 Hex 값으로 저장되며, 5-2번 쿼리처럼 DBMS_STATS.CONVERT_RAW_TO_* 함수로 Hex 값을 변환하여 조회할 수 있습니다. DBMS_XPLAN.DISPLAY_CURSOR 함수는 Timestamp 타입의 바인드 변수 값을 표시하지 않습니다.
-- 5-1
SELECT b.*
FROM v$sql_plan a
, XMLTABLE ('other_xml/peeked_binds/bind' PASSING XMLTYPE (a.other_xml) COLUMNS
pos NUMBER PATH '@pos'
, nam VARCHAR2(100) PATH '@nam'
, dty NUMBER PATH '@dty'
, value RAW(2000) PATH 'text()') b
WHERE a.sql_id = '1utbpv9wvpkxz'
AND a.child_number = 0
AND a.other_xml IS NOT NULL;
POS NAM DTY VALUE
--- --- --- ----------------------
1 :B4 2 C102
2 :B3 1 41
3 :B2 12 78960101010101
4 :B1 180 78960101010101075BCA00
4 rows selected.
-- 5-2
SELECT DBMS_STATS.CONVERT_RAW_TO_NUMBER ('C102') AS c1 -- UTL_RAW.CAST_TO_NUMBER ('C102')
, DBMS_STATS.CONVERT_RAW_TO_VARCHAR2 ('41') AS c2 -- UTL_RAW.CAST_TO_VARCHAR2 ('41')
, DBMS_STATS.CONVERT_RAW_TO_DATE ('78960101010101') AS c3
, CAST (DBMS_STATS.CONVERT_RAW_TO_DATE (SUBSTR ('78960101010101075BCA00', 1, 14)) AS TIMESTAMP)
+ NUMTODSINTERVAL (NVL (TO_NUMBER (SUBSTR ('78960101010101075BCA00', 15, 8), 'XXXXXXXX') / 1e9, 0), 'SECOND') AS c4
FROM DUAL;
C1 C2 C3 C4
-- -- ------------------- -----------------------------
1 A 2050-01-01 00:00:00 2050-01-01 00:00:00.123456000
1 row selected.
-- 5-3
SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('1utbpv9wvpkxz', 0, format => '+PEEKED_BINDS'));
Peeked Binds (identified by position):
--------------------------------------
1 - :B4 (NUMBER): 1
2 - :B3 (VARCHAR2(30), CSID=846): 'A'
3 - :B2 (DATE): 01/01/2050 00:00:00
4 - :B1 (TIMESTAMP): [Not Printable]
아래는 V$SQL_MONITOR 뷰의 binds_xml 칼럼을 조회한 결과입니다. Timestamp 타입의 바인드 변수 값이 Hex 값으로 저장되며 앞선 예제와 동일한 방법으로 값을 조회할 수 있습니다. DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수는 Timestamp 타입의 바인드 변수 값을 Hex 값으로 표시합니다.
-- 6-1
SELECT b.*
FROM v$sql_monitor a
, XMLTABLE ('binds/bind' PASSING XMLTYPE (a.binds_xml) COLUMNS
pos NUMBER PATH '@pos'
, name VARCHAR2(100) PATH '@name'
, dtystr VARCHAR2(100) PATH '@dtystr'
, format VARCHAR2(100) PATH '@format'
, value VARCHAR2(4000) PATH 'text()') b
WHERE a.sql_id = '1utbpv9wvpkxz';
POS NAME DTYSTR FORMAT VALUE
--- ---- ------------ ------- ----------------------
1 :B4 NUMBER 1
2 :B3 VARCHAR2(32) A
3 :B2 DATE 01/01/2050 00:00:00
4 :B1 TIMESTAMP hexdump 78960101010101075BCA00
4 rows selected.
-- 6-2
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR ('1utbpv9wvpkxz') AS report
FROM DUAL;
Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B4 | 1 | NUMBER | 1 |
| :B3 | 2 | VARCHAR2(32) | A |
| :B2 | 3 | DATE | 01/01/2050 00:00:00 |
| :B1 | 4 | TIMESTAMP | 78960101010101075BCA00 |
========================================================================================================================
참고
- V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP (Doc ID 444551.1)
- TIMESTAMP Data Type Bind Variables Are Incorrectly Reported as Hex Number in SQL MONITOR (Doc ID 2111950.1)
업데이트 (2024-05-12)
Oracle 23c부터 DBMS_XPLAN.DISPLAY_CURSOR 함수와 DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수가 Timestamp 타입의 바인드 변수 값을 아래와 같이 표시합니다.
-- 7-1
SELECT *
FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('1utbpv9wvpkxz', 0, format => '+PEEKED_BINDS'));
Peeked Binds (identified by position):
--------------------------------------
1 - :B4 (NUMBER): 1
2 - :B3 (VARCHAR2(30), CSID=873): 'A'
3 - :B2 (DATE): 01/01/2050 00:00:00
4 - :B1 (TIMESTAMP): 2050-01-01 00:00:00.123456000
-- 7-2
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR ('1utbpv9wvpkxz') AS report
FROM DUAL;
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :B4 | 1 | NUMBER | 1 |
| :B3 | 2 | VARCHAR2(32) | A |
| :B2 | 3 | DATE | 01/01/2050 00:00:00 |
| :B1 | 4 | TIMESTAMP | 2050-01-01T00:00:00Z |
========================================================================================================================
아래는 Oracle 23c에서 V$SQL_MONITOR 뷰의 binds_xml 칼럼을 조회한 결과입니다. Timestamp 타입의 바인드 변수 값이 문자 값으로 저장된 것을 확인할 수 있습니다.
-- 8
SELECT b.*
FROM v$sql_monitor a
, XMLTABLE ('binds/bind' PASSING XMLTYPE (a.binds_xml) COLUMNS
pos NUMBER PATH '@pos'
, name VARCHAR2(100) PATH '@name'
, dtystr VARCHAR2(100) PATH '@dtystr'
, format VARCHAR2(100) PATH '@format'
, value VARCHAR2(4000) PATH 'text()') b
WHERE a.sql_id = '1utbpv9wvpkxz';
POS NAME DTYSTR FORMAT VALUE
--- ---- ------------ ------ -----------------------------
1 :B4 NUMBER 1
2 :B3 VARCHAR2(32) A
3 :B2 DATE 01/01/2050 00:00:00
4 :B1 TIMESTAMP 2050-01-01 00:00:00.123456000
4 rows selected.