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

（記錄丁俊，吉慶，楊奇龍和我討論並測試的過程）

測試一下使用繫結變數對執行計劃的影響

版本資訊：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

建立測試用的表和索引：

create table t_test_bind as select * from dba_objects ;

create index ind_test_bind__oid on t_test_bind(object_id);

var x1 number;

exec :x1 :=3;

SQL>

SQL> set linesize 130

先看看這個查詢的執行計劃

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where :x1=0;

未選定行

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

PLAN_TABLE_OUTPUT

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

SQL_ID 2nsgh48aphhm3, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where :x1=0

Plan hash value: 121150299

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

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

| 0 | SELECT STATEMENT | | | | 183 (100)| |

|* 1 | FILTER | | | | | |

PLAN_TABLE_OUTPUT

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

| 2 | TABLE ACCESS FULL| T_TEST_BIND | 65500 | 831K| 183 (2)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - filter(:X1=0)

Note

-----

- dynamic sampling used for this statement

PLAN_TABLE_OUTPUT

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

已選擇23行。

注意到前面的執行計劃走了全表，謂詞資訊可以看到是1 - filter(:X1=0)，而最後的cost是183，意味著做了全表掃描，可能一般會這樣想：初次解析是能夠bind peeking到:x1的值為3的，因此不應該有實際的cost，壓根就不會全表。下面看另一個查詢，使用實際值來代替使用繫結變數：

SQL>

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

COUNT(*)

----------

0

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

PLAN_TABLE_OUTPUT

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

SQL_ID 8868bj9fd1udm, child number 0

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

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

0

Plan hash value: 458487044

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

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

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

PLAN_TABLE_OUTPUT

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

| 1 | SORT AGGREGATE | | 1 | | |

|* 2 | FILTER | | | | |

| 3 | TABLE ACCESS FULL| T_TEST_BIND | 59624 | 183 (2)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

2 - filter(NULL IS NOT NULL)

已選擇21行。

SQL>

現在可以看到這個執行計劃是合情理的，謂詞資訊看到tilter(null is not null),而cost在filter後為1，即最後的cost並不像前面的是全表的cost。

接下來就完全做一個全表的測試，看看這個全表的cost：

SQL> select /*+ gather_plan_statistics*/count(*) from t_test_bind ;

COUNT(*)

----------

59624

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

PLAN_TABLE_OUTPUT

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

SQL_ID fu9y6n134rr9x, child number 0

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

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

Plan hash value: 3972846854

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

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

| 0 | SELECT STATEMENT | | | 183 (100)| |

| 1 | SORT AGGREGATE | | 1 | | |

PLAN_TABLE_OUTPUT

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

| 2 | TABLE ACCESS FULL| T_TEST_BIND | 59624 | 183 (2)| 00:00:03 |

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

已選擇14行。

SQL>

結果一目瞭然了，雖然這個sql本身可能沒有多大意義，但是確實是走了和我們期待的不一樣的執行計劃，再來繼續測試一下：

前面已經在object_id上建立了索引，因此我們很容易想到這裡應該是會走range scan，事實上也是如此：

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1;

OBJECT_ID

----------

3

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

PLAN_TABLE_OUTPUT

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

SQL_ID d5g74q12bmdsh, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id

=:x1

Plan hash value: 1752026137

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

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

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

PLAN_TABLE_OUTPUT

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

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

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

Predicate Information (identified by operation id):

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

1 - access("OBJECT_ID"=:X1)

Note

-----

- dynamic sampling used for this statement

PLAN_TABLE_OUTPUT

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

已選擇23行。

SQL>

增加一個or條件：

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1 or :x1=0;

OBJECT_ID

----------

3

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

PLAN_TABLE_OUTPUT

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

SQL_ID 3fmuy7qv1dfu6, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where

object_id =:x1 or :x1=0

Plan hash value: 2456385350

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

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

| 0 | SELECT STATEMENT | | | | 184 (100)| |

PLAN_TABLE_OUTPUT

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

|* 1 | TABLE ACCESS FULL| T_TEST_BIND | 13 | 169 | 184 (3)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

