[20201210]sql語句優化.txt

lfree發表於2020-12-10

[20201210]sql語句優化.txt

--//生產系統sql語句,優化有一些特點,拿出來跟大家分享一下。
--//我一般不大願意寫sql優化的部落格,因為許多情況要講清楚很困難,還有就是安全問題。

1.環境:
xxxxx> @ 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

xxxxx> @ sqlid bw49msgp0a9sn
SQL_ID        SQLTEXT
------------- ------------------------------------------------------------------------------------------------------------------
bw49msgp0a9sn SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )

xxxxx> @ dpc bw49msgp0a9sn ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bw49msgp0a9sn, child number 0
-------------------------------------
SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ
WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )

Plan hash value: 1158680521

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |        |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| ZY_CWTJ    |      1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ZY_CWTJ |      1 |       |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE           |            |      1 |    21 |            |          |
|*  4 |     TABLE ACCESS FULL       | ZY_CWTJ    |   7518 |   154K|  2409   (1)| 00:00:29 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / ZY_CWTJ@SEL$1
   2 - SEL$1 / ZY_CWTJ@SEL$1
   3 - SEL$2
   4 - SEL$2 / ZY_CWTJ@SEL$2
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 82
   2 - (DATE): 12/09/2020 00:03:19
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JLXH"=)
   4 - filter(("BRKS"=:1 AND "BQPB"=0 AND "CZRQ"<:2))
--//已經講了N多次,開發為什麼不仔細想想,這樣要掃描ZY_CWTJ2次。另外說明一下複合索引BRKS,BQPB是存在的,實際上選擇性很差,
--//基本不會使用這個的索引,我一上來就刪除了。
--//另外我真心不願意提交任何修改報告,我們這裡業務流程不復雜,但是時間N久才完成。首先填寫工單,而且提交後要等N久,而且我
--//還必須跟蹤催促相關人員,我真心不知道我在修好修改語句的情況下,為什麼需要很長時間完成修改任務。
--//如果不催促,這樣的修改往往是遙遙無期。

xxxxx> @ bind_cap bw49msgp0a9sn ''
C200
------------------------------------------------------------------------------------------------------------------
SELECT XSYS FROM ZY_CWTJ WHERE JLXH =( SELECT MAX ( JLXH ) FROM ZY_CWTJ WHERE BRKS =:1 AND BQPB =0 AND CZRQ < :2 )

SQL_ID        CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING        C30
------------- ------------ --- ---- -------- ---------- ------------------- --------------- ------------------- ------------------------------
bw49msgp0a9sn            0 YES :1          1         22 2020-12-10 00:05:00 NUMBER          82
                           YES :2          2          7 2020-12-10 00:05:00 DATE            2020/12/10 00:03:18

--//注意查詢的繫結變數,實際上這個程式是一個job呼叫的,在凌晨統計資訊的一部分。

2.首先我先寫出我改寫語句:
SELECT XSYS
  FROM (  SELECT XSYS, JLXH
            FROM ZY_CWTJ
           WHERE BRKS = :1 AND BQPB = 0 AND CZRQ < :2
        ORDER BY JLXH DESC)
 WHERE ROWNUM = 1;

--//這樣僅僅掃描1次。看看執行計劃,為了瞭解sql執行情況加入提示gather_plan_statistics。另外說明一下JLXH是主鍵,順序遞增,
--//上面的語句僅僅會返回1條,不會出現歧義性。執行計劃如下:

SQL_ID  4kkk01y9tzmuc, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  XSYS   FROM (  SELECT XSYS, JLXH  
           FROM ZY_CWTJ            WHERE BRKS = :1 AND BQPB = 0 AND
CZRQ < :2         ORDER BY JLXH DESC)  WHERE ROWNUM = 1
Plan hash value: 1497600757
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |       |    26 (100)|          |      1 |00:00:00.01 |       6 |
|*  1 |  COUNT STOPKEY                |            |      1 |        |       |            |          |      1 |00:00:00.01 |       6 |
|   2 |   VIEW                        |            |      1 |      2 |    26 |    26   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| ZY_CWTJ    |      1 |     20 |   480 |    26   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |
|   4 |     INDEX FULL SCAN DESCENDING| PK_ZY_CWTJ |      1 |   3120 |       |     8   (0)| 00:00:01 |    400 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2 / ZY_CWTJ@SEL$2
   4 - SEL$2 / ZY_CWTJ@SEL$2
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      
      OPT_PARAM('_bloom_filter_enabled' 'false')
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~==>不知道安裝者要修改這2個隱含引數。      
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      INDEX_DESC(@"SEL$2" "ZY_CWTJ"@"SEL$2" ("ZY_CWTJ"."JLXH"))
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   3 - filter(("BRKS"=:1 AND "CZRQ"<:2 AND "BQPB"=0))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "XSYS"[NUMBER,22]
   2 - "XSYS"[NUMBER,22]
   3 - "XSYS"[NUMBER,22]
   4 - "ZY_CWTJ".ROWID[ROWID,10]

