[20230329]利用bind_aware提示最佳化案例2.txt
[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重複測試,不行,遇到類似的情況.選擇錯誤的索引.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2942866/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20191213]toad 12下BIND_AWARE提示無效.txt
- [20211221]提示precompute_subquery補充2.txt
- [20221130]最佳化備庫dg遇到的問題2.txt
- union all 最佳化案例
- SQL最佳化案例-使用with as最佳化Subquery Unnesting（七）SQL
- Oracle "腦殘" CBO 最佳化案例Oracle
- OB_MYSQL UPDATE 最佳化案例MySql
- OceanBase 金融專案最佳化案例
- SQL最佳化案例-union代替or（九）SQL
- Element 利用Tooltip提示框實現動態顯示文字提示
- 線上最佳化之案例實戰
- 記一次PHP最佳化案例PHP
- [20230329]記錄除錯sql語句遇到的問題.txt除錯SQL
- SQL最佳化案例-單表分頁語句的最佳化（八）SQL
- DM 傳統行業SQL最佳化案例行業SQL
- Oracle某行系統SQL最佳化(案例四)OracleSQL
- 幫任總DBA太太最佳化條金融SQL案例（DM資料庫案例）SQL資料庫
- 線上業務最佳化之案例實戰
- MySQL效能最佳化淺析及線上案例MySql
- Oracle某X系統SQL最佳化(案例六)OracleSQL
- SQL最佳化案例-正確的使用索引（二）SQL索引
- SQL最佳化案例-自定義函式索引（五）SQL函式索引
- ESET曝光Lojax：首個被利用的UEFI rootkit案例
- LightDB-像Oracle一樣使用最佳化器提示（十）Oracle
- 某保險理賠核心OB SQL最佳化案例SQL
- MySQL原理簡介—11.最佳化案例介紹MySql
- OceanBase 金融專案最佳化案例(union all 改寫)
- 一次系統延遲性最佳化案例
- 大資料交叉報表效能最佳化案例（方案）大資料
- 如何利用精益原則最佳化產品定價？
- 利用Lighthouse進行覆蓋率統計及其最佳化
- 利用免費OA軟體最佳化創造管理效益
- [20191011]拆分rowid 2.txt
- [20180625]oradebug peek 2.txt
- 40個最佳化你的php程式碼的小提示PHP
- kingbase SQL最佳化案例 ( union遞迴 改 cte遞迴 )SQL遞迴
- 技術分享 | MySQL 覆蓋索引最佳化案例一則MySql索引
- SQL最佳化 | MySQL問題處理案例分享三則MySql