【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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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/，如需轉載，請註明出處，否則將追究法律責任。

【sql調優】使用繫結變數（二）

相關文章