--//你可以發現邏輯讀僅僅6個,而且我並沒有建立新的索引,直接利用主鍵JLXH的索引。
--//因為查詢條件非常特殊,總是查詢的日期CZRQ是執行語句的當前時間,也就是表中的最大值。檢視繫結變數就清楚了。jLXH是主鍵,順序增加。
--//缺點就是如果查詢根本不存在的BRKS值,這樣就邏輯讀很大,要掃描整個索引。我發現這樣的情況還真心存在。
--//我估計分院與總院的科室表都是一樣的,這樣兩邊的統計也需要掃描整個科室列表。
--//比如BRKS 帶入 10000,執行計劃的邏輯讀上升,執行計劃如下:
Plan hash value: 1497600757
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |       |    26 (100)|          |      0 |00:00:00.01 |     241 |
|*  1 |  COUNT STOPKEY                |            |      1 |        |       |            |          |      0 |00:00:00.01 |     241 |
|   2 |   VIEW                        |            |      1 |      2 |    26 |    26   (0)| 00:00:01 |      0 |00:00:00.01 |     241 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| ZY_CWTJ    |      1 |     20 |   480 |    26   (0)| 00:00:01 |      0 |00:00:00.01 |     241 |
|   4 |     INDEX FULL SCAN DESCENDING| PK_ZY_CWTJ |      1 |   3120 |       |     8   (0)| 00:00:01 |  31262 |00:00:00.01 |      64 |
--------------------------------------------------------------------------------------------------------------------------------------
--//基本掃描整個索引,再探查整個表,效率比全表掃描要差許多。看全表掃描的執行計劃:

--//這樣情況出現邏輯讀依舊很大,可以建立這樣的索引,充分利用取jlxh最大的特性。
create unique index I_ZY_CWTJ_BRKS_BQPB_JLXH on zy_cwtj (brks, bqpb, jlxh) logging compress 2;

Plan hash value: 2516239191
-----------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |       5 |
|*  1 |  COUNT STOPKEY                 |                          |      1 |        |       |            |          |      1 |00:00:00.01 |       5 |
|   2 |   VIEW                         |                          |      1 |      2 |    26 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | ZY_CWTJ                  |      1 |     20 |   480 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
|*  4 |     INDEX RANGE SCAN DESCENDING| I_ZY_CWTJ_BRKS_BQPB_JLXH |      1 |     40 |       |     2   (0)| 00:00:01 |     21 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//這樣即可以保持索引很小,查詢邏輯讀很低。如果查詢CZRQ 條件是 '2019/12/8 16:11:15',這樣邏輯讀會很高。
Plan hash value: 2516239191
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |      1 |        |       |     4 (100)|          |      0 |00:00:00.01 |     171 |
|*  1 |  COUNT STOPKEY                 |                          |      1 |        |       |            |          |      0 |00:00:00.01 |     171 |
|   2 |   VIEW                         |                          |      1 |      2 |    26 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |     171 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | ZY_CWTJ                  |      1 |     20 |   480 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |     171 |
|*  4 |     INDEX RANGE SCAN DESCENDING| I_ZY_CWTJ_BRKS_BQPB_JLXH |      1 |     40 |       |     2   (0)| 00:00:01 |   1196 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//但是這樣的查詢在實際系統中基本不存在的。

3.回到生產系統看看原來的語句的這樣的索引效果如何:
create unique index I_ZY_CWTJ_BRKS_BQPB_JLXH on zy_cwtj (brks, bqpb, jlxh) logging compress 2;

--//BRKS 帶入 10000,執行計劃如下:
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |      1 |        |       |     2 (100)|          |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ZY_CWTJ                  |      1 |      1 |     9 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN            | PK_ZY_CWTJ               |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|   3 |    SORT AGGREGATE              |                          |      1 |      1 |    21 |            |          |      1 |00:00:00.01 |       2 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| ZY_CWTJ                  |      1 |     20 |   420 |    13   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  5 |      INDEX RANGE SCAN          | I_ZY_CWTJ_BRKS_BQPB_JLXH |      1 |    401 |       |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//如果帶入存在的BRKS值,邏輯讀反而上升。
Plan hash value: 353781330
-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |     174 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ZY_CWTJ                  |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |     174 |
|*  2 |   INDEX UNIQUE SCAN            | PK_ZY_CWTJ               |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |     173 |
|   3 |    SORT AGGREGATE              |                          |      1 |      1 |    21 |            |          |      1 |00:00:00.01 |     171 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| ZY_CWTJ                  |      1 |     20 |   420 |    13   (0)| 00:00:01 |   1199 |00:00:00.01 |     171 |
|*  5 |      INDEX RANGE SCAN          | I_ZY_CWTJ_BRKS_BQPB_JLXH |      1 |    401 |       |     2   (0)| 00:00:01 |   1199 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------------------------------------
--//禁用上面的索引,設定該索引隱含。繼續測試:

