[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

时间： 2024-07-31 04:14:17

[20150812]关于抓取绑定变量.txt的相关文章

[20170929]&amp; 代替冒号绑定变量.txt

[20170929]& 代替冒号绑定变量.txt --//我昨天看链接,http://orasql.org/2017/09/27/ampersand-instead-of-colon-for-bind-variables/ --//重复测试: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -

[20120726]建立约束和使用绑定变量.txt

[20120726]建立约束和使用绑定变量.txt 昨天检查awr报表文件,发现:select condition from cdef$ where rowid=:1这条语句执行次数很高,因为查询的where条件使用rowid=:1,应该不会是用户的程序执行,而是某种递归的调用. 直接在google输入select condition from cdef$ where rowid=:1,发现如下链接:http://jonathanlewis.wordpress.com/2006/12/14/co

[20121102]PLSQL中的绑定变量.txt

[20121102]PLSQL中的绑定变量.txt     以前曾经遇到一个sql语句提交给开发,开发没有找到,最终确定是问题语句在PLSQL中,实际上PLSQL转化为大写, 加上自己没有注意.实际上SQL语句在PLSQL中,一些好像被"格式化一样",我举一个例子: 1.测试环境: SQL> select * from v$version where rownum BANNER ---------------------------------------------------

[20130918]12c FETCH FIRST和绑定变量.txt

[20130918]12c FETCH FIRST和绑定变量.txt http://connormcdonald.wordpress.com/2013/09/11/12c-fetch-first/ 重复测试: @ver BANNER                                                                               CON_ID ------------------------------------------------

百度为什么抓取被robots.txt禁止的文件

中介交易 http://www.aliyun.com/zixun/aggregation/6858.html">SEO诊断 淘宝客 云主机 技术大厅 前段时间互联网界对360不遵守robots.txt文件进行了群批,百度也强调自己是遵守robots.txt协议的.但从最近对某网站的分析来看,百度对robots.txt文件的遵守很不彻底.11月15号,我写了一篇文章各搜索蜘蛛对robots.txt文件改动的反应, 因为在11月13号左右,我在我从事的网站上放了robots.txt文件,明确禁

[20140212]linux下使用tcpdump抓取sql语句

[20140212]linu下使用tcpdump抓取sql语句.txt 我们生产系统问题多多,经常要跟踪用户执行的sql语句,当出现问题时要跟踪比较麻烦,我需要一个快捷的方式"看到"用户执行的sql语 句,想到了tcpdump抓包软件. 我测试建立shell脚本如下: #! /bin/bash /usr/sbin/tcpdump -l -i eth0 -s 16384 -A -nn src host $1 and dst port 1521 --说明: -- -l     Make s

[20160224]绑定变量的分配长度.txt

[20160224]绑定变量的分配长度.txt --如果绑定变量中字符串分配占用空间的长度变化,oracle会建立子光标. --昨天被别人问一个问题,通过例子来说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------

零基础写python爬虫之抓取百度贴吧并存储到本地txt文件改进版_python

百度贴吧的爬虫制作和糗百的爬虫制作原理基本相同,都是通过查看源码扣出关键数据,然后将其存储到本地txt文件. 项目内容: 用Python写的百度贴吧的网络爬虫. 使用方法: 新建一个BugBaidu.py文件,然后将代码复制到里面后,双击运行. 程序功能: 将贴吧中楼主发布的内容打包txt存储到本地. 原理解释: 首先,先浏览一下某一条贴吧,点击只看楼主并点击第二页之后url发生了一点变化,变成了: http://tieba.baidu.com/p/2296712428?see_lz=1&pn=

了解robots.txt的用法 优化搜索引擎抓取和索引

通过给网站设置适当的robots.txt对Google和百度seo优化的作用是很明显的.WordPress博客网站也一样. 我们先看看robots.txt是什么,有什么作用? robots.txt是什么? 我们都知道txt后缀的文件是纯文本文档,robots是机器人的意思,所以顾名思义,robots.txt文件也就是给搜索引擎蜘蛛这个机器人看 的纯文本文件.robots.txt是搜索引擎公认遵循的一个规范文 档,它告诉Google.百度等搜索引擎哪些网页允许抓取.索引并在搜索结果中显示,哪些网页