Oracle/Tuning2018. 11. 9. 12:11

바인드 변수 값 조회

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

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

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

아래와 같이 쿼리를 수행하고 SQL ID와 child 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
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 절에 사용한 바인드 변수 값만 기록된다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 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 행이 선택되었습니다.


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

1
2
3
4
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 값으로도 바인드 변수 값을 조회할 수 있다. 변수명이 반환되지 않아 활용도가 높지 않다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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 값으로 반환되고, 누락된 정보가 많아 활용도가 높지 않다.

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
-- 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 값을 반환한다. 역시 활용도는 높지 않다.

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
-- 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 행이 선택되었습니다.


저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

다수 테이블에 대한 OR 조건  (0) 2018.12.01
ROWNUM = 1 패턴  (0) 2018.11.28
'PLAN_TABLE' is old version  (0) 2018.11.08
스칼라 서브 쿼리의 실행 계획 #2 - 소요 시간과 블록 I/O  (0) 2018.11.07
스칼라 서브 쿼리의 실행 계획 #1 - 표시 순서  (0) 2018.11.06
Posted by 정희락_

티스토리툴바