慎用sys_context，可能導致無法正確的bind_peeking，而選擇錯誤的執行計劃

http://www.itpub.net/thread-1350771-1-1.html

在上面的帖子裡，樓主提出使用dbms_session來處理繫結變數。但是，這個方法必須進行有效的測試，防止出現錯誤的執行計劃。

由於sys_context導致bind_peeking無法正確執行，在某些情況下，會生成錯誤的執行計劃，見下例：

[@more@]

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 9月 29 08:43:56 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SUNDOG315>conn test/test
已連線。
SUNDOG315>create table t (id number,text varchar2(4000));

表已建立。

SUNDOG315>insert into t select 1,object_name from dba_objects;

已建立55085行。

SUNDOG315>insert into t select 2,'aaa' from dual;

已建立 1 行。

SUNDOG315>commit;

提交完成。

SUNDOG315>create index t_idx on t(id);

索引已建立。

SUNDOG315>exec dbms_stats.gather_table_stats(user,'T',null,null,method_opt=>'for
all columns',cascade=>true);

PL/SQL 過程已成功完成。

SUNDOG315>select id,count(*) from t group by id;

ID COUNT(*)
---------- ----------
1 55085
2 1

SUNDOG315>select count(*) from t where id=2;

COUNT(*)
----------
1

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

SQL_ID 8tfp2xac71yd6, child number 0
-------------------------------------
select count(*) from t where id=2

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T_IDX | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("ID"=2)

已選擇19行。

--選擇了正確的索引範圍掃描

SUNDOG315>var fid number;
SUNDOG315>exec :fid := 2;

PL/SQL 過程已成功完成。


SUNDOG315>select count(*) from t where id=:fid;

COUNT(*)
----------
1

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

SQL_ID 3u1847dua6k75, child number 0
-------------------------------------
select count(*) from t where id=:fid

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| T_IDX | 1 | 3 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - access("ID"=:FID)

已選擇19行。

--由於bind_peeking的存在，依然選擇了正確的索引範圍掃描

SUNDOG315>create context test using test_p;

上下文已建立。

SUNDOG315>create or replace procedure test_p is
2 begin
3 dbms_session.set_context('test','id',2);
4 end;
5 /

過程已建立。

SUNDOG315>exec test_p;

PL/SQL 過程已成功完成。

SUNDOG315>select sys_context('TEST','ID') from dual;

SYS_CONTEXT('TEST','ID')
--------------------------------------------------------------------------------

2

SUNDOG315>select count(*) from t where id=sys_context('test','id');

COUNT(*)
----------
1

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

SQL_ID 69x691fhtckm0, child number 0
-------------------------------------
select count(*) from t where id=sys_context('test','id')

Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 32 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX FAST FULL SCAN| T_IDX | 27543 | 82629 | 32 (4)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - filter("ID"=TO_NUMBER(SYS_CONTEXT('test','id')))

已選擇19行。

--TO_NUMBER(SYS_CONTEXT('test','id'))無法peeking到正確的值，因此，選擇了錯誤的執行計劃

SUNDOG315>set autot trace stat
SUNDOG315>select count(*) from t where id=2;


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

SUNDOG315>select count(*) from t where id=:fid;


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

SUNDOG315>select count(*) from t where id=sys_context('test','id');


統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
116 consistent gets --邏輯讀增大
0 physical reads
0 redo size
423 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

目前，估計用dbms_session的人還是比較少，像這種問題，Oracle其實完全可以處理掉的。估計如果使用的人多，就會進行處理了。呵呵