[20220331]為什麼不使用索引.txt

lfree發表於2022-03-31

[20220331]為什麼不使用索引.txt

--//生產系統一條語句存在效能問題,導致3個表都全表掃描,分析看看。

1.環境:

> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.分析:
> @ sql_id 017n1f8tkgczv
--SQL_ID = 017n1f8tkgczv
select * from xxxxxx_yyy.etc_zy_listDetail where fphm = :FPHM ;

--//語句很簡單,xxxxxx_yyy.etc_zy_listDetail是一個檢視,fphm表示發票號碼,檢查索引也存在,但是就是全表掃描。
--//執行計劃如下:
Plan hash value: 2654262995
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |        |       |   626K(100)|          |       |       |          |
....
|* 24 |  VIEW                          | ETC_ZY_LISTDETAIL |   2723K|   693M|   626K  (1)| 00:00:25 |       |       |          |
|  25 |   COUNT                        |                   |        |       |            |          |       |       |          |
|* 26 |    HASH JOIN                   |                   |   2723K|   262M| 28376   (1)| 00:00:02 |  1695K|  1695K| 1643K (0)|
|  27 |     TABLE ACCESS FULL          | GY_SFXM           |     60 |   660 |     2   (0)| 00:00:01 |       |       |          |
|* 28 |     HASH JOIN                  |                   |   2723K|   233M| 28366   (1)| 00:00:02 |  1486K|  1486K| 1729K (0)|
|  29 |      TABLE ACCESS FULL         | ZY_ZYJS           |   7211 |   140K|    39   (0)| 00:00:01 |       |       |          |
|  30 |      TABLE ACCESS FULL         | ZY_FYMX_JS        |   2755K|   183M| 28319   (1)| 00:00:02 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(3=:B1)
   3 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
   5 - access("YK_YPML"."YPXH"=:B1)
   6 - filter(3=:B1)
   8 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  10 - access("YK_YPML"."YPXH"=:B1)
  11 - filter(3=:B1)
  14 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  16 - access("YK_YPML"."YPXH"=:B1)
  18 - access("T4"."YPSX"="YK_YPML"."YPSX")
  19 - filter(3=:B1)
  21 - access("YK_YPXX"."JGID"=:B1 AND "YK_YPXX"."YPXH"=:B2)
  23 - access("YK_YPML"."YPXH"=:B1)
  24 - filter("FPHM"=:FPHM)
  ~~~~~~~~~~~~~~~~~~~~~~~~~
  26 - access("T2"."FYXM"="T3"."SFXM")
  28 - access("T1"."ZYH"="T2"."ZYH" AND "T1"."JSCS"="T2"."JSCS")
 
--//注意id=24的操作,為什麼放在最後呢,仔細看執行計劃還存在一個count在id = 25,為什麼呢?
Column Projection Information (identified by operation id):
-----------------------------------------------------------
...
  25 - "T3"."SFMC"[VARCHAR2,20], "T2"."FYXM"[NUMBER,22], "T1"."FPHM"[VARCHAR2,12],
       "T2"."JLXH"[NUMBER,22], "T2"."JGID"[NUMBER,22], "T2"."FYXH"[NUMBER,22],
       "T2"."FYMC"[VARCHAR2,100], "T2"."FYSL"[NUMBER,22], "T2"."FYDJ"[NUMBER,22],
       "T2"."ZJJE"[NUMBER,22], "T2"."ZFJE"[NUMBER,22], "T2"."YPLX"[NUMBER,22],
       "T2"."JLXH"[NUMBER,22], "T2"."JGID"[NUMBER,22], "T2"."FYXM"[NUMBER,22],
       "T2"."FYXH"[NUMBER,22], "T2"."FYMC"[VARCHAR2,100], "T2"."FYSL"[NUMBER,22],
       "T2"."FYDJ"[NUMBER,22], "T2"."ZJJE"[NUMBER,22], "T2"."ZFJE"[NUMBER,22],
       "T2"."YPLX"[NUMBER,22], ROWNUM[8]

--//檢查檢視定義發現如下:
       ROWNUM AS sortno ,/*序號*/
--//注:Column Projection Information實際上最後ROWNUM[8],自己很少注意這個細節。
--//昏,整個檢視裡面存在一個毫無意義的取順序號rownum的情況,竟然不定義在開始,也沒有出現在結尾,而是在中間靠後的某個位置.
--//導致無法使用建立的索引,很簡單我寫一個包括全部欄位僅僅沒有sortno的/查詢:

select
BUSNO, LISTDETAILNO, CHARGECODE, CHARGENAME, PRESCRIBECODE,
 LISTTYPECODE, LISTTYPENAME, CODE, NAME, FORM,
 SPECIFICATION, UNIT, STD, NUMBER1, AMT,
 SELFAMT, RECEIVABLEAMT, MEDICALCARETYPE, MEDCAREITEMTYPE, MEDREIMBURSERATE,
 REMARK,  CHRGTYPE, FPHM
 from xxxxxx_yyy.etc_zy_listDetail where fphm = :FPHM ;

--//幾乎是瞬間完成。
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                         |        |       |  1709 (100)|          |       |       |          |
....
|* 24 |  HASH JOIN                             |                         |    378 | 38178 |     9   (0)| 00:00:01 |  1695K|  1695K| 1635K (0)|
|  25 |   TABLE ACCESS FULL                    | GY_SFXM                 |     60 |   660 |     2   (0)| 00:00:01 |       |       |          |
|  26 |   NESTED LOOPS                         |                         |    378 | 34020 |     7   (0)| 00:00:01 |       |       |          |
|  27 |    NESTED LOOPS                        |                         |    378 | 34020 |     7   (0)| 00:00:01 |       |       |          |
|  28 |     TABLE ACCESS BY INDEX ROWID BATCHED| ZY_ZYJS                 |      1 |    20 |     2   (0)| 00:00:01 |       |       |          |
|* 29 |      INDEX RANGE SCAN                  | I_ZY_ZYJS_FPHM          |      1 |       |     1   (0)| 00:00:01 |       |       |          |
|* 30 |     INDEX RANGE SCAN                   | IDX_ZY_FYMX_JS_ZYH_JSCS |     58 |       |     2   (0)| 00:00:01 |       |       |          |
|  31 |    TABLE ACCESS BY INDEX ROWID         | ZY_FYMX_JS              |    378 | 26460 |     5   (0)| 00:00:01 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
...
  24 - access("T2"."FYXM"="T3"."SFXM")
  29 - access("T1"."FPHM"=:FPHM)
  30 - access("T1"."ZYH"="T2"."ZYH" AND "T1"."JSCS"="T2"."JSCS")

--//看著這樣的開發團隊,真心很無語,上線前測試沒有。
--//他們應該好好感謝oracle ODA,exadata,它們才是真正的先進工作者。

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

相關文章