Oracle认证:ORACLE绑定变量BINDPEEKING
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_lay_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID3rg5r8sghcvb3, 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='';
系统已更改。
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_lay_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID3rg5r8sghcvb3, 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_lay_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID3rg5r8sghcvb3, 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 |
相關文章
-
Diaspora Finance Powers Global Development美文欣赏
Diaspora Finance Powers Global DevelopmentLuis Lopez-Ramirez, a 30-year old Honduran immigrant, makes pizza.He has worked two jobs over 16 hours each day.He carefully saves his money and rents a small -
英语阅读:VIT declines to acquire Ang Mo Kio property
The managers of Viva Industrial Trust (VIT) announced on Oct 9 that they have declined an offer to purchase an Ang Mo Kio property at $300 million from Ho Lee Group.Located along Ang Mo Kio Avenue 5, -
英语阅读:Covergirl unveils empowering brand update
It may have been founded 60 years ago old, but US cosmetics giant Covergirl is proving that you're never too old to reinvent yourself.The Coty-owned makeup brand, which has been attracting attenti -
英语满分作文:Reading-TheBestMeanstoAcquireknowledge
Reading- The Best Means to Acquire knowledgeKnowledge may be acquired through many ways。 One way of getting knowledge is from travelling Another way is by conversation, especially one with a great man -
Win10系统提示Officebackgroundtaskhandler.exe弹窗问题的解决方
win10系统经常弹出名称为“”的.弹窗怎么办?如何解决?针对这一问题,本文就为大家图文介绍Win10系统弹窗问题的解决方法,有兴趣的朋友们就来了解下吧!前阵子有不少朋友反映,自己 Windows 10 屏幕总是会弹出一个神秘窗口,眼疾 -
The place of Science and Technology in Modern Life英语作文
human life can not continue without science and technology. for many years, human society has developed with the advance of science and technology while the development of science and technology has i -
英语阅读:African Union Leaders Talk Mali, Chinese Investmen
African leaders met in Ethiopia during the weekend to celebrate the 50th anniversary of their continental alliance and discuss issues such as Chinese investment on the continent as well as the fight a -
Modern Technology Makes Life More Conv英语六级作文
mode technology makes life more convenient; tools are the milestores of the technology as well as human being s progress.men used to cut trees with hand saw. but now, by using electronic saw they can -
Should Private Cars Be Encouraged in China高中英语作文
with the rapid development of economy in china, the number of people who own private cars is rising day after day. quite different views eist as to this e is no doubt that private cars have been playi -
英语阅读范文参考:LovePeopleInAllKindsOfWeather
为进一步搞好院内工作,树好院外形象,提高医院知名度,特制定如下计划:一、对科室进行不定期抽查,包括医务人员精神面貌、着装、文明用语、服务态度、技术操作几百方面,一项不合格扣科室及个人积分,与奖金挂钩,建立奖罚分明制度