关于bind peeking

By cui hua on February 25, 2009 2:36 PM | No Comments

当你发现你执行某条sql时其执行计划时好时坏(oracle称这种情况为flip flop plans)，或者你在RAC的不同节点上执行同样的sql但发现执行计划并不一样的时候，你就要小心了，你可能是遇到了bind peeking。

 

这里，我们以一个简单的例子来重现flip flop plans：

SQL> conn caipra/acca@ipratest;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as caipra

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

SQL> create index idx_t1 on t1(object_id);

 

Index created

 

SQL> select count(*) from t1;

 

  COUNT(*)

----------

     51660

 

SQL> select count(distinct(object_id)) from t1;

 

COUNT(DISTINCT(OBJECT_ID))

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

                     51659

 

这里为什么是51659而不是51660，是因为db link的object_id是null，如下所示：

SQL> select object_name,object_type from t1 where object_id is null;

 

OBJECT_NAME                                      OBJECT_TYPE

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

IPRADEV                                            DATABASE LINK

 

这里我们收集统计信息的时候让oracle自行来决定是否收集histograms及用多少个buckets：

SQL> exec dbms_stats.gather_table_stats(ownname => 'CAIPRA', tabname => 'T1', method_opt => 'for all indexed columns size auto', cascade => true);

 

PL/SQL procedure successfully completed

 

我们先来看不使用绑定变量情况下的执行计划：

SQL> set wrap off;

SQL> select count(*) from t1 where object_id between 999 and 1000;

 

  COUNT(*)

----------

         2

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  5gu397922cuqd, child number 0

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

select count(*) from t1 where object_id between 999 and 1000

 

Plan hash value: 4071255416

 

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

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

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

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

|   1 |  SORT AGGREGATE   |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID">=999 AND "OBJECT_ID"<=1000)

 

 

已选择19行。

 

SQL> select count(*) from t1 where object_id between 0 and 50000;

 

  COUNT(*)

----------

     47451

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  6tf6z6j1sjds7, child number 0

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

select count(*) from t1 where object_id between 0 and 50000

 

Plan hash value: 2174314726

 

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

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

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

|   0 | SELECT STATEMENT      |        |       |       |    30 (100)|          |

|   1 |  SORT AGGREGATE       |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter(("OBJECT_ID"<=50000 AND "OBJECT_ID">=0))

 

 

已选择19行。

这里我们可以看到一个的执行计划是index range scan，另外一个是index fast full scan，这和我们预期的一致！

 

好，现在我们来使用绑定变量来更改上述两句sql：

SQL> var x number;

SQL> var y number;

SQL> exec :x :=999;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y :=1000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9dhu3xk2zu531, child number 0

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

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

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

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

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

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

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

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

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

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

 

Peeked Binds (identified by position):

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

 

   1 - :X (NUMBER): 999

   2 - :Y (NUMBER): 1000

 

Predicate Information (identified by operation id):

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

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

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

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

 

SQL> exec :x := 0;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y := 50000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     47451

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9dhu3xk2zu531, child number 0

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

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

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

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

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

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

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |     3 |    15 |     2   (0)| 00:00:01 |

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

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

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

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

 

Peeked Binds (identified by position):

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

 

   1 - :X (NUMBER): 999

   2 - :Y (NUMBER): 1000

 

Predicate Information (identified by operation id):

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

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

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

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

看到了吗，当我们更改了绑定变量的值后，执行同样的sql，执行计划并没有改变，还是用的index range scan，而且oracle这里执行计划里绑定变量的peeking值还是999和1000，这就是所谓的bind peeking！

 

现在我们来让其执行计划变成index fast full scan：

SQL> grant select on t1 to scott;

 

授权成功。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

     47451

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9dhu3xk2zu531, child number 0

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

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3581975568

 

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

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

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

|   0 | SELECT STATEMENT       |        |       |       |    30 (100)|

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   FILTER               |        |       |       |            |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

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

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

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

 

Peeked Binds (identified by position):

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

 

   1 - :X (NUMBER): 0

   2 - :Y (NUMBER): 50000

 

Predicate Information (identified by operation id):

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

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

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

   3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

