在資料分佈嚴重不均的列上使用繫結變數容易錯過更好的執行計劃

在資料分佈嚴重不均的列上使用繫結變數容易錯過更好的執行計劃，原因在於9i引入bind peeking機制導致的。[@more@]

SQL> desc tt
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> select count(*) from tt;

COUNT(*)
----------
46080

SQL> update tt set object_id=999 where object_id>5;

已更新46064行。

SQL> commit;

提交完成。

SQL> create index idx_tt on tt(object_id) tablespace users;

索引已建立。

SQL> set autotrace traceonly
SQL> variable i number
SQL> exec :i := 2;

PL/SQL 過程已成功完成。

SQL> set linesize 200
SQL> select * from tt where object_id=:i;

執行計劃
----------------------------------------------------------
Plan hash value: 6977672

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 548 | 96996 | 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 548 | 96996 | 22 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT | 219 | | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=TO_NUMBER(:I))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1472 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> exec :i := 999;

PL/SQL 過程已成功完成。

SQL> select * from tt where object_id=:i;

已選擇46064行。

執行計劃
----------------------------------------------------------
Plan hash value: 6977672

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 548 | 96996 | 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 548 | 96996 | 22 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT | 219 | | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=TO_NUMBER(:I))

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
6774 consistent gets
98 physical reads
0 redo size
4572424 bytes sent via SQL*Net to client
34155 bytes received via SQL*Net from client
3072 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
46064 rows processed

SQL> select * from tt where object_id=2;

執行計劃
----------------------------------------------------------
Plan hash value: 6977672

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 708 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TT | 4 | 708 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TT | 4 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID"=2)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
9 recursive calls
0 db block gets
89 consistent gets
0 physical reads
0 redo size
1472 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> select * from tt where object_id=999;

已選擇46064行。

執行計劃
----------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54823 | 9476K| 253 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TT | 54823 | 9476K| 253 (1)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_ID"=999)

Note
-----
- dynamic sampling used for this statement

統計資訊
----------------------------------------------------------
9 recursive calls
0 db block gets
4248 consistent gets
0 physical reads
0 redo size
1920772 bytes sent via SQL*Net to client
34155 bytes received via SQL*Net from client
3072 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
46064 rows processed

SQL>