对于带有绑定变量的SQL语句,ORACLE在第一次执行的时候,将会进行绑定变量的PEEK。是否PEEK是否隐含参数_OPTIM_PEEK_USER_BINDS控制,默认为TRUE.。ORACLE在第一次PEEK之后,将绑定变量的值放到V$SQL.BIND_DATA列里。如下:
SQL> SELECT COUNT(1) FROM TEST;
COUNT(1)
----------
1202416
SQL> DESC TEST;
Name Null? Type
----------------------------------------------------- -------- -------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12 CHAR)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> SELECT LINE,COUNT(1) FROM TEST GROUP BY LINE;
LINE COUNT(1)
---------- ----------
2 100
1 1202316
SQL> VARIABLE LINE NUMBER
SQL> EXEC :LINE:=1;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
1202316
SQL> COL SQL_ID FORMAT A20
SQL> COL BIND_DATA FORMAT A60
SQL> SELECT SQL_ID ,BIND_DATA FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM TEST WHERE LINE=:LINE';
SQL_ID BIND_DATA
-------------------- ------------------------------------------------------------
abhf6n1xqgrr0 BEDA0A2004004C33EB45000101C0021602C102
SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE.EXTRACT_BINDS('BEDA0A2004004C33EB45000101C0021602C102'));
VALUE_STRING
-----------------------------------------------------------------------------------
1
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('abhf6n1xqgrr0'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID abhf6n1xqgrr0, child number 0
-------------------------------------
SELECT COUNT(1) FROM TEST WHERE LINE=:LINE
Plan hash value: 704220325
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 355 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST_01 | 601K| 1762K| 355 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("LINE"=:LINE)
Note
-----
- cpu costing is off (consider enabling it)
23 rows selected.
SQL> EXEC :LINE:=2;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
100
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
100
SQL> SELECT SQL_ID ,BIND_DATA FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM TEST WHERE LINE=:LINE';
SQL_ID BIND_DATA
-------------------- ------------------------------------------------------------
abhf6n1xqgrr0 BEDA0A2004004C33ECC2000101C0021602C103
SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE.EXTRACT_BINDS('BEDA0A2004004C33ECC2000101C0021602C103'));
VALUE_STRING
-----------------------------------------------------------------------------------
2
SQL> PRINT:
LINE
----------
2
SQL> EXEC :LINE:=1;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
1202316
SQL> SELECT SQL_ID ,BIND_DATA FROM V$SQL WHERE SQL_TEXT='SELECT COUNT(1) FROM TEST WHERE LINE=:LINE';
SQL_ID BIND_DATA
-------------------- ------------------------------------------------------------
abhf6n1xqgrr0 BEDA0A2004004C33ECC2000101C0021602C103
SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNE.EXTRACT_BINDS('BEDA0A2004004C33ECC2000101C0021602C103'));
VALUE_STRING
-----------------------------------------------------------------------------------
2
对于随后的绑定变量的捕获,ORACLE将放在V$SQL_BIND_CATPURE里。默认每隔900秒捕获一次,捕获间隔受隐含参数_cursor_bind_capture_interval的控制。
为了演示的方便,我们把这个间隔设小一点。
SQL> alter system set "_cursor_bind_capture_interval"=1;
System altered.
SQL> exec :LINE:=3;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
0
SQL> EXEC :LINE:=4;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
0
SQL> EXEC :LINE:=5;
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(1) FROM TEST WHERE LINE=:LINE;
COUNT(1)
----------
0
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='abhf6n1xqgrr0';
VALUE_STRING
-----------------------------------------------------------------------------------
5
SQL> EXEC :LINE:=6;
PL/SQL procedure successfully completed.
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='abhf6n1xqgrr0';
VALUE_STRING
-----------------------------------------------------------------------------------
5
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='abhf6n1xqgrr0';
VALUE_STRING
-----------------------------------------------------------------------------------
5
SQL> EXEC :LINE:=7;
PL/SQL procedure successfully completed.
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='abhf6n1xqgrr0';
VALUE_STRING
-----------------------------------------------------------------------------------
5
不知道为什么,有些捕获不到。