ITPub博客

首页 > 数据库 > Oracle > [20150812]关于抓取绑定变量.txt

[20150812]关于抓取绑定变量.txt

原创 Oracle 作者:lfree 时间:2015-08-12 10:07:08 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292303