ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > [20230329]利用bind_aware提示优化案例2.txt

[20230329]利用bind_aware提示优化案例2.txt

原创 Oracle 作者：lfree 时间：2023-03-31 09:32:21 1 删除 编辑

[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2942866/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： [20230329]记录调试sql语句遇到的问题.txt
下一篇： [20230403]学习UNIFIED audit--验证清理AUDSYS.AUD$UNIFIED.txt
请登录后发表评论 登录
全部评论
lfree
专家姓名：张文胜
熟悉oracle相关技术，擅长sql优化，rman备份与恢复，熟悉linux shell编程。

注册时间：2008-01-03

  • 博文量
    4006
  • 访问量
    7971267

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593