[20220121]开发不应该这样写sql2.txt

--//生产系统遇到的优化问题,原始语句很复杂,在测试环境做一个分析并记录.

1.环境:
SCOTT@book> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试例子:
SCOTT@book> variable a number ;
SCOTT@book> exec :a := 7499;
PL/SQL procedure successfully completed.

SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.

SCOTT@book> select * from emp where empno = :a or :a =0 ;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b1b1d5k0f6had, child number 0
-------------------------------------
select * from emp where empno = :a or :a =0
Plan hash value: 3956160932
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       7 |      5 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |    39 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |      5 |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 7499
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("EMPNO"=:A OR :A=0))

--//开发本意是这样可以实现带入0的时候使用全表扫描,而带入大于0的情况下选择索引,可是可是oracle的优化器没有这么智能,不知道开发的想法.
--//好久不做这类优化了,你可以测试单独使用USE_CONCAT提示无效.
select  /*+ USE_CONCAT(@"SEL$1") */ * from emp where (empno = :a or :a =0);
select  /*+ USE_CONCAT */ * from emp where (empno = :a or :a =0);

--//提示无效,大家可以看看很久以前的测试,链接:
--//http://blog.itpub.net/267265/viewspace-1788598/ =>[20150901]提示USE_CONCAT.txt

--//必须写成如下:
--//select  /*+ USE_CONCAT(@"SEL$1"  OR_PREDICATES(1)) */ * from emp where (empno = :a or :a =0);
--//select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from emp where (empno = :a or :a =0);

SCOTT@book> select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from emp where (empno = :a or :a =0);
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  64amtw9gz3kry, child number 0
-------------------------------------
select  /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ * from emp where
(empno = :a or :a =0)
Plan hash value: 3475259919
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  CONCATENATION                |        |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|*  2 |   FILTER                      |        |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS FULL          | EMP    |      0 |     14 |   546 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|*  4 |   FILTER                      |        |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |    39 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1_1 / EMP@SEL$1
   5 - SEL$1_2 / EMP@SEL$1_2
   6 - SEL$1_2 / EMP@SEL$1_2
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 7499
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:A=0)
   4 - filter(LNNVL(:A=0))
   6 - access("EMPNO"=:A)

--//少量出现可以使用sql profile之类稳定执行计划,但是如果多次出现使用sql profile就很麻烦,建议开发不要在应该中使用这样的编写sql模式.
--//或者称为技巧.加入判断来构件sql 语句.

--//我真心很奇怪,在exadata上要7秒才能完成的语句,开发居然也把它上线,不仔细看看吗?
> @dashtop sql_id,event sql_id='ay987w0c6r8rv'  sysdate-4 sysdate-1
    Total
  Seconds     AAS %This   SQL_ID        EVENT                                      FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------------------------------------------ ------------------- -------------------
    17420      .1   64%   ay987w0c6r8rv                                            2022-01-17 19:54:09 2022-01-18 20:30:46
     9520      .0   35%   ay987w0c6r8rv cell smart table scan                      2022-01-17 19:42:21 2022-01-18 20:29:05
       40      .0    0%   ay987w0c6r8rv enq: KO - fast object checkpoint           2022-01-18 11:12:51 2022-01-18 19:40:50
       40      .0    0%   ay987w0c6r8rv gc cr block 2-way                          2022-01-18 11:08:48 2022-01-18 15:56:37
       20      .0    0%   ay987w0c6r8rv reliable message                           2022-01-18 11:03:23 2022-01-18 11:28:34