Oracle绑定变量Bind Peeking
Jun 07, 2016 pm 05:34 PMOracle 在9i之后引入了Bind Peeking,通过Bind Peeking,Oracle可以在硬解析的时候窥探绑定变量的值,并根据当前绑定变量的值生成
Oracle 在9i之后引入了Bind Peeking,通过Bind Peeking,Oracle可以在硬解析的时候窥探绑定变量的值,,并根据当前绑定变量的值生成执行计划。在Oracle 9i之前的版本中,Oracle仅仅通过统计信息来生成执行计划。
下面看一下不同版本Oracle下绑定变量对执行计划的影响
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system set optimizer_features_enable='8.1.7';
系统已更改。
SQL> var v number;
SQL> exec :v := 1;
PL/SQL 过程已成功完成。
SQL> select count(*) from acs_test_tab where record_type = :v;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v
Plan hash value: 2956728990
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
| 1 | SORT AGGREGATE | | 1 | 4 | |
|* 2 | INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 2 | 8 | 3 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RECORD_TYPE"=:V)
已选择47行。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system set optimizer_features_enable='11.2.0.3.1';
系统已更改。
SQL> var v number;
SQL> exec :v := 1;
PL/SQL 过程已成功完成。
SQL> select count(*) from acs_test_tab where record_type = :v;
COUNT(*)
----------
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v
Plan hash value: 2956728990
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 1 | 4 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V (NUMBER): 1 --绑定变量窥探
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RECORD_TYPE"=:V)
已选择49行。
SQL> alter system flush shared_pool;
系统已更改。
SQL> exec :v := 2;
PL/SQL 过程已成功完成。
SQL> select count(*) from acs_test_tab where record_type = :v;
COUNT(*)
----------
50000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3rg5r8sghcvb3, child number 0
-------------------------------------
select count(*) from acs_test_tab where record_type = :v
Plan hash value: 2957754476
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 136 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| ACS_TEST_TAB_RECORD_TYPE_I | 48031 | 187K| 136 (1)| 00:00:02 |
----------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :V (NUMBER): 2 --绑定变量窥探,绑定变量会影响最初硬解析的执行计划
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RECORD_TYPE"=:V)
已选择49行。

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Function to calculate the number of days between two dates in oracle

How long will Oracle database logs be kept?

The order of the oracle database startup steps is

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle

How to determine whether two strings are contained in oracle
