[20150812]關於抓取繫結變數.txt

lfree發表於2015-08-12
變數

[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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章