工作中有时候需要解析一下SQL的绑定变量以分析SQL逻辑,通过wrh$_sqlstat的BIND_DATA可以将Oracle存储的绑定变量分析出来。
使用DBMS_SQLTUNE包可以很容易实现这个目的:
使用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
---------- ------------- ------------ ------------------------------------------------------------ ---------- ------------------------------ --------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
在此记录一下。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-713797/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-713797/