watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in , How to get the implemented goals SQL The value of the bound variable in ?


     
          Answer section          


When Oracle Parsing and executing targets with bound variables SQL when , If one of the following two conditions is satisfied , Then the SQL The specific input value of the bound variable in will be Oracle Capture :

l  When a target containing bound variables SQL When executed in the form of hard parsing .

l  When a target containing bound variables SQL When repeated in soft parsing or soft parsing ,Oracle By default, at least the interval 15 Only once a minute . This 15 Minutes are affected by implicit parameters “_CURSOR_BIND_CAPTURE_INTERVAL” control , The default value is 900 second , namely 15 minute .

 [email protected] > SET PAGESIZE 9999
 [email protected] > SET LINE 9999
 [email protected] > COL NAME FORMAT A40
 [email protected] > COL KSPPDESC FORMAT A60
 [email protected] > COL KSPPSTVL FORMAT A20
 [email protected] > SELECT A.INDX,
 7  2         A.KSPPINM NAME,
 8  3         A.KSPPDESC,
 9  4         B.KSPPSTVL 
10  5  FROM   X$KSPPI  A,
11  6         X$KSPPCV B
12  7  WHERE  A.INDX = B.INDX
13  8  AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%');
14Enter value for parameter: _CURSOR_BIND_CAPTURE_INTERVAL
15old   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%')
16new   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%_CURSOR_BIND_CAPTURE_INTERVAL%')
17
18      INDX NAME                                     KSPPDESC                                                     KSPPSTVL
19---------- ---------------------------------------- ------------------------------------------------------------ --------------------
20      2140 _cursor_bind_capture_interval            interval (in seconds) between two bind capture for a cursor  900
     


It should be noted that ,Oracle Only those located in the target will be captured SQL Of WHERE The specific input value of the binding variable in the condition , And for those that use bound variables INSERT sentence , No matter what INSERT Whether the statement is executed in the way of hard parsing ,Oracle Never capture INSERT Of the statement VALUES Clause corresponding to the specific input value of the bound variable .

Query view V$SQL_BIND_CAPTURE or V$SQL You can get the implemented goals SQL The specific input value of the bound variable in . If V$SQL_BIND_CAPTURE We can't find , Then there is a possible correspondence Shared Cursor Has gone from Shared Pool It's been cleared from the library , At this time, you can try from AWR Related data dictionary table DBA_HIST_SQLSTAT or DBA_HIST_SQLBIND Query in . in addition , It can also be done through DBMS_XPLAN.DISPLAY_CURSOR and 10046 To get the value of the bound variable .

Inquire about SQL The statement is as follows :

 1COL SQL_ID FOR A14;
 2COL SQL_TEXT FOR A32;
 3COL HASH_VALUE FOR 99999999999;
 4COL BIND_DATA FOR A32;
 5SELECT SQL_ID          
 6      ,SQL_TEXT
 7      ,LITERAL_HASH_VALUE
 8      ,HASH_VALUE
 9      ,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND_DATA
10FROM V$SQL
11WHERE SQL_TEXT LIKE ''SELECT * FROM TEST%';
12
13
14COL SQL_ID FOR A14;
15COL SQL_TEXT FOR A32;
16COL HASH_VALUE FOR 99999999999;
17COL BIND_DATA FOR A32;
18SELECT SQL_ID          
19      ,SQL_TEXT
20      ,LITERAL_HASH_VALUE
21      ,HASH_VALUE
22      ,DBMS_SQLTUNE.EXTRACT_BIND(BIND_DATA,1).VALUE_STRING BIND_DATA
23FROM V$SQL
24WHERE SQL_TEXT LIKE 'SELECT * FROM TEST%';
25
26SELECT D.SQL_ID,
27       D.CHILD_NUMBER,
28       D.CHILD_ADDRESS,
29       D.NAME,
30       D.POSITION,
31       D.DATATYPE,
32       D.DATATYPE_STRING,
33       D.MAX_LENGTH,
34       D.WAS_CAPTURED,
35       D.LAST_CAPTURED,
36       D.VALUE_STRING
37  FROM V$SQL_BIND_CAPTURE D
38 WHERE D.SQL_ID = '01g03pruhphqc'
39 ORDER BY D.CHILD_NUMBER, D.POSITION;
40
41SELECT D.SQL_ID,
42        D.NAME,
43        D.POSITION,
44        D.DATATYPE,
45        D.DATATYPE_STRING,
46        D.MAX_LENGTH,
47        D.WAS_CAPTURED,
48        D.LAST_CAPTURED,
49        D.VALUE_STRING
50  FROM DBA_HIST_SQLBIND D;
51
52SELECT D.SNAP_ID,
53       DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA, 1).VALUE_STRING BIND1,
54       DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA, 1).VALUE_STRING BIND2
55  FROM DBA_HIST_SQLSTAT D
56 WHERE D.SQL_ID = '01g03pruhphqc';
57
58SELECT D.SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,D.NAME,D.POSITION,D.DATATYPE, D.DATATYPE_STRING,D.MAX_LENGTH,D.WAS_CAPTURED,D.LAST_CAPTURED,D.VALUE_STRING FROM V$SQL_BIND_CAPTURE D WHERE D.SQL_ID = 'aug0d49nzbgtq' ORDER BY D.CHILD_NUMBER,D.POSITION;
59
60SELECT D.SNAP_ID,DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA,1).VALUE_STRING BIND1,DBMS_SQLTUNE.EXTRACT_BIND(D.BIND_DATA,1).VALUE_STRING BIND2 FROM DBA_HIST_SQLSTAT D WHERE D.SQL_ID = 'aug0d49nzbgtq';
61
62SELECT * FROM DBA_HIST_SQLBIND  D WHERE D.SQL_ID = 'aug0d49nzbgtq';
63
64SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1t2r2p48w4p0g', 0, 'ADVANCED'));
65
66ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'; --LEVEL=4  Means to enable SQL_TRACE And capture the bound variables in the trace file .
67
     

