[20230329]利用bind_aware提示优化案例2.txt --//跟别人聊天提到优化多个查询条件,使用use_concate时有一个分支总是全表扫描的问题.对方可以使用bind_aware提示优化. --//我上网查了一下,发现链接: http://www.dbi-services.com/index.php/blog/entry/generic-query-for-multicriteria-search-part-i-useconcat-or-expansion http://www.dbi-services.com/index.php/blog/entry/generic-query-for-multicriteria-search-part-ii-bindaware-adaptive-cursor-sharing --//自己测试看看. 1.环境： SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 create table t1 as select * from all_objects; create index i_t1_object_id on t1(object_id); create index i_t1_data_object_id on t1(data_object_id); SCOTT@test01p> alter table t1 modify object_id null; Table altered. --//分析略。 SCOTT@test01p> variable a number; SCOTT@test01p> variable b number; $ cat a.txt SELECT /*+ &&1 */ object_name from t1 where object_id = nvl(:a,object_id) and data_object_id = nvl(:b,data_object_id); --//注:原始作者这样写存在一个小问题,就是如果有null值,可能漏掉的.例子: SCOTT@test01p> select count(*) from emp where comm=comm; COUNT(*) ---------- 4 SCOTT@test01p> select count(*) from emp ; COUNT(*) ---------- 14 2.测试: SCOTT@test01p> exec :a :=2; :b :=2 ; PL/SQL procedure successfully completed. SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> @ a.txt bind_aware OBJECT_NAME -------------------- C_OBJ# --//执行计划如下: Plan hash value: 3737217491 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 98 (100)| | 1 |00:00:00.01 | 4 | | 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 2 | 132 | 98 (2)| 00:00:01 | 1 |00:00:00.01 | 4 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 3 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 1 | 27 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 4 | |* 5 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | |* 6 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 7 | TABLE ACCESS FULL | T1 | 0 | 1 | 27 | 96 (2)| 00:00:01 | 0 |00:00:00.01 | 0 | ---------------------------------------------------------------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): 2 2 - :2 (NUMBER): 2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:A IS NOT NULL) 4 - filter("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID")) 5 - access("OBJECT_ID"=:A) 6 - filter(:A IS NULL) 7 - filter(("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID") AND "OBJECT_ID" IS NOT NULL)) --//在两个都有值的情况下,虽然有一路全表扫描,但是starts=0(id=7),等于没有执行.另外oracle选择索引 I_T1_OBJECT_ID ,选择性更好. SCOTT@test01p> exec :a :=null; :b :=2; PL/SQL procedure successfully completed. SCOTT@test01p> @ a.txt bind_aware OBJECT_NAME -------------------- IND$ CLU$ C_OBJ# ICOL$ COL$ TAB$ LOB$ COLTYPE$ SUBCOLTYPE$ NTAB$ REFCON$ OPQTYPE$ ICOLDEP$ VIEWTRCOL$ LIBRARY$ ASSEMBLY$ ATTRCOL$ TYPE_MISC$ 18 rows selected. --//执行计划如下: Plan hash value: 3737217491 ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 18 |00:00:00.01 | 348 | | 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 2 | 132 | 97 (2)| 00:00:01 | 18 |00:00:00.01 | 348 | | 2 | UNION-ALL | | 1 | | | | | 18 |00:00:00.01 | 348 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | FILTER | | 1 | | | | | 18 |00:00:00.01 | 348 | |* 7 | TABLE ACCESS FULL | T1 | 1 | 1 | 27 | 96 (2)| 00:00:01 | 18 |00:00:00.01 | 348 | ---------------------------------------------------------------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): (null) 2 - :2 (NUMBER): 2 Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:A IS NOT NULL) 4 - filter("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID")) 5 - access("OBJECT_ID"=:A) 6 - filter(:A IS NULL) 7 - filter(("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID") AND "OBJECT_ID" IS NOT NULL)) --//并没有出现我想需要的效果!! SCOTT@test01p> exec :a :=2; :b :=null; PL/SQL procedure successfully completed. SCOTT@test01p> @ a.txt bind_aware OBJECT_NAME -------------------- C_OBJ# --//执行计划如下: Plan hash value: 4197413899 -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 1 |00:00:00.01 | 348 | | 1 | VIEW | VW_ORE_BA8ECEFB | 1 | 2 | 132 | 97 (2)| 00:00:01 | 1 |00:00:00.01 | 348 | | 2 | UNION-ALL | | 1 | | | | | 1 |00:00:00.01 | 348 | |* 3 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 5 | INDEX RANGE SCAN | I_T1_DATA_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 348 | |* 7 | TABLE ACCESS FULL | T1 | 1 | 1 | 27 | 96 (2)| 00:00:01 | 1 |00:00:00.01 | 348 | -------------------------------------------------------------------------------------------------------------------------------------------------------- Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): 2 2 - :2 (NUMBER): (null) Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(:B IS NOT NULL) 4 - filter("OBJECT_ID"=NVL(:A,"OBJECT_ID")) 5 - access("DATA_OBJECT_ID"=:B) 6 - filter(:B IS NULL) 7 - filter(("DATA_OBJECT_ID" IS NOT NULL AND "OBJECT_ID"=NVL(:A,"OBJECT_ID"))) --//并没有出现我想需要的效果!! 选择索引正好弄反了. --//我仔细看了执行计划发现,outline如下: /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') OPT_PARAM('_fix_control' '20289688:0') ALL_ROWS OUTLINE_LEAF(@"SET$9162BF3C_2") OUTLINE_LEAF(@"SET$9162BF3C_1") OUTLINE_LEAF(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) ~~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE_LEAF(@"SEL$BA8ECEFB") OUTLINE(@"SET$9162BF3C") OR_EXPAND(@"SEL$1" (1) (2)) ~~~~~~~~~~~~~~~~~~~~~~~~~~ OUTLINE(@"SEL$1") NO_ACCESS(@"SEL$BA8ECEFB" "VW_ORE_BA8ECEFB"@"SEL$BA8ECEFB") INDEX_RS_ASC(@"SET$9162BF3C_1" "T1"@"SEL$1" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SET$9162BF3C_1" "T1"@"SEL$1") FULL(@"SET$9162BF3C_2" "T1"@"SEL$1") END_OUTLINE_DATA */ --//并没有使用对方的use_concat提示. 3.继续: SCOTT@test01p> alter session set optimizer_features_enable='12.1.0.2'; Session altered. SCOTT@test01p> exec :a :=2; :b :=null; PL/SQL procedure successfully completed. SCOTT@test01p> @ a.txt Bind_aware OBJECT_NAME -------------------- C_OBJ# --//执行计划如下: Plan hash value: 2484419617 ------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 1 |00:00:00.01 | 348 | | 1 | CONCATENATION | | 1 | | | | | 1 |00:00:00.01 | 348 | |* 2 | FILTER | | 1 | | | | | 1 |00:00:00.01 | 348 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 1 | 27 | 96 (2)| 00:00:01 | 1 |00:00:00.01 | 348 | |* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | INDEX RANGE SCAN | I_T1_DATA_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | ------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1_1 / T1@SEL$1 5 - SEL$1_2 / T1@SEL$1_2 6 - SEL$1_2 / T1@SEL$1_2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"SEL$1_1") USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2)) OUTLINE_LEAF(@"SEL$1_2") OUTLINE(@"SEL$1") FULL(@"SEL$1_1" "T1"@"SEL$1") INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."DATA_OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "T1"@"SEL$1_2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): 2 2 - :2 (NUMBER): (null) Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:B IS NULL) 3 - filter(("DATA_OBJECT_ID" IS NOT NULL AND "OBJECT_ID"=NVL(:A,"OBJECT_ID"))) 4 - filter(:B IS NOT NULL) 5 - filter("OBJECT_ID"=NVL(:A,"OBJECT_ID")) 6 - access("DATA_OBJECT_ID"=:B) --//还是不对,选择错误的索引. SCOTT@test01p> exec :a :=null; :b :=2; PL/SQL procedure successfully completed. SCOTT@test01p> @ a.txt Bind_aware OBJECT_NAME -------------------- IND$ CLU$ C_OBJ# ICOL$ COL$ TAB$ LOB$ COLTYPE$ SUBCOLTYPE$ NTAB$ REFCON$ OPQTYPE$ ICOLDEP$ VIEWTRCOL$ LIBRARY$ ASSEMBLY$ ATTRCOL$ TYPE_MISC$ 18 rows selected. --//执行计划如下: Plan hash value: 4221700763 -------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 97 (100)| | 18 |00:00:00.01 | 348 | | 1 | CONCATENATION | | 1 | | | | | 18 |00:00:00.01 | 348 | |* 2 | FILTER | | 1 | | | | | 18 |00:00:00.01 | 348 | |* 3 | TABLE ACCESS FULL | T1 | 1 | 1 | 27 | 96 (2)| 00:00:01 | 18 |00:00:00.01 | 348 | |* 4 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | |* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 0 | 1 | 27 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | |* 6 | INDEX RANGE SCAN | I_T1_OBJECT_ID | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | -------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1_1 / T1@SEL$1 5 - SEL$1_2 / T1@SEL$1_2 6 - SEL$1_2 / T1@SEL$1_2 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") OUTLINE_LEAF(@"SEL$1_1") USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) OUTLINE_LEAF(@"SEL$1_2") OUTLINE(@"SEL$1") FULL(@"SEL$1_1" "T1"@"SEL$1") INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."OBJECT_ID")) BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1_2" "T1"@"SEL$1_2") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - :1 (NUMBER): (null) 2 - :2 (NUMBER): 2 Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(:A IS NULL) 3 - filter(("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID") AND "OBJECT_ID" IS NOT NULL)) 4 - filter(:A IS NOT NULL) 5 - filter("DATA_OBJECT_ID"=NVL(:B,"DATA_OBJECT_ID")) 6 - access("OBJECT_ID"=:A) --//还是不对,选择错误的索引. 4.总结: --//总之通过提示bind_aware确实改变了执行计划,但是我的测试选择索引错误,也就是还是无法使用这样的方式控制执行计划, --//或者讲我没有测试出作者应该有的效果. --//另外我也在11g重复测试,不行,遇到类似的情况.选择错误的索引.
[20230329]利用bind_aware提示优化案例2.txt
来源：这里教程网
时间：2026-03-03 18:36:41
作者：
编辑推荐：
- [20230329]利用bind_aware提示优化案例2.txt03-03
- Oracle - CDB和PDB介绍03-03
- 玩转行业数字化转型 | 全新华为云Astro低代码平台带你起飞03-03
- 使用安全顾问查看NAS安全性03-03
- Oracle - 启动失败解决03-03
- 视频压缩助力航天科工集团视频回传03-03
- Oracle Database 21c 自动数据加密简要介绍03-03
- 军用场景需超时视频存储投资过大怎么办03-03
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 玩转行业数字化转型 | 全新华为云Astro低代码平台带你起飞
玩转行业数字化转型 | 全新华为云Astro低代码平台带你起飞
26-03-03 - 使用安全顾问查看NAS安全性
使用安全顾问查看NAS安全性
26-03-03 - 视频压缩助力航天科工集团视频回传
视频压缩助力航天科工集团视频回传
26-03-03 - 军用场景需超时视频存储投资过大怎么办
军用场景需超时视频存储投资过大怎么办
26-03-03 - 视频压缩助力航天科工集团视频回传
视频压缩助力航天科工集团视频回传
26-03-03 - 视频压缩解决银行网点视频无法回传
视频压缩解决银行网点视频无法回传
26-03-03 - Oracle数据库表碎片整理
Oracle数据库表碎片整理
26-03-03 - 【数据库数据恢复】误truncate table的Oracle数据库数据恢复方案
- 视频压缩解决工地安全监控视频统一管理、回传视频卡顿、黑屏问题
视频压缩解决工地安全监控视频统一管理、回传视频卡顿、黑屏问题
26-03-03 - Active Insight 云服务使用方法
Active Insight 云服务使用方法
26-03-03
