10g绑定变量窥探

最新推荐文章于 2026-01-08 17:10:56 发布

转载最新推荐文章于 2026-01-08 17:10:56 发布 · 93 阅读

文章标签：

在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(*)

----------

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(*)

----------

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。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1435067/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1435067/