[20150812]關於抓取繫結變數.txt
[20150812]關於抓取繫結變數.txt
--透過檢視v$sql_bind_capture以及DBA_HIST_SQLBIND可以抓取到sql語句的繫結變數。受到一些引數的限制，曾經寫過一篇：
[20130410]v$sql_bind_capture和隱含引數_bind_capture_area_size.txt
http://blog.itpub.net/267265/viewspace-758175/
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @hide bind_capture
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ----------------------
_cursor_bind_capture_area_size maximum size of the cursor bind capture area TRUE 400 400
_cursor_bind_capture_interval interval (in seconds) between two bind capture for a cursor TRUE 900 900
cursor_bind_capture_destination Allowed destination for captured bind variables TRUE memory+disk memory+disk
--從引數可以發現_cursor_bind_capture_area_size表示抓取變數區域大小，如果你繫結變數很多，或者佔用空間很大，400位元組遠遠不足。可以參考我以前的連結。
-- _cursor_bind_capture_interval 很明顯是時間間隔，設定太小估計對資訊有一定影響，太大可能遺漏一些重要有問題的引數。
--不過第1次執行生成新的游標，oracle一定會抓取的。可以透過例子來驗證：
1.建立測試環境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id , cast('test' as varchar2(20)) name from dual connect by level<=20;
Table created.
--分析表。
2.測試：
SCOTT@test> variable x number ;
SCOTT@test> exec :x := 1;
PL/SQL procedure successfully completed.
SCOTT@test> select * from t where id=:x;
ID NAME
---------- ----------------------------------------
1 test
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
1 - filter("ID"=:X)
$ cat bind_cap.sql
set verify off
column value_string format a50
column datatype_string format a15
break on sql_id on child_number skip 1
select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1;
SELECT sql_id,
child_number,
was_captured,
name,
position,
max_length,
last_captured,
datatype_string,
DECODE (
datatype_string,
'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'),
'yyyy/mm/dd hh24:mi:ss'),
value_string)
value_string
FROM v$sql_bind_capture
WHERE sql_id = '&1' and was_captured='YES'
order by child_number,was_captured,position;
break on sql_id on child_number skip 0
SCOTT@test> @bind_cap 3yxwagyspybax
C200
----------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ----- ---------- ---------- ------------------- --------------- ---------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:35:25 NUMBER 1
--很明顯，我第一次執行一定會抓取。
3.如果我修改某個引數一定會生成新的游標，這樣應該也會抓取變數：
SCOTT@test> alter session set optimizer_index_caching=10;
Session altered.
SCOTT@test> exec :x := 42;
PL/SQL procedure successfully completed.
SCOTT@test> select * from t where id=:x;
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 42
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
SCOTT@test> @bind_cap 3yxwagyspybax
C200
---------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- ------ ---------- ---------- ------------------- --------------- --------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:35:25 NUMBER 1
1 YES :X 1 22 2015-08-12 09:41:25 NUMBER 42
--可以發現生成新的子關閉，oracle也會抓取。
4.其它引數:
SCOTT@test> @hide _optim_peek_user_binds
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------- ----------------------------- ---------------------- ---------------------- ----------------------
_optim_peek_user_binds enable peeking of user binds TRUE TRUE TRUE
--退出等上15分鐘，也就是900秒。
SCOTT@test> variable x number ;
SCOTT@test> exec :x := 34;
PL/SQL procedure successfully completed.
SCOTT@test> select sysdate from dual;
SYSDATE
-------------------
2015-08-12 09:56:25
--已經過了900秒。
SCOTT@test> select * from t where id=:x;
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
SCOTT@test> @bind_cap 3yxwagyspybax
C200
---------------------------
select * from t where id=:x
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------------
3yxwagyspybax 0 YES :X 1 22 2015-08-12 09:56:46 NUMBER 34
1 YES :X 1 22 2015-08-12 09:41:25 NUMBER 42
--可以發現1個現象：
--使用dbms_xplan.display_cursor檢視執行計劃的繫結變數還是第一次執行的，並沒有變化。
--而查詢v$sql_bind_capture時已經發生了變化。
--實際上dbms_xplan.display_cursor看到的來源於v$sql_plan:
SCOTT@test> select child_number,other_xml from v$sql_plan where sql_id='3yxwagyspybax' order by 1;
CHILD_NUMBER OTHER_XML
------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><peeked_binds><b
ind nam=":X" pos="1" dty="2" pre="0" scl="0" mxl="22">c102</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]
></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data
></other_xml>
1
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![CDATA["SCOTT"]]></info><info type="plan_hash">1601196873</info><info type="plan_hash_2">2498539100</info><peeked_binds><b
ind nam=":X" pos="1" dty="2" pre="0" scl="0" mxl="22">c12b</bind></peeked_binds><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]
></hint><hint><![CDATA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[OPT_PARAM('optimizer_index_caching' 10)]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><
hint><![CDATA[FULL(@"SEL$1" "T"@"SEL$1")]]></hint></outline_data></other_xml>
--c102 就是數字1.
SCOTT@test> select dump(1,16),dump(42,16) from dual ;
DUMP(1,16) DUMP(42,16)
----------------- ------------------
Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,2b
--附：我的dpc.sql指令碼：：
$ cat dpc.sql
set verify off
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1768955/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於繫結變數變數
- 關於繫結變數的使用變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- 關於DSS中的繫結變數變數
- 關於pl/sql中的繫結變數SQL變數
- [20200326]繫結變數抓取與NULL值.txt變數Null
- 關於sql_profile中的繫結變數SQL變數
- 關於oracle sql變數繫結提高效率OracleSQL變數
- 繫結變數變數
- [20170929]& 代替冒號繫結變數.txt變數
- [20160706]like % 繫結變數.txt變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle9i, 10g 如何抓取繫結變數的值Oracle變數
- [20171231]PLSQL使用繫結變數.txtSQL變數
- Oracle 繫結變數Oracle變數
- [20180930]in list與繫結變數個數.txt變數
- [20180912]PLSLQ與繫結變數.txt變數
- [20180930]in list與繫結變數.txt變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- [20210120]in list與繫結變數個數.txt變數
- [20160224]繫結變數的分配長度.txt變數
- [20121102]PLSQL中的繫結變數.txtSQL變數
- ORACLE 繫結變數用法總結Oracle變數
- 關於高效捕獲資料庫非繫結變數的SQL語句資料庫變數SQL
- 繫結變數引數關閉之後，oracle會如何操作變數Oracle
- 【最佳化】使用繫結變數 OR 不使用繫結變數，這不是問題！變數
- 繫結變數窺測的演變變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數（bind variable）Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數