关闭bind peeking功能的测试

sundog315 · 发表于 2010-7-8 18:17

http://www.itpub.net/thread-1323402-1-1.html提到是否可以关闭data binds peeking功能，查了一下，有个隐含参数_optim_peek_user_binds，顺便做下测试，Oracle版本为10.2.0.4 IA64 Linux

SQL> create table t (id varchar2(10),text varchar2(4000));

表已创建。

SQL> insert into t select '1',object_name from dba_objects;

已创建92220行。

SQL> insert into t values( '2','aaa');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select id,count(*) from t group by id;

ID          COUNT(*)
---------- ----------
1             92220
2                1

SQL> create index t_idx on t(id);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all indexed co
lumns',cascade=>true);

PL/SQL 过程已成功完成。

SQL> var fid varchar2;
SQL> exec :fid := '2';

PL/SQL 过程已成功完成。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    |    1 (100)|       |
| 1 |  TABLE ACCESS BY INDEX ROWID| T    |    1 | 23 |    1 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | T_IDX |    1 |    |    1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

已选择19行。

正常的binds peeking，由于2的选择度很高，采用索引扫描方式

SQL> alter session set "_optim_peek_user_binds"=false;

会话已更改。

SQL> select * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------

2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  fprdgayw5y5vq, child number 0
-------------------------------------
select * from t where id=:fid

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 85 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 92204 |  2070K| 85 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

已选择18行。

看看10053的内容：

Table Stats::
  Table:  T  Alias:  T
#Rows: 94451  #Blks:  370  AvgRowLen:  23.00
Index Stats::
  Index: T_IDX  Col#: 1
LVLS: 1  #LB: 168  #DK: 2  LB/K: 84.00  DB/K: 181.00  CLUF: 363.00
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
*** 2010-07-08 16:24:44.912
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
  Column (#1): ID(VARCHAR2)
AvgLen: 2.00 NDV: 1 Nulls: 0 Density: 5.2937e-06
Histogram: Freq  #Bkts: 1  UncompBkts: 5633  EndPtVals: 1
  Table:  T  Alias: T
Card: Original: 94451  Rounded: 94451  Computed: 94451.00  Non Adjusted: 94451.00
  -----------------------------------------
  END Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
Cost:  84.71  Resp: 84.71  Degree: 0
   Cost_io: 83.00  Cost_cpu: 23414153
   Resp_io: 83.00  Resp_cpu: 23414153
  Access Path: index (AllEqRange)
Index: T_IDX
resc_io: 531.00  resc_cpu: 37904105
ix_sel: 1  ix_sel_with_filters: 1       --受直方图影响，索引选择度变成了1
Cost: 213.51  Resp: 213.51  Degree: 1
  Best:: AccessPath: TableScan
      Cost: 84.71  Degree: 1  Resp: 84.71  Card: 94451.00  Bytes: 0

OutLine:
  /*+
BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
   OPT_PARAM('_optim_peek_user_binds' 'false')
   OPT_PARAM('optimizer_dynamic_sampling' 4)
   OPT_PARAM('optimizer_index_cost_adj' 40)
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$1")
   FULL(@"SEL$1" "T"@"SEL$1")
END_OUTLINE_DATA
  */

看看使用HINT：

SQL> select /*+ OPT_PARAM('_optim_peek_user_binds' 'false') */ * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------
2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  d487pfc3jdsh8, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds' 'false') */ * from t
where id=:fid
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 85 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 92204 |  2070K| 85 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:FID)

已选择19行。

[ 本帖最后由 sundog315 于 2010-7-8 18:24 编辑 ]

mengyuexin · 发表于 2010-7-8 18:55

上边完全看不懂，先来踩一脚哈~

lsq_008 · 发表于 2010-7-8 19:04

不错

sundog315 · 发表于 2010-7-9 08:28

删除直方图的结果：

SQL> exec dbms_stats.delete_column_stats(user,'T','ID');

PL/SQL 过程已成功完成。

SQL> select /*+ OPT_PARAM('_optim_peek_user_binds' 'false') */ * from t where id=:fid;

ID
----------
TEXT
--------------------------------------------------------------------------------
2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  d487pfc3jdsh8, child number 0
-------------------------------------

select /*+ OPT_PARAM('_optim_peek_user_binds' 'false') */ * from t where
id=:fid

Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    | 34 (100)| |
| 1 |  TABLE ACCESS BY INDEX ROWID| T    | 922 | 21206 | 34 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | T_IDX | 369 |    | 34 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("ID"=:FID)

已选择20行。

ignu · 发表于 2010-7-9 14:13

删除了直方图应该走全表扫描吧，为什么是index

sundog315 · 发表于 2010-7-12 08:45

原帖由 ignu 于 2010-7-9 14:13 发表
删除了直方图应该走全表扫描吧，为什么是index

不好意思，是我的问题，因为，我使用的是exec dbms_stats.delete_column_stats(user,'T','ID');
这样，列的统计信息被删除，导致使用了默认的选择度。

** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
  Column (#1): ID(VARCHAR2)  NO STATISTICS (using defaults)
AvgLen: 7.00 NDV: 2881 Nulls: 0 Density: 3.4706e-04
  Table:  T  Alias: T
Card: Original: 92204  Rounded: 922  Computed: 922.04  Non Adjusted: 922.04
  -----------------------------------------
  END Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
Cost:  84.54  Resp: 84.54  Degree: 0
   Cost_io: 83.00  Cost_cpu: 21094173
   Resp_io: 83.00  Resp_cpu: 21094173
  Access Path: index (AllEqGuess)
Index: T_IDX
resc_io: 86.00  resc_cpu: 749824
ix_sel: 0.004  ix_sel_with_filters: 0.004
Cost: 34.42  Resp: 34.42  Degree: 1
  Best:: AccessPath: IndexRange  Index: T_IDX
      Cost: 34.42  Degree: 1  Resp: 34.42  Card: 922.04  Bytes: 0

SQL> select * from user_tab_col_statistics t where t.table_name='T';
未选定行

sundog315 · 发表于 2010-7-12 08:57

重做了一下：

SQL> alter session set "_optim_peek_user_binds"=false;

会话已更改。

SQL> alter system flush shared_pool;

系统已更改。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed
columns',cascade => true);

PL/SQL 过程已成功完成。

SQL> select * from t where id = :fid;

ID
----------
TEXT
--------------------------------------------------------------------------------
2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  dmctp60cmuxxg, child number 0
-------------------------------------

select * from t where id = :fid

Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 85 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 93847 |  2107K| 85 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

1 - filter("ID"=:FID)

已选择18行。

SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt => 'for all indexed
columns size 1',cascade => true);

PL/SQL 过程已成功完成。

SQL> select * from t where id = :fid;

ID
----------
TEXT
--------------------------------------------------------------------------------
2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  dmctp60cmuxxg, child number 0
-------------------------------------

select * from t where id = :fid

Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 85 (100)|       |
|*  1 |  TABLE ACCESS FULL| T | 93847 |  2107K| 85 (3)| 00:00:02 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

已选择18行。

SQL> exec dbms_stats.delete_column_stats(user,'T','ID');

PL/SQL 过程已成功完成。

SQL> alter system flush shared_pool;

系统已更改。

SQL> select * from t where id = :fid;

ID
----------
TEXT
--------------------------------------------------------------------------------
2
aaa

SQL> select * from table(dbms_xplan.display_cursor(null));

SQL_ID  dmctp60cmuxxg, child number 0
-------------------------------------

select * from t where id = :fid

Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    | 34 (100)| |
| 1 |  TABLE ACCESS BY INDEX ROWID| T    | 939 | 21597 | 34 (0)| 00:00:01 |
|*  2 | INDEX RANGE SCAN       | T_IDX | 376 |    | 34 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - access("ID"=:FID)

已选择19行。

huanhuanlove · 发表于 2010-7-12 09:20

good

ignu · 发表于 2010-7-12 10:34

我在测试中发现OPT_PARAM('_optim_peek_user_binds','false') 不能生效，alter session 可以

SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
  2  id=:fid;

ID       TEXT
---------- ---------------------------------------------------------------------
2       aaa

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  38bhw8n219y25, child number 1
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t
WHERE id=:fid

Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 84 (100)|       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T | 30643 | 748K| 84 (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

19 rows selected.

Elapsed: 00:00:00.07
SQL> alter session set "_optim_peek_user_binds"=true;

Session altered.

Elapsed: 00:00:00.00
SQL> alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.05
SQL> select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t
  2  WHERE id=:fid;

ID       TEXT
---------- ---------------------------------------------------------------------
2       aaa

Elapsed: 00:00:00.05
SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7bc25xy53znsp, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
id=:fid

Plan hash value: 1020776977

--------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    |    2 (100)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 |  TABLE ACCESS BY INDEX ROWID| T    |    1 | 25 |    2 (0)| 00:00
|*  2 | INDEX RANGE SCAN       | T_IDX |    1 |    |    1 (0)| 00:00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

20 rows selected.

Elapsed: 00:00:00.40
SQL>

ignu · 发表于 2010-7-12 10:40

SQL> select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
  2  id=:fid;

ID       TEXT
---------- ---------------------------------------------------------------------
2       aaa

Elapsed: 00:00:00.01
SQL>  select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  38bhw8n219y25, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
id=:fid

Plan hash value: 1020776977

--------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    |    2 (100)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 |  TABLE ACCESS BY INDEX ROWID| T    |    1 | 25 |    2 (0)| 00:00
|*  2 | INDEX RANGE SCAN       | T_IDX |    1 |    |    1 (0)| 00:00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

20 rows selected.

Elapsed: 00:00:00.03
SQL>  alter system flush shared_pool;

System altered.

Elapsed: 00:00:00.04
SQL> select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
  2  id=:fid;

ID       TEXT
---------- ---------------------------------------------------------------------
2       aaa

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  38bhw8n219y25, child number 0
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
id=:fid

Plan hash value: 1020776977

--------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    |    |    2 (100)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 |  TABLE ACCESS BY INDEX ROWID| T    |    1 | 25 |    2 (0)| 00:00
|*  2 | INDEX RANGE SCAN       | T_IDX |    1 |    |    1 (0)| 00:00
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:FID)

20 rows selected.

Elapsed: 00:00:00.39

----------只有alter session后才生效
SQL> alter session set "_optim_peek_user_binds"=false;

Session altered.

Elapsed: 00:00:00.00
SQL> select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t WHERE
  2  id=:fid;

ID       TEXT
---------- ---------------------------------------------------------------------
2       aaa

Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display_cursor(null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  38bhw8n219y25, child number 1
-------------------------------------
select /*+ OPT_PARAM('_optim_peek_user_binds','false') */ * from  t
WHERE id=:fid

Plan hash value: 2153619298

--------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |    |    |    | 84 (100)|       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T | 30643 | 748K| 84 (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=:FID)

19 rows selected.

Elapsed: 00:00:00.07
SQL>