【sql调优】使用绑定变量（二）

（记录丁俊，吉庆，杨奇龙和我 讨论并测试的过程）

接前面的测试，再来看看在数据分布改变较大的时候，结合直方图测试一下绑定变量的一些特点。

update t_test_bind a set a.object_id = 3 where a.object_id > 1000;

commit;

analyze table t_test_bind compute statistics ;

analyze table t_test_bind compute statistics for columns object_id size 50;

 

 

SQL> select /*+ gather_plan_statistics*/count(object_id) from t_test_bind where object_id =3;

 

COUNT(OBJECT_ID)

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

           58670

 

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

 

PLAN_TABLE_OUTPUT

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

 

SQL_ID  c5x11x69qbdcf, child number 0

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

select /*+ gather_plan_statistics*/count(object_id) from t_test_bind where

object_id =3

 

Plan hash value: 2169226493

 

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

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

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

|   0 | SELECT STATEMENT      |                    |       |       |    78 (100)|          |

 

PLAN_TABLE_OUTPUT

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

 

|   1 |  SORT AGGREGATE       |                    |     1 |     3 |            |          |

|*  2 |   INDEX FAST FULL SCAN| IND_TEST_BIND__OID | 58431 |   171K|    78   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_ID"=3)

 

 

已选择20行。

 

SQL>

注意到不使用绑定变量的时候走了index fast full scan，这时候因为有直方图，这个执行计划是优化的。

下面使用绑定变量：

SQL> var x2 number;

SQL> exec :x2 :=3;

 

PL/SQL 过程已成功完成。

 

SQL> select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= :x2;

 

  COUNT(*)

----------

     58670

 

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

 

PLAN_TABLE_OUTPUT

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

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

SQL_ID  7z3c4rcwmatz2, child number 0

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

select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= :x2

 

Plan hash value: 2169226493

 

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

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

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

|   0 | SELECT STATEMENT      |                    |       |       |    78 (100)|          |

|   1 |  SORT AGGREGATE       |                    |     1 |     3 |            |          |

 

PLAN_TABLE_OUTPUT

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

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

|*  2 |   INDEX FAST FULL SCAN| IND_TEST_BIND__OID | 58431 |   171K|    78   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_ID"=:X2)

 

 

已选择19行。

恩，也是比较合理的。

接下来修改x2的值：

SQL> exec :x2 :=4;

 

PL/SQL 过程已成功完成。

 

SQL> select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= :x2;

 

  COUNT(*)

----------

         1

 

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

 

PLAN_TABLE_OUTPUT

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

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

SQL_ID  7z3c4rcwmatz2, child number 0

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

select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= :x2

 

Plan hash value: 2169226493

 

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

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

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

|   0 | SELECT STATEMENT      |                    |       |       |    78 (100)|          |

|   1 |  SORT AGGREGATE       |                    |     1 |     3 |            |          |

 

PLAN_TABLE_OUTPUT

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

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

|*  2 |   INDEX FAST FULL SCAN| IND_TEST_BIND__OID | 58431 |   171K|    78   (3)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("OBJECT_ID"=:X2)

 

 

已选择19行。

SQL>

可以看到，这是绑定变量的特点了，沿用了前面已经解析后生成的执行计划，但是我们清楚数据的分布，也做过直方图，最优的情况应该是下面这种：

SQL> select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= 4;

 

  COUNT(*)

----------

         1

 

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

 

PLAN_TABLE_OUTPUT

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

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

SQL_ID  g29jyjygzd6sv, child number 0

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

select /*+ gather_plan_statistics*/count(*) from t_test_bind where object_id= 4

 

Plan hash value: 4208194829

 

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

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

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

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

|   1 |  SORT AGGREGATE   |                    |     1 |     3 |            |          |

 

PLAN_TABLE_OUTPUT

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

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

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

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=4)

 

 

已选择19行。

 

SQL>

所以，在使用绑定变量的时候，需要注意到非常大的数据分布的改变时，可能之前的执行计划已经不是最优的了，这种情况下，使用绑定变量的缺点就暴露出来了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-672921/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-672921/