oracle绑定变量执行计划,绑定变量对执行计划的影响-CSDN博客

本文探讨了Oracle数据库中，开启和关闭绑定变量窥探(_optim_peek_user_binds)对SQL执行计划的影响。当关闭绑定变量窥探时，SQL可能会选择错误的执行计划，即使后续执行相同SQL也会沿用之前的错误计划。而开启该选项并刷新共享池后，SQL会根据实际参数调整执行计划，但可能导致全表扫描。文章通过实例展示了不同设置下的执行计划和性能差异，揭示了绑定变量对数据库性能的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

发现无论启动或者关闭绑定变量窥探使用绑定变量的SQL都有可能选择错误的执行计划，当第一次硬解析产生执行计划时，之后的绑定变量SQL就会继续这个执行计划执行，有时可能是错的。如何改进呢？

1.当关闭绑定变量窥探时

SQL> @p _optim_peek_user_binds

old 4: and lower(n.ksppinm) like lower('%&1%')

new 4: and lower(n.ksppinm) like lower('%_optim_peek_user_binds%')

NAME VALUE

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

_optim_peek_user_binds FALSE

SQL> select HISTOGRAM,COLUMN_NAME,NUM_DISTINCT from user_tab_columns where TABLE_NAME='T';

HISTOGRAM COLUMN_NAME NUM_DISTINCT

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

HEIGHT BALANCED ID 1000

FREQUENCY PAD 1

SQL> select count(*) from t;

COUNT(*)

----------

1000

SQL> EXECUTE :id := 10;

PL/SQL procedure successfully completed.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)

----------

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID asth1mx10aygn, child number 0

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

SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2053823973

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 50 | 9 |00:00:00.01 | 3 |

|* 3 | INDEX RANGE SCAN | T_PK | 1 | 9 | 9 |00:00:00.01 | 2 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"<:id>

20 rows selected.

SQL> EXECUTE :id := 990;

PL/SQL procedure successfully completed.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)

----------

989

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID asth1mx10aygn, child number 0

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

SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2053823973

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 19 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 19 |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 50 | 989 |00:00:00.01 | 19 |

|* 3 | INDEX RANGE SCAN | T_PK | 1 | 9 | 989 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

3 - access("ID"<:id>

20 rows selected.

1.当开启绑定变量窥探时

SQL> alter system set "_optim_peek_user_binds"=true scope=both;

System altered.

SQL> @p _optim_peek_user_binds

old 4: and lower(n.ksppinm) like lower('%&1%')

new 4: and lower(n.ksppinm) like lower('%_optim_peek_user_binds%')

NAME VALUE

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

_optim_peek_user_binds TRUE

SQL> alter system flush shared_pool;

System altered.

SQL> EXECUTE :id := 990;

SELECT count(pad) FROM t WHERE id < :id;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PL/SQL procedure successfully completed.

SQL>

COUNT(PAD)

----------

989

SQL>

PLAN_TABLE_OUTPUT

SQL_ID asth1mx10aygn, child number 0

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

SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 28 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 28 |

|* 2 | TABLE ACCESS FULL| T | 1 | 988 | 989 |00:00:00.01 | 28 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"<:id>

19 rows selected.

SQL> EXECUTE :id := 10;

PL/SQL procedure successfully completed.

SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)

----------

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID asth1mx10aygn, child number 0

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

SELECT count(pad) FROM t WHERE id < :id

Plan hash value: 2966233522

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 28 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 28 |

|* 2 | TABLE ACCESS FULL| T | 1 | 988 | 9 |00:00:00.01 | 28 |

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

Predicate Information (identified by operation id):

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

2 - filter("ID"<:id>

19 rows selected.