|
对于带有绑定变量的SQL语句ORACLE在第一次执行的时候将会进行绑定变量的PEEK是否PEEK是否隐含参数_OPTIM_PEEK_USER_BINDS控制默认为TRUEORACLE在第一次 PEEK之后将绑定变量的值放到V$SQLBIND_DATA列里如下 SQL> SELECT COUNT() FROM TEST; COUNT()
SQL> DESC TEST; Name Null? Type OWNER VARCHAR() NAME VARCHAR() TYPE VARCHAR( CHAR) LINE NUMBER TEXT VARCHAR() SQL> SELECT LINECOUNT() FROM TEST GROUP BY LINE; LINE COUNT() SQL> VARIABLE LINE NUMBER SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> COL SQL_ID FORMAT A SQL> COL BIND_DATA FORMAT A SQL> SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT=SELECT COUNT() FROM TEST WHERE LINE=:LINE; SQL_ID BIND_DATA abhfnxqgrr BEDAACEBCC SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNEEXTRACT_BINDS(BEDAACEBCC)); VALUE_STRING
SQL> SELECT * FROM TABLE(DBMS_XPLANDISPLAY_CURSOR(abhfnxqgrr)); PLAN_TABLE_OUTPUT
SQL_ID abhfnxqgrr child number
SELECT COUNT() FROM TEST WHERE LINE=:LINE Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost |
| | SELECT STATEMENT | | | | | | | SORT AGGREGATE | | | | | |* | INDEX FAST FULL SCAN| IDX_TEST_ | K| K| |
Predicate Information (identified by operation id):
filter(LINE=:LINE) Note
cpu costing is off (consider enabling it) rows selected SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT=SELECT COUNT() FROM TEST WHERE LINE=:LINE; SQL_ID BIND_DATA abhfnxqgrr BEDAACECCCC SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNEEXTRACT_BINDS(BEDAACECCCC)); VALUE_STRING
SQL> PRINT: LINE
SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> SELECT SQL_ID BIND_DATA FROM V$SQL WHERE SQL_TEXT=SELECT COUNT() FROM TEST WHERE LINE=:LINE; SQL_ID BIND_DATA abhfnxqgrr BEDAACECCCC SQL> SELECT VALUE_STRING FROM TABLE(DBMS_SQLTUNEEXTRACT_BINDS(BEDAACECCCC)); VALUE_STRING
对于随后的绑定变量的捕获ORACLE将放在V$SQL_BIND_CATPURE里默认每隔秒捕获一次捕获间隔受隐含参数_cursor_bind_capture_interval的控制 为了演示的方便我们把这个间隔设小一点 SQL> alter system set _cursor_bind_capture_interval=; System altered SQL> exec :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT COUNT() FROM TEST WHERE LINE=:LINE; COUNT()
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID=abhfnxqgrr; VALUE_STRING
SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID=abhfnxqgrr; VALUE_STRING
SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID=abhfnxqgrr; VALUE_STRING
SQL> EXEC :LINE:=; PL/SQL procedure successfully completed SQL> SELECT VALUE_STRING FROM V$SQL_BIND_CAPTURE WHERE SQL_ID=abhfnxqgrr; VALUE_STRING
不知道为什么有些捕获不到 |