这里我们看到执行计划如我们所愿那样变成了index fast full scan，并且这里oracle重新对绑定变量x和y做了一次peeking！

 

现在我们再来换一种方式让bind peeking失效：

SQL> exec :x := 999;

 

PL/SQL 过程已成功完成。

 

SQL> exec :y := 1000;

 

PL/SQL 过程已成功完成。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9dhu3xk2zu531, child number 0

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

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3581975568

 

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

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

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

|   0 | SELECT STATEMENT       |        |       |       |    30 (100)|

|   1 |  SORT AGGREGATE        |        |     1 |     5 |            |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   FILTER               |        |       |       |            |

|*  3 |    INDEX FAST FULL SCAN| IDX_T1 | 41764 |   203K|    30  (10)| 00:00:01

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

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

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

 

PLAN_TABLE_OUTPUT

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

 

Peeked Binds (identified by position):

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

 

   1 - :X (NUMBER): 0

   2 - :Y (NUMBER): 50000

 

Predicate Information (identified by operation id):

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

 

   2 - filter(:X<=:Y)

 

PLAN_TABLE_OUTPUT

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

   3 - filter(("OBJECT_ID"<=:Y AND "OBJECT_ID">=:X))

 

Column Projection Information (identified by operation id):

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

 

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择51行。

 

SQL> alter session set "_optim_peek_user_binds" = false;

 

会话已更改。

 

SQL> select count(*) from t1 where object_id between :x and :y;

 

  COUNT(*)

----------

         2

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9dhu3xk2zu531, child number 1

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

select count(*) from t1 where object_id between :x and :y

 

Plan hash value: 3858015043

 

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

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

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

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

|   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |

 

PLAN_TABLE_OUTPUT

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

|*  2 |   FILTER           |        |       |       |            |          |

|*  3 |    INDEX RANGE SCAN| IDX_T1 |   129 |   645 |     2   (0)| 00:00:01 |

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

 

Query Block Name / Object Alias (identified by operation id):

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

 

   1 - SEL$1

   3 - SEL$1 / T1@SEL$1

 

Outline Data

 

PLAN_TABLE_OUTPUT

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

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

      OPT_PARAM('_optim_peek_user_binds' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

      END_OUTLINE_DATA

 

PLAN_TABLE_OUTPUT

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

  */

 

Predicate Information (identified by operation id):

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

 

   2 - filter(:X<=:Y)

   3 - access("OBJECT_ID">=:X AND "OBJECT_ID"<=:Y)

 

Column Projection Information (identified by operation id):

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

 

 

PLAN_TABLE_OUTPUT

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

   1 - (#keys=0) COUNT(*)[22]

 

 

已选择46行。

 

这里有两点需要注意：

1、即使你把_optim_peek_user_binds设置为false，因为Bug: 4567767的存在，bind peeking还是有可能会发生。

2、如果要重现我这篇文章里举的例子，请在sqlplus中重现，不要在PL/SQL Developer的command window中执行，为什么？你看了如下演示就知道了：

在sqlplus中执行如下语句一切正常：

SQL> select count(*) from uplbth;

 

  COUNT(*)

----------

         7

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  7v0k2uk56ag4b, child number 0

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

select count(*) from uplbth

 

Plan hash value: 2588870679

 

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

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

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

|   0 | SELECT STATEMENT |                  |       |     1 (100)|          |

|   1 |  SORT AGGREGATE  |                  |     1 |            |          |

 

PLAN_TABLE_OUTPUT

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

|   2 |   INDEX FULL SCAN| PK_UPLBTH_UBTBTH |     7 |     1   (0)| 00:00:01 |

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

 

 

已选择14行。

 

但如果你在PL/SQL Developer的command window中执行上述同样的sql，会碰到如下问题：

SQL> select count(*) from uplbth;

 

  COUNT(*)

----------

         7

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  9m7787camwh4m, child number 1

begin :id := sys.dbms_transaction.local_transaction_id; end;

NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 1

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan

 

8 rows selected

也就是说当你在PL/SQL Developer的command window中执行sql的时候，从v$sql_plan抓到的sql实际上是begin :id := sys.dbms_transaction.local_transaction_id; end;

 

Categories:

Leave a comment