The test example is as follows :

 1CREATE TABLE T_BG_20170610_LHR(N NUMBER(10),V VARCHAR2(3000));
 2--SQL_TEXT1:  Hard parsing
 3DECLARE
 4  N NUMBER(10) :=1;   -- Distribute 22 Byte memory space
 5  V VARCHAR2(32) :='XIAOMAIMIAO1';   -- Distribute 32 Byte memory space
 6BEGIN
 7  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
 8  COMMIT;
 9END;
10/
11--SQL_TEXT2:  Hard parsing
12DECLARE
13  N NUMBER(10) :=2;  -- Distribute 22 Byte memory space
14  V VARCHAR2(33) :='XIAOMAIMIAO2'; -- Distribute 128 Byte memory space
15BEGIN
16  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
17  COMMIT;
18END;
19/
20
21--SQL_TEXT3:   Hard parsing
22DECLARE
23  N NUMBER(10) :=3;  -- Distribute 22 Byte memory space
24  V VARCHAR2(129) :='XIAOMAIMIAO3'; -- Distribute 2000 Byte memory space
25BEGIN
26  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
27  COMMIT;
28END;
29/
30
31--SQL_TEXT4:  Soft parsing
32DECLARE
33  N NUMBER(10) :=4;  -- Distribute 22 Byte memory space
34  V VARCHAR2(2001) :='XIAOMAIMIAO4';  -- Distribute 2000 Byte memory space
35BEGIN
36  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
37  COMMIT;
38END;
39/
40--SQL_TEXT5:  Soft parsing
41DECLARE
42  N NUMBER(10) :=5;  -- Distribute 22 Byte memory space
43  V VARCHAR2(32767) :='XIAOMAIMIAO5';  -- Distribute 2000 Byte memory space
44BEGIN
45  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
46  COMMIT;
47END;
48/
49
50--SQL_TEXT6:  Hard parsing
51DECLARE
52  N NUMBER(10) :=6;  -- Distribute 22 Byte memory space
53  V VARCHAR2(32767) :=RPAD('XIAOMAIMIAO6',2002,'8');  -- String length is 2002, Distribute 4000 Byte memory space
54BEGIN
55  EXECUTE IMMEDIATE 'INSERT INTO T_BG_20170610_LHR SELECT * FROM T_BG_20170610_LHR WHERE N=:N AND V=:V ' USING N, V;
56  COMMIT;
57END;
58/
     

Query the input value of the bound variable :

 [email protected] > COL NAME FORMAT A6
 [email protected] > COL VALUE_STRING FORMAT A15
 [email protected] > SELECT D.SQL_ID,D.CHILD_NUMBER,D.CHILD_ADDRESS,D.NAME,D.POSITION,D.DATATYPE, D.DATATYPE_STRING,D.MAX_LENGTH,D.WAS_CAPTURED,D.LAST_CAPTURED,D.VALUE_STRING FROM V$SQL_BIND_CAPTURE D WHERE D.SQL_ID = 'aug0d49nzbgtq' ORDER BY D.CHILD_NUMBER,D.POSITION;
 4
 5SQL_ID        CHILD_NUMBER CHILD_ADDRESS    NAME     POSITION   DATATYPE DATATYPE_STRING                MAX_LENGTH WAS LAST_CAPTURED       VALUE_STRING
 6------------- ------------ ---------------- ------ ---------- ---------- ------------------------------ ---------- --- ------------------- ---------------
 7aug0d49nzbgtq            0 0000000095C56BB0 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 1
 8aug0d49nzbgtq            0 0000000095C56BB0 :V              2          1 VARCHAR2(32)                           32 YES 2017-06-10 11:48:47 XIAOMAIMIAO1
 9aug0d49nzbgtq            1 0000000095C5ECF0 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 2
10aug0d49nzbgtq            1 0000000095C5ECF0 :V              2          1 VARCHAR2(128)                         128 YES 2017-06-10 11:48:47 XIAOMAIMIAO2
11aug0d49nzbgtq            2 0000000095C66750 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:47 3
12aug0d49nzbgtq            2 0000000095C66750 :V              2          1 VARCHAR2(2000)                       2000 YES 2017-06-10 11:48:47 XIAOMAIMIAO3
13aug0d49nzbgtq            3 0000000095C22880 :N              1          2 NUMBER                                 22 YES 2017-06-10 11:48:48 6
14aug0d49nzbgtq            3 0000000095C22880 :V              2          1 VARCHAR2(4000)                       4000 NO