create index i_zy_cwtj_laji on zy_cwtj (brks, bqpb, czrq, jlxh) compress 2;

SELECT /*+ gather_plan_statistics  */  XSYS   FROM (  SELECT  XSYS,
JLXH             FROM ZY_CWTJ            WHERE BRKS = :1 AND BQPB = 0
AND CZRQ < :2         ORDER BY JLXH DESC)  WHERE ROWNUM = 1
 
Plan hash value: 4136658378
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |     171 |       |       |          |
|*  1 |  COUNT STOPKEY                 |                |      1 |        |       |            |          |      1 |00:00:00.01 |     171 |       |       |          |
|   2 |   VIEW                         |                |      1 |     20 |   260 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |     171 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY       |                |      1 |     20 |   480 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |     171 |  2048 |  2048 | 2048  (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID| ZY_CWTJ        |      1 |     20 |   480 |     3   (0)| 00:00:01 |   1176 |00:00:00.01 |     171 |       |       |          |
|*  5 |      INDEX RANGE SCAN          | I_ZY_CWTJ_LAJI |      1 |      4 |       |     2   (0)| 00:00:01 |   1176 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
--//可以發現這樣的索引並不佳,邏輯讀很高。而利用它取jlxh最大值,以及查詢CZRQ值都是最大值的特點。重新建立索引如下:

create index i_zy_cwtj_laji on zy_cwtj (brks, bqpb, jlxh,czrq ) compress 2;
--//索引czrq,jlxh交換對比前面的索引。
--//查詢存在的BRKS情況。
SQL_ID  8rvdqyj2pj5kp, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  XSYS   FROM ZY_CWTJ  WHERE JLXH =
(SELECT MAX (JLXH)                  FROM ZY_CWTJ                 WHERE
BRKS = :1 AND BQPB = 0 AND CZRQ < :2)
Plan hash value: 1977445073
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       5 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ZY_CWTJ        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |      1 |
|*  2 |   INDEX UNIQUE SCAN            | PK_ZY_CWTJ     |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |      1 |
|   3 |    SORT AGGREGATE              |                |      1 |      1 |    21 |            |          |      1 |00:00:00.01 |       2 |      1 |
|   4 |     FIRST ROW                  |                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI |      1 |      1 |    21 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      1 |
----------------------------------------------------------------------------------------------------------------------------------------------------
--//這樣效果最好,因為查詢條件 CZRQ < :2,不是等值查詢。注意執行計劃出現INDEX RANGE SCAN (MIN/MAX).
--//如果查詢BRKS在表中不存在,帶入10000.
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |     2 (100)|          |      0 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ZY_CWTJ        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN            | PK_ZY_CWTJ     |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|   3 |    SORT AGGREGATE              |                |      1 |      1 |    21 |            |          |      1 |00:00:00.01 |       2 |
|   4 |     FIRST ROW                  |                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------------
--//兩者差異不大。
--//這樣效果最好,因為查詢條件 CZRQ < :2,不是等值查詢。注意執行計劃出現INDEX RANGE SCAN (MIN/MAX).而不是前面的INDEX RANGE SCAN。
--//即使我日期帶入2018/12/10 0:03:18,邏輯讀也不會上升很大。
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |       |     2 (100)|          |      0 |00:00:00.01 |       6 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | ZY_CWTJ        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |
|*  2 |   INDEX UNIQUE SCAN            | PK_ZY_CWTJ     |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |
|   3 |    SORT AGGREGATE              |                |      1 |      1 |    21 |            |          |      1 |00:00:00.01 |       6 |
|   4 |     FIRST ROW                  |                |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| I_ZY_CWTJ_LAJI |      1 |      1 |    21 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       6 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JLXH"=)
   5 - access("BRKS"=:1 AND "BQPB"=0)
       filter("CZRQ"<:2)
--//注意Predicate Information部分,僅僅在索引中探查。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2740879/,如需轉載,請註明出處,否則將追究法律責任。

相關文章