熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

綁定變量的獲取

2013-11-13 16:22:52  來源: Oracle 

  對於帶有綁定變量的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

  

  

  不知道為什麼有些捕獲不到


From:http://tw.wingwit.com/Article/program/Oracle/201311/18232.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.