首页 » ORACLE » Never bind peeking or Wrong execution plan?

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$1

Predicate 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 >= :i

aghwmavnzb4zk 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=3925185522

Explain 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 >= :i

aghwmavnzb4zk 0 select * from anbob.testplan where id >= :i 606188295 3925185522
7dvh9w59mva5z 0 explain plan for select * from anbob.testplan where id >= :i 1838407967 1396549823

sys@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 >= :i

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 |
------------------------------------------------------------------------------

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 >= 1

fqu0jygrnknbd 0 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :i

fqu0jygrnknbd 1 EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * 1838407967 4014559597
from anbob.testplan where id >= :i

aghwmavnzb4zk 0 select * from anbob.testplan where id >= :i 606188295 3925185522
7dvh9w59mva5z 0 explain plan for select * from anbob.testplan where id >= :i 1838407967 1396549823

5d38gauv87bub 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 SIMILAR

note :
注意当用veriable变量绑定时sqlplus autotrace有可能显示的执行计划不是实际的,用dbms_xplan.display_cursor查看
cursor_sharing=SIMILAR也不在推荐,到11G更是ORACLE放弃了这个值。

打赏

对不起,这篇文章暂时关闭评论。