獲取sql繫結變數的值

工作中有時候需要解析一下SQL的繫結變數以分析SQL邏輯，通過wrh$_sqlstat的BIND_DATA可以將Oracle儲存的繫結變數分析出來。

使用DBMS_SQLTUNE包可以很容易實現這個目的：

 select dbms_sqltune.extract_bind(w.bind_data, 1).value_string 
         ||'-'|| dbms_sqltune.extract_bind(w.bind_data, 2).value_string 
         ||'-'|| dbms_sqltune.extract_bind(w.bind_data, 3).value_string 
         ||'-'|| dbms_sqltune.extract_bind(w.bind_data, 4).value_string 
         ||'-'|| dbms_sqltune.extract_bind(w.bind_data, 5).value_string 
         ||'-'|| dbms_sqltune.extract_bind(w.bind_data, 6).value_string 
         bind_data ,q.SQL_TEXT 
       FROM   sys.wrh$_sqlstat w,v$sql q 
       WHERE w.sql_id=q.SQL_ID 
       AND  w.sql_id = '620g972n1n06s'; 
BIND_DATA                     SQL_TEXT
--------------------------------------------------------------------------------
3762031-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>3761374-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>3761728-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>3756976-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>3761672-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>3756976-2---- select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_Id as addressId from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date desc ) rr where rownum<:>

 

還有一種方法就是查詢檢視：v$sql_bind_capture

 

select a.HASH_VALUE,a.SQL_ID,a.CHILD_NUMBER,a.NAME,a.POSITION,a.DATATYPE_STRING,a.VALUE_STRING from v$sql_bind_capture a where a.sql_id = '620g972n1n06s';

 

HASH_VALUE SQL_ID        CHILD_NUMBER NAME                                                           POSITION DATATYPE_STRING                VALUE_STRING
---------- ------------- ------------ ------------------------------------------------------------ ---------- ------------------------------ --------------------------------------------------------------------------------
2820276440 620g972n1n06s            8 :SYS_B_0                                                              1 NUMBER                         3757081
2820276440 620g972n1n06s            8 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            7 :SYS_B_0                                                              1 NUMBER                         3756149
2820276440 620g972n1n06s            7 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            6 :SYS_B_0                                                              1 NUMBER                         3761672
2820276440 620g972n1n06s            6 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            5 :SYS_B_0                                                              1 NUMBER                         3761728
2820276440 620g972n1n06s            5 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            4 :SYS_B_0                                                              1 NUMBER                         3757069
2820276440 620g972n1n06s            4 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            3 :SYS_B_0                                                              1 NUMBER                         3761485
2820276440 620g972n1n06s            3 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            2 :SYS_B_0                                                              1 NUMBER                         3761374
2820276440 620g972n1n06s            2 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            1 :SYS_B_0                                                              1 NUMBER                         3761748
2820276440 620g972n1n06s            1 :SYS_B_1                                                              2 NUMBER                         2
2820276440 620g972n1n06s            0 :SYS_B_0                                                              1 NUMBER                         3756994
2820276440 620g972n1n06s            0 :SYS_B_1                                                              2 NUMBER                         2
 

但是這個檢視有個侷限，它的記錄頻率受_cursor_bind_capture_interval 隱含引數控制，預設值900，表示每900秒記錄一次繫結值，可以通過alter system set "_cursor_bind_capture_interval"=10;
來修改，在特殊情況下用於排查問題可能會用到，生產環境儘量不要這麼做，會增加負荷。

 

如果想知道第一次peeking時執行計劃所用的繫結變數的值，則用以下sql：

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('620g972n1n06s', 0, 'ADVANCED'));

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  620g972n1n06s, child number 0
-------------------------------------
select rr.orderId,rr.addressId from ( select oi.order_Id as orderId,oi.address_I
from m_order_info oi where oi.member_id =:"SYS_B_0" order by oi.comfirm_Date des
rownum<:>Plan hash value: 3210503414
--------------------------------------------------------------------------------
| Id  | Operation                      | Name                   | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |       |
|*  1 |  COUNT STOPKEY                 |                        |       |
|   2 |   VIEW                         |                        |     2 |    52
|*  3 |    SORT ORDER BY STOPKEY       |                        |     2 |    48
|   4 |     TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO           |     2 |    48
|*  5 |      INDEX RANGE SCAN          | M_ORDER_INFO_MEMBER_ID |     2 |
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / RR@SEL$1
   3 - SEL$2
   4 - SEL$2 / OI@SEL$2
   5 - SEL$2 / OI@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "RR"@"SEL$1")
      INDEX_RS_ASC(@"SEL$2" "OI"@"SEL$2" ("M_ORDER_INFO"."MEMBER_ID"))
      END_OUTLINE_DATA
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  */
Peeked Binds (identified by position):
--------------------------------------
   1 - :SYS_B_0 (NUMBER): 3756994
   2 - :SYS_B_1 (NUMBER): 2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<:sys_b_1>   3 - filter(ROWNUM<:sys_b_1>   5 - access("OI"."MEMBER_ID"=:SYS_B_0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "RR"."ORDERID"[NUMBER,22], "RR"."ADDRESSID"[NUMBER,22]
   2 - "RR"."ORDERID"[NUMBER,22], "RR"."ADDRESSID"[NUMBER,22]
   3 - (#keys=1) INTERNAL_FUNCTION("OI"."COMFIRM_DATE")[7], "OI"."ORDER_ID"[NUMB
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
       "OI"."ADDRESS_ID"[NUMBER,22]
   4 - "OI"."ORDER_ID"[NUMBER,22], "OI"."COMFIRM_DATE"[DATE,7], "OI"."ADDRESS_ID
   5 - "OI".ROWID[ROWID,10]
 
66 rows selected

 

在此記錄一下。