绑定变量peeking.docx
《绑定变量peeking.docx》由会员分享，可在线阅读，更多相关《绑定变量peeking.docx（21页珍藏版）》请在冰点文库上搜索。
绑定变量peeking
在我们进行对oracle系统进行优化的时候，我们希望在数据中执行的语句尽量的少出现hardparse,尽量使用绑定变量，可以减少SQL分析，节约共享池的空间，减少CPU的使用，所以对于系统中未使用的绑定语句通常告诉开发人员尽量改成使用绑定变量，当where条件上的字段分布均匀的时候，绑定变量可以达到比较好的效果，但当该字段数据分布倾斜严重时，并在该字段上收集了histogram信息时会出现什么情况呢？
下面将一一测试。
一、创建测试表
创建测试表TESTTB
createtableTESTTB（idnumber,textvarchar（100））;,
并插入98304条值为（1,'aaa'）,3条值为（2,'bbb'）。
begin
foriin1..98304loop
insertintotesttbvalues（1,'aaa'）;
endloop;
end;
begin
foriin1..3loop
insertintotesttbvalues（2,'bbb'）;
endloop;
end;
SQL>selectcount
（1）fromtesttbwhereid=1;
COUNT
（1）
----------
98304
SQL>selectcount
（1）fromtesttbwhereid=2;
COUNT
（1）
----------
3
在ID列上创建索引TESTTB_IND
SQL>createindextesttb_indontesttb（id）;
Indexcreated.
二、不使用绑定变量的情况：
1.当不对表进行分析时
ID=1时，查询大量数据，使用全表扫描，是正确的执行计划
SQL>Selectcount（*）fromtesttbwhereid=1;
COUNT（*）
----------
98304
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID40c2k892vm3hy,childnumber0
-------------------------------------
Selectcount（*）fromtesttbwhereid=1
Planhashvalue:
1163950994
-----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||||44（100）||
|1|SORTAGGREGATE||1|13|||
|*2|TABLEACCESSFULL|TESTTB|116K|1480K|44（7）|00:
00:
01|
-----------------------------------------------------------------------------
ID=2时，执行计划使用的是索引，也是正确的执行计划。
SQL>selectcount
（1）fromtesttbwhereid=2;
COUNT
（1）
----------
3
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_IDgyapcxhzu2nq9,childnumber0
-------------------------------------
selectcount
（1）fromtesttbwhereid=2
Planhashvalue:
3027455797
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||1（100）||
|1|SORTAGGREGATE||1|3|||
|*2|INDEXRANGESCAN|TESTTB_IND|3|9|1（0）|00:
00:
01|
--------------------------------------------------------------------------------
2.对表进行分析，但不生成柱状图信息时
清空sharedpool
SQL>altersystemflushshared_pool;
收集TESTTB表统计信息，但不生成柱状图信息
SQL>execDBMS_STATS.GATHER_TABLE_STATS（OWNNAME=>'SCOTT',TABNAME=>'TESTTB',CASCADE=>TRUE,ESTIMATE_PERCENT=>100）;
验证统计信息已收集
SQL>selectlast_analyzed,num_rowsfromdba_tableswheretable_name='TESTTB';
LAST_ANALYZEDNUM_ROWS
-----------------------------
2010/06/1520:
10:
2798307
ID=1时，分析后依然使用的是正确的全表扫描执行计划。
SQL>selectcount
（1）fromtesttbwhereid=1;
COUNT
（1）
----------
98304
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID6mf5jdawwgy3q,childnumber0
-------------------------------------
selectcount
（1）fromtesttbwhereid=1
Planhashvalue:
1163950994
-----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||||39（100）||
|1|SORTAGGREGATE||1|3|||
|*2|TABLEACCESSFULL|TESTTB|98304|288K|39（3）|00:
00:
01|
-----------------------------------------------------------------------------
ID=2，执行计划走的是索引，依然是正确的执行计划。
SQL>selectcount
（1）fromtesttbwhereid=2;
COUNT
（1）
----------
3
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_IDgyapcxhzu2nq9,childnumber0
-------------------------------------
selectcount
（1）fromtesttbwhereid=2
Planhashvalue:
3027455797
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||1（100）||
|1|SORTAGGREGATE||1|3|||
|*2|INDEXRANGESCAN|TESTTB_IND|3|9|1（0）|00:
00:
01|
--------------------------------------------------------------------------------
3.对表进行分析，并生成柱状图信息时
SQL>altersystemflushshared_pool;
SQL>execDBMS_STATS.GATHER_TABLE_STATS（OWNNAME=>'SCOTT',TABNAME=>'TESTTB',CASCADE=>TRUE,ESTIMATE_PERCENT=>100,METHOD_OPT=>'FORALLINDEXEDCOLUMNSsize100'）;
SQL>selectlast_analyzed,num_rowsfromdba_tableswheretable_name='TESTTB';
LAST_ANALYZEDNUM_ROWS
-----------------------------
2010/06/1520:
17:
3798307
验证存在柱状图信息
SQL>select*fromdba_tab_histogramswheretable_name='TESTTB';
OWNERTABLE_NAMECOLUMN_NAMEENDPOINT_NUMBERENDPOINT_VALUEENDPOINT_ACTUAL_VALUE
---------------------------------------------------------------------------------------------------------
SCOTTTESTTBID983041
SCOTTTESTTBID983072
SCOTTTESTTBTEXT05.056278741411
SCOTTTESTTBTEXT15.108405326374
ID=1，依然走的是正确的全表扫描的执行计划。
SQL>selectcount
（1）fromtesttbwhereid=1;
COUNT
（1）
----------
98304
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_ID6mf5jdawwgy3q,childnumber0
-------------------------------------
selectcount
（1）fromtesttbwhereid=1
Planhashvalue:
1163950994
-----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||||39（100）||
|1|SORTAGGREGATE||1|3|||
|*2|TABLEACCESSFULL|TESTTB|98304|288K|39（3）|00:
00:
01|
-----------------------------------------------------------------------------
ID=2，执行计划走的是索引，依然是正确的执行计划。
SQL>selectcount
（1）fromtesttbwhereid=2;
COUNT
（1）
----------
3
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_IDgyapcxhzu2nq9,childnumber0
-------------------------------------
selectcount
（1）fromtesttbwhereid=2
Planhashvalue:
3027455797
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||||1（100）||
|1|SORTAGGREGATE||1|3|||
|*2|INDEXRANGESCAN|TESTTB_IND|3|9|1（0）|00:
00:
01|
--------------------------------------------------------------------------------
由此可以看出当表中数据倾斜很厉害的时候，并且未使用绑定变量时，无论是否存在histogram每次执行时，都使用了正确的执行计划。
三、使用绑定变量的情况：
在oracle9i之后引入了bindpeeking，在第一次分析的时候，优化器会根据绑定变量来确定执行计划。
BINDPEEKING只有当该SQL第一次执行的时候，并且在进行HARDPARSE的时候才进行，第二次调用该SQL，就不会再次进行BINDPEEKING，继续使用上一次产生的执行计划。
以下为3种测试：
1.当不进行表分析时
删除之前收集的统计信息
SQL>execdbms_stats.delete_table_stats（OWNNAME=>'SCOTT',TABNAME=>'TESTTB',CASCADE_COLUMNS=>TRUE,CASCADE_INDEXES=>TRUE）;
SQL>selectlast_analyzed,num_rowsfromdba_tableswheretable_name='TESTTB';
LAST_ANALYZEDNUM_ROWS
-----------------------------
:
testid=1，使用正确的全表扫描的执行计划。
SQL>vartestidnumber;
SQL>exec:
testid:
=1;
SQL>selectcount（*）fromtesttbwhereid=:
testid;
COUNT（*）
----------
98304
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_IDfx0xzybczvkgv,childnumber0
-------------------------------------
selectcount（*）fromtesttbwhereid=:
testid
Planhashvalue:
1163950994
-----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||||39（100）||
|1|SORTAGGREGATE||1|13|||
|*2|TABLEACCESSFULL|TESTTB|101K|1292K|39（3）|00:
00:
01|
-----------------------------------------------------------------------------
:
testid=2时候本应走索引，因为bindpeeking的原因选择错误的执行计划----全表扫描
SQL>exec:
testid:
=2;
SQL>selectcount（*）fromtesttbwhereid=:
testid;
COUNT（*）
----------
3
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
SQL_IDfx0xzybczvkgv,childnumber0
-------------------------------------
selectcount（*）fromtesttbwhereid=:
testid
Planhashvalue:
1163950994
-----------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------
|0|SELECTSTATEMENT||||39（100）||
|1|SORTAGGREGATE||1|13|||
|*2|TABLEACCESSFULL|TESTTB|101K|1292K|39（3）|00:
00:
01|
-----------------------------------------------------------------------------
2.对表进行分析，但不生成柱状图信息
SQL>EXECDBMS_STATS.GATHER_TABLE_STATS（ownname=>'SCOTT',tabname=>'TESTTB',ESTIMATE_PERCENT=>100,CASCADE=>TRUE）;
SQL>altersystemflushshared_pool;
:
testid=2，执行计划走的是索引，是正确的执行计划。
SQL>exec:
testid:
=2
SQL>selectcount（*）fromtesttbwhereid=:
testid;
COUNT（*）
----------
3
SQL>select*fromtable（dbms_xplan.display_cursor）;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_IDfx0xzybczvkgv,childnumber0
---------
升级会员 