So for binding , How to get the implemented goals SQL The value of the bound variable in ?

  When it comes to targets that have been executed and used binding variables SQL when , We have to get these targets for the crime SQL The actual input value of the bound variable in . Because only the actual input value of the bound variable is known , We can reproduce the target in the original database environment SQL The original implementation plan , Only in this way can we confirm that our adjustments to its implementation plan are targeted , It's real and effective .

        How to get the implemented goals SQL The input value of the bound variable in ? It's the query view v$sql_bind_capture. If v$sql_bind_capture We can't find , Then there is a possible correspondence shared cursor Has been age out Out shared pool 了 , This is the time to try awr repository Related data dictionary table dba_hist_sqlstat or dba_hist_sqlbind Query in .

        When Oracle Parsing and executing targets with bound variables SQL when , If the price is adjusted as follows , be SQL The specific input value of the bound variable will be Oracle Capture , Through view v$sql_bind_capture Inquire about .

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

        b、 When a target containing bound variables SQL Soft analysis 、 When the soft parsing mode is repeatedly executed , The SQL The specific input value of the bound variable in can also be Oracle Capture , It's just that by default this capture operation Oracle At least at intervals 15 It's only done once a minute .

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 It is never captured values The specific input value of the corresponding binding variable in the sentence .

Use the following test to simulate how to get the value of the bound variable -- According to the previous binding variable hierarchical operation to create t Table environment :

SQL> select n,length(v) from t;          N  LENGTH(V)---------- ----------         1          5         2          5         3          5         4          5         5          5         6       2002  Have chosen  6  That's ok .

build t surface , Insert 6 Data entry , As shown above :

SQL> col sql_text for a40

SQL> set linesi 200

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'insert into t%';


SQL_TEXT                                 SQL_ID                     VERSION_COUNT EXECUTIONS

---------------------------------------- -------------------------- ------------- ----------

insert into t values(:n,:v)              21mycdpm39kzv                          4          6

Use what you find out sql_id Inquire about v$sql_bind_capture, You can see that insert The sentence corresponds to four child cursor Stored binding variables n and v Specific information :

SQL> col sql_id for a15

SQL> col name for a10

SQL> col position for 999

SQL> col datatype_string for a15

SQL> col last_captured for a15

SQL> col value_string for a15

SQL> select sql_id,name,position,datatype_string,last_captured,value_string
         from v$sql_bind_capture where sql_id='21mycdpm39kzv';



-------------   ------- -------- --------------- ------------- ------------

21mycdpm39kzv   :N           1 NUMBER

21mycdpm39kzv   :V           2 VARCHAR2(4000)

21mycdpm39kzv   :N           1 NUMBER

21mycdpm39kzv   :V           2 VARCHAR2(2000)

21mycdpm39kzv   :N           1 NUMBER

21mycdpm39kzv   :V           2 VARCHAR2(128)

21mycdpm39kzv   :N           1 NUMBER

21mycdpm39kzv   :V           2 VARCHAR2(32)


Have chosen 8 That's ok .

You can see the bound variables n and v In four child cursor Middle column value_string The values are all null, explain Oracle It really won't capture insert Of the statement values The specific input value of the corresponding bound variable . Of course ,awr repository Corresponding data dictionary table dba_hist_sqlstat and dba_hist_sqlbind There will not be specific input values of the above binding variables .

At this point, if you do flush shared_pool Operation to empty shared pool, From the view v$sql_bind_capture You can't find any information after the change . But it can still be done from awr repository In the corresponding data dictionary dba_hist_sqlstat and dba_hist_sqlbind Found binding variable n and v Specific capture values .