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

[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