Never bind peeking or Wrong execution plan?
朋友问我的问题：
版本 ：Release 10.2.0.3.0
set timing on;
set autotrace on;
set autotrace traceonly explain statistics;
create table testplan(id number(10),name varchar2(10));
begin
for i in 1..10000 loop
insert into testplan values(i,’anbob’||i);
end loop;
commit;
end;
create index idx_testplan_id on testplan(id);
alter system set cursor_sharing=SIMILAR;
alter system flush buffer_cache;
alter system flush shared_pool;
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=> ‘zhuyuehua’ , tabname=>’testplan’,estimate_percent=>null,method_opt=> ‘for all indexed columns’, cascade=> TRUE );
–note：收集了直方图
variable i number;
exec :i :=1;
select * from testplan where id >= :i;
select * from testplan where id >= 1;
可以发现这两个的执行计划不一致。为什么？
mytest:
首先来猜想一下，
1,9i起支持了cursor_sharing=SIMILAR 自适应游标共享
所以select * from testplan where id >= 1;肯定会rewrite为共享bind veriable sql.
2,10g有bind peeking（变量窥探）,且flash shared_pool在先
所以exec :i :=1; and select * from testplan where id >= :i;也应该是full-table scan;
然而用的是sqlplus 的autot查看执行计划，
note:有时sqlplus autotrace显示的explian 不一定是实际的。但是统计信息是实际的。
sys@NCME>set autot trace exp stat
sys@NCME>select * from anbob.testplan where id >= :i;10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1838407967-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 6500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTPLAN | 500 | 6500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TESTPLAN_ID | 90 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID">=TO_NUMBER(:I))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
235821 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed而是走了索引
system@NCME>EXPLAIN PLAN FOR select * from anbob.testplan where id >= :i;
Explained.
system@NCME>select * from table(dbms_xplan.display(null,null,'ALL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838407967-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 6500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTPLAN | 500 | 6500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TESTPLAN_ID | 90 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$1 / TESTPLAN@SEL$1
2 - SEL$1 / TESTPLAN@SEL$1Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID">=TO_NUMBER(:I))
Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - "ID"[NUMBER,22], "TESTPLAN"."NAME"[VARCHAR2,10]
2 - "TESTPLAN".ROWID[ROWID,10], "ID"[NUMBER,22]同上
sys@NCME>select sql_id,CHILD_NUMBER,sql_text,plan_hash_value,hash_value from v$sql where sql_text like '%testplan%';SQL_ID CHILD_NUMBER SQL_TEXT PLAN_HASH_VALUE HASH_VALUE
------------- ------------ ------------------------------------------------------------ --------------- ----------
fqu0jygrnknbd 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :iaghwmavnzb4zk 0 select * from anbob.testplan where id >= :i 606188295 3925185522
注意产生两个cursor,且不同的执行计划（不同plan_hash_value）,第一个是sqlplus产生的，下面才是真正的这条sql的。
sys@NCME>select '| Operation |Object Name | Rows | Bytes| Cost |'
2 as "Explain Plan in library cache:" from dual
3 union all
4 select rpad('| '||substr(lpad(' ',1*(depth-1))||operation||
5 decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
6 rpad(decode(id, 0, '----------------------------',
7 substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)
8 ||' ',1, 30)), 31, ' ')||'|'|| lpad(decode(cardinality,null,' ',
9 decode(sign(cardinality-1000), -1, cardinality||' ',
10 decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
11 decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
12 trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
13 lpad(decode(bytes,null,' ',
14 decode(sign(bytes-1024), -1, bytes||' ',
15 decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
16 decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
17 trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
18 lpad(decode(cost,null,' ', decode(sign(cost-10000000), -1, cost||' ',
19 decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
20 trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
21 from v$sql_plan sp
22 where sp.hash_value=&hash_value;
Enter value for hash_value: 3925185522
old 22: where sp.hash_value=&hash_value
new 22: where sp.hash_value=3925185522Explain Plan in library cache:
-------------------------------------------------------------------------------------------------------------------
| Operation |Object Name | Rows | Bytes| Cost |
| SELECT STATEMENT |---------------------------- | | | 8 |
| TABLE ACCESS FULL |TESTPLAN | 10K| 126K| 8 |从v$sql_plan 我们看到应该也是 table full scan,那为什么autotrace 和dbms_xplan.display显示是索引呢，因为它们来源是引用的plan_table表。
sys@NCME>explain plan for select * from anbob.testplan where id >= :i;Explained.
sys@NCME>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1838407967-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500 | 6500 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TESTPLAN | 500 | 6500 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TESTPLAN_ID | 90 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID">=TO_NUMBER(:I))
14 rows selected.
sys@NCME>select sql_id,CHILD_NUMBER,sql_text,plan_hash_value,hash_value from v$sql where sql_text like '%testplan%';
SQL_ID CHILD_NUMBER SQL_TEXT PLAN_HASH_VALUE HASH_VALUE
------------- ------------ ------------------------------------------------------------ --------------- ----------
fqu0jygrnknbd 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :iaghwmavnzb4zk 0 select * from anbob.testplan where id >= :i 606188295 3925185522
7dvh9w59mva5z 0 explain plan for select * from anbob.testplan where id >= :i 1838407967 1396549823sys@NCME>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aghwmavnzb4zk', 0, 'ADVANCED'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aghwmavnzb4zk, child number 0
-------------------------------------
select * from anbob.testplan where id >= :iPlan hash value: 606188295
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | TABLE ACCESS FULL| TESTPLAN | 10000 | 126K| 8 (0)| 00:00:01 |
------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------1 - SEL$1 / TESTPLAN@SEL$1
Outline Data
-------------/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "TESTPLAN"@"SEL$1")
END_OUTLINE_DATA
*/Peeked Binds (identified by position):
--------------------------------------1 - :I (NUMBER): 1
Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID">=:I)
Column Projection Information (identified by operation id):
-----------------------------------------------------------1 - "ID"[NUMBER,22], "TESTPLAN"."NAME"[VARCHAR2,10]
用DBMS_XPLAN.DISPLAY_CURSOR我们也验证了真正应该就是走的全表扫描，而且注意
Peeked Binds (identified by position):
--------------------------------------1 - :I (NUMBER): 1
也窥探到了变量是1，所以sqlplus autot 给我们提供了错误的显示，下面看字面量时的执行计划sys@NCME>select * from anbob.testplan where id >= 1;
10000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 606188295------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TESTPLAN | 10000 | 126K| 8 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID">=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
696 consistent gets
0 physical reads
0 redo size
235821 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed注意走的是全表扫描，另外再注意sqlplus的统计信息，因为统计信息是实际的，是不是和上面用变量时的统计信息很像呢，怎么可能index和table一样叱？更应该是上面的执行计划有问题。
sys@NCME>select sql_id,CHILD_NUMBER,sql_text,plan_hash_value,hash_value from v$sql where sql_text like '%testplan%';
SQL_ID CHILD_NUMBER SQL_TEXT PLAN_HASH_VALUE HASH_VALUE
------------- ------------ ------------------------------------------------------------ --------------- ----------
7jhdfyz9rf4mc 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 606188295 3547796076
from anbob.testplan where id >= 1fqu0jygrnknbd 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :ifqu0jygrnknbd 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :iaghwmavnzb4zk 0 select * from anbob.testplan where id >= :i 606188295 3925185522
7dvh9w59mva5z 0 explain plan for select * from anbob.testplan where id >= :i 1838407967 13965498235d38gauv87bub 0 select * from anbob.testplan where id >= :"SYS_B_0" 606188295 3062083403
注意select * from anbob.testplan where id >= :"SYS_B_0" 也就是自适应游标起了作用，oracle反字面量替换为了sys开头的变量名
sys@NCME>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5d38gauv87bub'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 5d38gauv87bub, child number 0
-------------------------------------
select * from anbob.testplan where id >= :"SYS_B_0"Plan hash value: 606188295
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
|* 1 | TABLE ACCESS FULL| TESTPLAN | 10000 | 126K| 8 (0)| 00:00:01 |
------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID">=:SYS_B_0)
18 rows selected.
sys@NCME>show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing string SIMILARnote :
注意当用veriable变量绑定时sqlplus autotrace有可能显示的执行计划不是实际的，用dbms_xplan.display_cursor查看
cursor_sharing=SIMILAR也不在推荐，到11G更是ORACLE放弃了这个值。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。