바인드 변수 값 조회

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.