1 - filter(("OBJECT_ID"=:X1 OR :X1=0))

Note

-----

- dynamic sampling used for this statement

PLAN_TABLE_OUTPUT

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

已選擇23行。

看到執行計劃改變了，好像看起來繫結變數窺探可以知道:x1的值=3，加上了or :x1=0以後應該不會影響到前面的object_id = :x1使用索引，但事實上兵不是這樣，oracle選擇了全表，即單獨使用:x1 = 0的條件時的執行計劃。

稍稍改動一下，將or改為and：

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1 and :x1=0;

未選定行

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

PLAN_TABLE_OUTPUT

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

SQL_ID 08gjd8k25jhwu, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id

=:x1 and :x1=0

Plan hash value: 2416783713

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

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

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

PLAN_TABLE_OUTPUT

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

|* 1 | FILTER | | | | | |

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

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

Predicate Information (identified by operation id):

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

1 - filter(:X1=0)

2 - access("OBJECT_ID"=:X1)

Note

PLAN_TABLE_OUTPUT

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

-----

- dynamic sampling used for this statement

已選擇25行。

SQL>

這時候如願的使用索引，且謂詞資訊也有了變化，cost也降低下來了。

這個應該是在使用繫結變數時or的特性，類似的，改動一下上述查詢：

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1

2 union

3 select /*+ gather_plan_statistics*/object_id from t_test_bind where :x1 = 0

4 ;

OBJECT_ID

----------

2

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

PLAN_TABLE_OUTPUT

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

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

SQL_ID 4404tzvfm1t16, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1 union

select /*+ gather_plan_statistics*/object_id from t_test_bind where :x1 = 0

Plan hash value: 756466840

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

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

| 0 | SELECT STATEMENT | | | | | 355 (100)| |

PLAN_TABLE_OUTPUT

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

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

| 1 | SORT UNIQUE | | 59625 | 174K| 1416K| 355 (100)| 00:00:05 |

| 2 | UNION-ALL | | | | | | |

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

|* 4 | FILTER | | | | | | |

| 5 | TABLE ACCESS FULL| T_TEST_BIND | 59624 | 174K| | 183 (2)| 00:00:03 |

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

Predicate Information (identified by operation id):

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

3 - access("OBJECT_ID"=:X1)

PLAN_TABLE_OUTPUT

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

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

4 - filter(:X1=0)

已選擇24行。

SQL>

或者替換一下or後面的條件：

SQL> exec :x2 :=4;

PL/SQL 過程已成功完成。

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

COUNT(*)

----------

2

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

PLAN_TABLE_OUTPUT

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

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

SQL_ID 4c8x6c5vdjt33, child number 0

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

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

or object_id= :x2

Plan hash value: 2169226493

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

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

| 0 | SELECT STATEMENT | | | | 79 (100)| |

PLAN_TABLE_OUTPUT

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

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

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

|* 2 | INDEX FAST FULL SCAN| IND_TEST_BIND__OID | 58432 | 171K| 79 (4)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - filter(("OBJECT_ID"=:X1 OR "OBJECT_ID"=:X2))

已選擇20行。

SQL>

可以看到並不是or或者union all在作怪，而是使用繫結變數對最前面的單條件:x1=0這樣的情形就是這樣處理的。可以看看是否窺探到了具體的值：

SQL> select /*+ gather_plan_statistics*/object_id from t_test_bind where object_id =:x1 or :x1=0;

OBJECT_ID

----------

3

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

PLAN_TABLE_OUTPUT

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

SQL_ID 3fmuy7qv1dfu6, child number 0

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

select /*+ gather_plan_statistics*/object_id from t_test_bind where

object_id =:x1 or :x1=0

Plan hash value: 2456385350

……

SQL> select value_string

2 from table (select dbms_sqltune.extract_binds(aa.bind_data)

3 from v$sql aa

4 where aa.hash_value in

5 (select hash_value

6 from v$sql_plan a

7 where a.plan_hash_value = '2456385350'));

VALUE_STRING

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

3

SQL>

只測試了10g是這樣的情況，11g和9i沒有測試，不知道是否也是這樣的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-672919/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章