mysql bind variable_Bind Variable Peeking 测试

相关参数

SQL> select * from v$version;

BANNER

---------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter optimizer_mode;

NAME TYPE VALUE

------------------------------------ ---------------------- ----------------

optimizer_mode string ALL_ROWS

SQL> show parameter cursor_sharing;

NAME TYPE VALUE

------------------------------------ ---------------------- ----------------

cursor_sharing string EXACT

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description

2 from x$ksppi a,x$ksppcv b

3 where a.inst_id = USERENV ('Instance')

4 and b.inst_id = USERENV ('Instance')

5 and a.indx = b.indx

6 and upper(a.ksppinm) LIKE upper('%&param%')

7 order by name

8 /

Enter value for param: _optim_peek_user_binds

old 6: and upper(a.ksppinm) LIKE upper('%&param%')

new 6: and upper(a.ksppinm) LIKE upper('%_optim_peek_user_binds%')

NAME VALUE DESCRIPTION

-------------------------------- ------------------------ ----------------------------------

_optim_peek_user_binds TRUE enable peeking of user binds

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(30));

Table created.

SQL> begin

2 for i in 1..100000 loop

3 insert into t_xifenfei values(i,'xifenfei');

4 end loop;

5 commit;

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> update t_xifenfei SET name='www.xifenfei.com' where mod(id,20000)=0;

5 row updated.

SQL> commit;

Commit complete.

SQL> create index i_xifenfei on t_xifenfei(name);

Index created.

默认收集统计信息,查看执行计划

SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autot trace exp

SQL> select id from t_xifenfei where name='xifenfei';

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"='xifenfei')

SQL> select id from t_xifenfei where name='www.xifenfei.com';

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"='www.xifenfei.com')

--这里可以发现,对于这样少量的列的情况，没有选择一个合适的执行计划

准确收集统计信息

SQL> exec DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE,

2 method_opt => 'FOR ALL COLUMNS SIZE 254',estimate_percent => 100);

PL/SQL procedure successfully completed.

再次查看执行计划

SQL> select id from t_xifenfei where name='www.xifenfei.com';

Execution Plan

----------------------------------------------------------

Plan hash value: 1926396081

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 1 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("NAME"='www.xifenfei.com')

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

320 consistent gets

0 physical reads

0 redo size

418 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

5 rows processed

SQL> select id from t_xifenfei where name='xifenfei';

99995 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 99999 | 1367K| 103 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 99999 | 1367K| 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"='xifenfei')

Statistics

----------------------------------------------------------

0 recursive calls

1 db block gets

6970 consistent gets

0 physical reads

0 redo size

1455968 bytes sent via SQL*Net to client

73745 bytes received via SQL*Net from client

6668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

99995 rows processed

--通过这里可以看出在完整的收集表和index包括直方图信息后,数据库执行计划正常

--也说明一点:在数据列分布不均匀的时候,依靠数据库自动收集直方图还是不怎么拷贝.

使用AUTOTRACE测试

SQL> set autot trace exp

SQL> var a varchar2(30);

SQL> exec :a := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select id from t_xifenfei where name=:a;

Execution Plan

----------------------------------------------------------

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 50000 | 683K| 103 (1)| 00:00:02 |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 50000 | 683K| 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"=:A)

--这里可以发现11g的Bind Variable Peeking 没有使用正确的执行计划,其实这个是AUTOTRACE本身的bug导致

收集下面sql执行计划(peeking测试需要)get_plan.sql脚本

SQL> select * from t_xifenfei where name='wwww.xifenfei.com' and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 2708637417

select * from t_xifenfei where name='wwww.xifenfei.com' and id=100

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 3 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=100)

2 - access("NAME"='wwww.xifenfei.com')

SQL> select * from t_xifenfei where name='xifenfei' and id=100;

ID NAME

---------- ------------------------------------------------------------

100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 1355242984

select * from t_xifenfei where name='xifenfei' and id=100

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 103 (100)| |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("ID"=100 AND "NAME"='xifenfei'))

--这里可以看到,两个执行计划都我们希望的

测试peeking功能

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei where name='xifenfei' and id=100;

ID NAME

---------- ------------------------------------------------------------

100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 2860562673

select * from t_xifenfei where name='xifenfei' and id=100

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 103 (100)| |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("ID"=100 AND "NAME"='xifenfei'))

SQL> var b varchar2(30);

SQL> exec :b := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768

select * from t_xifenfei where name=:b and id=100

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 103 (100)| |

|* 1 | TABLE ACCESS FULL| T_XIFENFEI | 1 | 14 | 103 (1)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(("ID"=100 AND "NAME"=:B))

--重新硬解析

SQL> alter system flush shared_pool;

System altered.

SQL> var b varchar2(30);

SQL> exec :b := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768

select * from t_xifenfei where name=:b and id=100

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=100)

2 - access("NAME"=:B)

SQL> var b varchar2(30);

SQL> exec :b := 'xifenfei';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

ID NAME

---------- ------------------------------------------------------------

100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768

select * from t_xifenfei where name=:b and id=100

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2 (100)| |

|* 1 | TABLE ACCESS BY INDEX ROWID| T_XIFENFEI | 1 | 14 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | I_XIFENFEI | 6 | | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID"=100)

2 - access("NAME"=:B)

--虽然oracle 11g宣称在在Bind Variable Peeking上增强了很多,

--但是这里的实验,依然证明他存在问题,导致执行计划不正确

通过整体实验过程,证明几个问题：

1.默认的的DBMS_STATS收集统计信息不一定使得所有执行计划均正确,特别在数据很不均匀分布时.

2.AUTOTRACE不能跟踪Bind Variable Peeking

3.Bind Variable Peeking是在硬解析时候生效,虽然11g进行了改善,但是有些时候效果还是不明显,如果数据很不均匀,在发现sql语句很多不合适的时候,建议先删除该sql的执行计划,让其再次硬解析,碰碰运气,如果一直效果不好,建议不适用绑定参数形式(正确的执行计划,更多的硬解析)

4._optim_peek_user_binds参数可以关闭Bind Variable Peeking功能,很不推荐.