在10g 中繫結變數窺探有一個缺點，繫結變數窺探不管後續傳入的繫結變數的具體輸入值是什麼，它會一直沿用之前第一次硬解析時所產省的解析樹和執行計劃（這個缺點在oracle 11g中引入自適應遊標共享後才得到了改善）。因為它可能使CBO在某些情況下所選擇的執行計劃並不是目標SQL在當前情況下的最優執行計劃，而且它可能會帶來目標SQL執行計劃的突然改變，影響系統的效能。

比如某個SQL的執行計劃隨著繫結變數具體輸入值不同對應兩個執行計劃，一個是走索引範圍掃描，另一個是走索引快速掃描。正常情況下，SQL絕大多數繫結變數輸入值，執行計劃走索引範圍掃描，極少數情況下走索引快速掃描。這意味著絕大多數情況下，該SQL在硬解析時快取在庫快取中的執行計劃都是索引範圍掃描，即便使用者輸入了一個本應該走索引快速掃描情形的SQL，沿用了之前硬解析的索引範圍掃描，最多就是不能像索引快速掃描那樣使用並行執行和多塊讀了，該SQL的執行效率也不會出現數量級的差異。

但假如有一天該SQL對應的share cursor被age out出了share pool,那麼當該SQL再次執行時oracle就必須硬解析，如果這次硬解析時傳入的繫結變數輸入值恰好走的是索引快速掃描對應的執行計劃，那麼以後oracle大多數執行時就會選擇索引快速掃描，這下問題就來了，只要這個索引快速掃描對應的share cursor在share pool中，那麼該SQL後續再執行時就會沿用索引快速掃描的計劃。這是非常恐怖的事情，因為索引快速掃描會掃描所有的索引業主塊，而在原來大多數情況下所採用的索引範圍掃描只需要掃描很少的葉子塊就可以了，現在，該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 Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index idx_t1 on t1(object_id);

Index created.

SQL> select count(*) from t1;

COUNT(*)

----------

9470

SQL> select count(distinct(object_id)) from t1;

COUNT(DISTINCT(OBJECT_ID))

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

9470

從查詢結果可以看出，表T1數量為9470，列object_id的distinct為9470，說明object_id的可選擇性非常好。

SQL> EXEC dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100,cascade=>true)

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 2;

COUNT(*)

----------

1

SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 8000;

COUNT(*)

----------

7767

SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM

2 V$SQLAREA

3 WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM T1%';

SQL_TEXT SQL_ID VERSION_COUNT

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

SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 8000 djuap23mwsjuy 1

SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 2 2kxxmbfh6rvay 1

從查詢結果上看，oracle的上述兩個SQL都使用了硬解析。兩個SQL各自生成了一個Parent cursor 和一個child cursor。因為兩個SQL並沒有使用繫結變數，都進行了硬解析。

SQL> set linesize 10000

SQL> col plan_table_output for a200

SQL> set long 900

SQL> set pagesize 800

SQL> select * from table(dbms_xplan.display_cursor('djuap23mwsjuy',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID djuap23mwsjuy, child number 0

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

SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 8000

Plan hash value: 2101382132

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

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

| 0 | SELECT STATEMENT | | | | 7 (100)| |

| 1 | SORT AGGREGATE | | 1 | 4 | | |

|* 2 | INDEX FAST FULL SCAN| IDX_T1 | 7320 | 29280 | 7 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX_FFS(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

2 - filter(("OBJECT_ID"<=8000 AND "OBJECT_ID">=1))

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

43 rows selected.

oracle在執行“SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 8000”時走的是對索引IDX_T1的索引快速全掃描。

SQL> select * from table(dbms_xplan.display_cursor('2kxxmbfh6rvay',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 2kxxmbfh6rvay, child number 0

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

SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 2

Plan hash value: 1970818898

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 4 | | |

|* 2 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 2 (0)| 00:00:01 |

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

2 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=2)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

43 rows selected.

oracle在執行“SELECT COUNT(*) FROM T1 WHERE OBJECT_ID BETWEEN 1 AND 2”時走的是對索引IDX_T1的索引範圍掃描。

下面改成繫結變數的形式。

SQL> var a number

SQL> var b number

SQL> exec :a :=1;

PL/SQL procedure successfully completed.

SQL> exec :b :=2;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_id between :a and :b;

COUNT(*)

----------

1

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT SQL_ID VERSION_COUNT

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

select count(*) from t1 where object_id between :a and :b 380qf5uqj2tct 1

SQL> select * from table(dbms_xplan.display_cursor('380qf5uqj2tct',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 380qf5uqj2tct, child number 0

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

select count(*) from t1 where object_id between :a and :b

Plan hash value: 2351893609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 4 | | |

|* 2 | FILTER | | | | | |

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

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

*/

Peeked Binds (identified by position):

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

1 - :A (NUMBER): 1

2 - :B (NUMBER): 2

Predicate Information (identified by operation id):

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

2 - filter(:A<=:B)

3 - access("OBJECT_ID">=:A AND "OBJECT_ID"<=:B)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

51 rows selected.

從執行結果上看到，oracle此時選擇的執行計劃是走對索引IDX_T1索引範圍掃描，注意到“peeked binds”部分的內容

1 - :A (NUMBER): 1 和 2 - :B (NUMBER): 2，這說明oracle在硬解析時確實使用了繫結變數窺探。

SQL> exec :b :=8000;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where object_id between :a and :b;

COUNT(*)

----------

7767

SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'select count(*) from t1%';

SQL_TEXT SQL_ID VERSION_COUNT EXECUTIONS

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

select count(*) from t1 where object_id between :a and :b 380qf5uqj2tct 1 2

verson_count的值為1，列executions的值為2，說明oracle在第二次執行時使用的是軟解析。

SQL> select * from table(dbms_xplan.display_cursor('380qf5uqj2tct',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID 380qf5uqj2tct, child number 0

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

select count(*) from t1 where object_id between :a and :b

Plan hash value: 2351893609

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

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

| 0 | SELECT STATEMENT | | | | 2 (100)| |

| 1 | SORT AGGREGATE | | 1 | 4 | | |

|* 2 | FILTER | | | | | |

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

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

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$1

3 - SEL$1 / T1@SEL$1

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('10.2.0.1')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))

END_OUTLINE_DATA

*/

Peeked Binds (identified by position):

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

1 - :A (NUMBER): 1

2 - :B (NUMBER): 2

Predicate Information (identified by operation id):

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

2 - filter(:A<=:B)

3 - access("OBJECT_ID">=:A AND "OBJECT_ID"<=:B)

Column Projection Information (identified by operation id):

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

1 - (#keys=0) COUNT(*)[22]

51 rows selected.

從第二次的執行計劃依然走的是對索引IDX_T1的索引範圍掃描，並且

“peeked binds”部分的內容依然是1 - :A (NUMBER): 1 和 2 - :B (NUMBER): 2。

10g繫結變數窺探

相關文章