바인드 변수 값 조회

오라클 데이터베이스는 다양한 방법으로 바인드 변수 값을 조회할 수 있다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER, c3 NUMBER);

아래와 같이 쿼리를 수행하고 SQL ID와 child number를 확인하자.

VAR v1 NUMBER;
VAR v2 NUMBER;
VAR v3 NUMBER;
VAR v4 NUMBER;
VAR v5 NUMBER;

EXEC :v1 := 1;
EXEC :v2 := 2;
EXEC :v3 := 3;
EXEC :v4 := 4;
EXEC :v5 := 5;

-- 2-1
SELECT /*+ MONITOR */
       :v3 AS c1, :v4 AS c2, :v5 AS c3
  FROM t1
 WHERE c1 = :v1
   AND c2 = :v2
   AND c3 = :v3;

-- 2-2
SELECT prev_sql_id, prev_child_number
  FROM v$session
 WHERE sid = SYS_CONTEXT ('USERENV', 'SID');

PREV_SQL_ID   PREV_CHILD_NUMBER
------------- -----------------
ckrnp9t0x37xh                 0

1개의 행이 선택되었습니다.

바인드 변수 값을 조회하는 가장 쉬운 방법은 V$SQL_BIND_CAPTURE 뷰를 사용하는 것이다. WHERE 절과 HAVING 절에 사용한 바인드 변수 값만 기록된다.

-- 3
SELECT name, position, dup_position, datatype_string, value_string
  FROM (SELECT a.*
             , ROW_NUMBER () OVER (PARTITION BY a.position ORDER BY a.last_captured DESC) AS rn 
          FROM v$sql_bind_capture a
         WHERE a.sql_id = 'ckrnp9t0x37xh'
           AND a.child_number = 0)
 WHERE rn = 1;

NAME POSITION DUP_POSITION DATATYPE_STRING VALUE_STRING
---- -------- ------------ --------------- ------------
:V3         1              NUMBER          3
:V4         2              NUMBER
:V5         3              NUMBER
:V1         4              NUMBER          1
:V2         5              NUMBER          2
:V3         6            1 NUMBER          3

6 행이 선택되었습니다.

아래는 해당 뷰와 관련된 파라미터다.

NAME                           VALUE DESCRIPTION
------------------------------ ----- -----------------------------------------------------------
_cursor_bind_capture_area_size 400   maximum size of the cursor bind capture area
_cursor_bind_capture_interval  900   interval (in seconds) between two bind capture for a cursor

V$SQL 뷰의 bind_data 값으로도 바인드 변수 값을 조회할 수 있다. 변수명이 반환되지 않아 활용도가 높지 않다.

-- 4
SELECT b.name, b.position, b.dup_position, b.datatype_string, b.value_string
  FROM v$sql a
     , TABLE (DBMS_SQLTUNE.EXTRACT_BINDS (a.bind_data)) b
 WHERE a.sql_id = 'ckrnp9t0x37xh'
   AND a.child_number = 0;

NAME POSITION DUP_POSITION DATATYPE_STRING VALUE_STRING
---- -------- ------------ --------------- ------------
            1              NUMBER          3
            2
            3
            4              NUMBER          1
            5              NUMBER          2
            6            1 NUMBER          3

6 행이 선택되었습니다.

DBMS_XPLAN.DISPLAY_CURSOR 함수에 PEEKED_BINDS 포맷을 지정해도 바인드 변수 값을 조회할 수 있다. V$SQL_PLAN 뷰의 other_xml 값을 반환한다. 바인드 변수 값이 RAW 값으로 반환되고, 누락된 정보가 많아 활용도가 높지 않다.

-- 5-1
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('ckrnp9t0x37xh', 0, format => 'PEEKED_BINDS'));

Peeked Binds (identified by position):
--------------------------------------

   4 - :V1 (NUMBER): 1
   5 - :V2 (NUMBER): 2
   6 - :V3 (NUMBER, Primary=1)

-- 5-2
SELECT b.*
  FROM v$sql_plan a
     , XMLTABLE ('other_xml/peeked_binds/bind'
                 PASSING XMLTYPE (a.other_xml)
                 COLUMNS nam VARCHAR2(30) PATH '@nam'
                       , pos NUMBER PATH '@pos'
                       , dty NUMBER PATH '@dty'
                       , pre NUMBER PATH '@pre'
                       , scl NUMBER PATH '@scl'
                       , mxl NUMBER PATH '@mxl'
                       , val RAW(2000) PATH 'text()') b
 WHERE a.sql_id = 'ckrnp9t0x37xh'
   AND a.child_number = 0
   AND a.other_xml IS NOT NULL;

NAM POS DTY PRE SCL MXL VAL
--- --- --- --- --- --- ----
:V1   4   2   0   0 22  C102
:V2   5   2   0   0 22  C103
:V3   6   2   0   0 22

3 행이 선택되었습니다.

SQL Monitor에 모니터링된 쿼리라면 DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수로 바인드 값을 조회할 수 있다. V$SQL_MONITOR 뷰의 binds_xml 값을 반환한다. 역시 활용도는 높지 않다.

-- 6-1
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR ('ckrnp9t0x37xh') AS report FROM DUAL;

Binds
====================================
| Name | Position |  Type  | Value |
====================================
| :V1  |        4 | NUMBER | 1     |
| :V2  |        5 | NUMBER | 2     |
| :V3  |        1 | NUMBER | 3     |
====================================

-- 6-2
SELECT b.*
  FROM v$sql_monitor a
     , XMLTABLE ('binds/bind'
                 PASSING XMLTYPE (a.binds_xml)
                 COLUMNS name   VARCHAR2(128)  PATH '@name'
                       , pos    NUMBER         PATH '@pos'
                       , dty    NUMBER         PATH '@dty'
                       , dtystr VARCHAR2(128)  PATH '@dtystr'
                       , maxlen NUMBER         PATH '@maxlen'
                       , csid   NUMBER         PATH '@csid'
                       , len    NUMBER         PATH '@len'
                       , val    VARCHAR2(4000) PATH 'text()') b
 WHERE a.sql_id = 'ckrnp9t0x37xh';

NAME POS DTY DTYSTR MAXLEN CSID LEN VAL
---- --- --- ------ ------ ---- --- ---
:V1    4   2 NUMBER 22            2 1
:V2    5   2 NUMBER 22            2 2
:V3    1   2 NUMBER 22            2 3

3 행이 선택되었습니다.