[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