本文為墨天輪數據庫管理服務團隊第70期技術分享，內容原創，作者為技術顧問馬奕璇，如需轉載請聯系小墨（VX：modb666）并注明來源。
一、問題概述
開發人員反映有條跑批語句在測試環境執行了很久都沒結束，發現卡在了一個update的sql，取出sql monitor查看，正在執行，已經跑了一個半小時左右還沒結束。
二、問題原因
SQL Text
------------------------------
update gla_glis_h gset (dybsam, cybsam) =(select nvl(sum(drtsam), 0), nvl(sum(crtsam), 0)from gla_glis_h hwhere h.stacid = :1and h.systid = '0000'and h.acctdt >= substr(:2, 0, 4) || '0101'and h.acctdt <= :3and h.geldtp = :4and g.brchcd = h.brchcdand g.itemcd = h.itemcdand g.crcycd = h.crcycdand h.centcd = g.centcdand h.prsncd = g.prsncdand h.custcd = g.custcdand h.prducd = g.prducdand h.prlncd = g.prlncdand h.acctno = g.acctnoand h.assis0 = g.assis0and h.assis1 = g.assis1and h.assis2 = g.assis2and h.assis3 = g.assis3and h.assis4 = g.assis4and h.assis5 = g.assis5and h.assis6 = g.assis6and h.assis7 = g.assis7and h.assis8 = g.assis8and h.assis9 = g.assis9)where g.stacid = :5and g.geldtp = :6and g.acctdt = :7and g.systid = '0000'
執行計劃
Global Information
------------------------------Status : EXECUTING Instance ID : 1 Session : SUNGL (666:36947) SQL ID : 8vmgcmug21gvp SQL Execution ID : 16777216 Execution Started : 03/30/2020 15:44:53 First Refresh Time : 03/30/2020 15:45:05 Last Refresh Time : 03/30/2020 17:00:14 Duration : 4521s Module/Action : JDBC Thin Client/- Service : uattapp Program : JDBC Thin Client Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | NUMBER | 201 |
| :2 | 2 | VARCHAR2(32) | 20191231 |
| :3 | 3 | VARCHAR2(32) | 20191231 |
| :4 | 4 | VARCHAR2(32) | H |
| :5 | 5 | NUMBER | 201 |
| :6 | 6 | VARCHAR2(32) | H |
| :7 | 7 | VARCHAR2(32) | 20191231 |
========================================================================================================================Global Stats
=================================================================================
| Elapsed | Cpu | IO | Concurrency | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
=================================================================================
| 4520 | 2350 | 0.00 | 0.01 | 2170 | 573M | 2 | 16384 |
=================================================================================SQL Plan Monitoring Details (Plan Hash Value=1242074832)
=====================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=====================================================================================================================================================================================
| 0 | UPDATE STATEMENT | | | | | | 1 | | | | | |
| -> 1 | UPDATE | GLA_GLIS_H | | | 4512 | +12 | 1 | 0 | | | 0.16 | log file switch completion (1) |
| | | | | | | | | | | | | Cpu (6) |
| -> 2 | PARTITION RANGE SINGLE | | 24734 | 848 | 4512 | +12 | 1 | 91679 | | | | |
| -> 3 | TABLE ACCESS FULL | GLA_GLIS_H | 24734 | 848 | 4524 | +0 | 1 | 91679 | | | 0.04 | Cpu (2) |
| -> 4 | SORT AGGREGATE | | 1 | | 4512 | +12 | 91679 | 91678 | | | 0.02 | Cpu (1) |
| -> 5 | TABLE ACCESS BY GLOBAL INDEX ROWID | GLA_GLIS_H | 1 | 4276 | 4512 | +12 | 91679 | 91719 | | | 0.02 | Cpu (1) |
| -> 6 | INDEX RANGE SCAN | PK_GLA_GLIS_H | 1 | 4275 | 4522 | +2 | 91679 | 91719 | 2 | 16384 | 99.76 | Cpu (4486) |
| | | | | | | | | | | | | latch free (1) |
=====================================================================================================================================================================================從sqlmonitor上看主要耗時在第六步PK_GLA_GLIS_H回表上，這個sql的主要結構是
update GLA_GLIS_H g
set col=(select col from GLA_GLIS_H h where g.xx=h.xx and h.col=“” )
where g.col=“”
查看索引的信息
PK_GLA_GLIS_H primary key (STACID, ACCTDT, SYSTID, BRCHCD, ITEMCD, CRCYCD, GELDTP, CENTCD, PRSNCD, CUSTCD, PRDUCD, PRLNCD, ACCTNO, ASSIS0, ASSIS1, ASSIS2, ASSIS3, ASSIS4, ASSIS5, ASSIS6, ASSIS7, ASSIS8, ASSIS9)
這是一個分區表，分區鍵是ACCTDT，主鍵索引確實全局索引，顯然是不合理的，再從內存獲取執行計劃，查看索引用上的是哪一個列
Predicate Information (identified by operation id):
---------------------------------------------------3 - filter(("G"."ACCTDT"=:7 AND "G"."STACID"=:5 AND "G"."GELDTP"=:6 AND "G"."SYSTID"='0000'))6 - access("H"."STACID"=:1 AND "H"."ACCTDT">=SUBSTR(:2,0,4)||'0101' AND "H"."SYSTID"='0000' AND"H"."BRCHCD"=:B1 AND "H"."ITEMCD"=:B2 AND "H"."CRCYCD"=:B3 AND "H"."GELDTP"=:4 AND "H"."CENTCD"=:B4 AND"H"."PRSNCD"=:B5 AND "H"."CUSTCD"=:B6 AND "H"."PRDUCD"=:B7 AND "H"."PRLNCD"=:B8 AND "H"."ACCTNO"=:B9 AND"H"."ASSIS0"=:B10 AND "H"."ASSIS1"=:B11 AND "H"."ASSIS2"=:B12 AND "H"."ASSIS3"=:B13 AND "H"."ASSIS4"=:B14 AND"H"."ASSIS5"=:B15 AND "H"."ASSIS6"=:B16 AND "H"."ASSIS7"=:B17 AND "H"."ASSIS8"=:B18 AND "H"."ASSIS9"=:B19 AND"H"."ACCTDT"<=:3)filter(("H"."ITEMCD"=:B1 AND "H"."BRCHCD"=:B2 AND "H"."ASSIS1"=:B3 AND "H"."ASSIS0"=:B4 AND"H"."CRCYCD"=:B5 AND "H"."GELDTP"=:4 AND "H"."SYSTID"='0000' AND "H"."CENTCD"=:B6 AND "H"."PRSNCD"=:B7 AND"H"."CUSTCD"=:B8 AND "H"."PRDUCD"=:B9 AND "H"."PRLNCD"=:B10 AND "H"."ACCTNO"=:B11 AND "H"."ASSIS2"=:B12 AND"H"."ASSIS3"=:B13 AND "H"."ASSIS4"=:B14 AND "H"."ASSIS5"=:B15 AND "H"."ASSIS6"=:B16 AND "H"."ASSIS7"=:B17 AND"H"."ASSIS8"=:B18 AND "H"."ASSIS9"=:B19))
–從access與filter對比，實際上索引用到的列有STACID，ACCTDT，SYSTID
–再查看表的統計信息，表總的有接近600w行數據，STACID，ACCTDT，SYSTID 的num_distinct值分別是 9，25，11，篩選度非常低
–外層sql查詢結果大概75w行，作為驅動表再通過篩選度非常低的主鍵索引去篩選符合條件的記錄，性能很差
–將sql monitor獲取到的綁定變量帶入：
–外層sql結果
| :4 | 4 | VARCHAR2(32) | H |
| :5 | 5 | NUMBER | 201 |
| :6 | 6 | VARCHAR2(32) | H |
| :7 | 7 | VARCHAR2(32) | 20191231select count(1) from sungl.gla_glis_h g where g.stacid = 201
and g.geldtp = ‘H’
and g.acctdt = ‘20191231’
and g.systid = ‘0000’
–754952
–里層sql結果
| :1 | 1 | NUMBER | 201 |
| :2 | 2 | VARCHAR2(32) | 20191231 |
| :3 | 3 | VARCHAR2(32) | 20191231 |
| :4 | 4 | VARCHAR2(32) | H
select COUNT(1)
from SUNGL.gla_glis_h h
where h.stacid = 201
and h.systid = ‘0000’
and h.acctdt >= substr(‘20191231’, 0, 4) || ‘0101’
and h.acctdt <= ‘20191231’
and h.geldtp = ‘H’
–755618
–根據sql的連接條件，查看表的統計信息，連接列中篩選度較高的是以下幾個列
用戶 列 NUM_DISTINCT NUM_NULLS 收集方式 最后分析 SAMPLE_SIZE
------------------------------ ------------------------------ ------------ ---------- --------------- ------------------- -----------
SUNGL ITEMCD 1154 0 HEIGHT BALANCED 2020-03-29 06:02:19 5517
SUNGL BRCHCD 863 0 HEIGHT BALANCED 2020-03-29 06:02:19 5517
SUNGL TRANTI 252 0 NONE 2020-03-29 06:02:19 59501917
SUNGL ASSIS1 70 0 FREQUENCY 2020-03-29 06:02:19 5518
SUNGL ASSIS0 56 0 FREQUENCY 2020-03-29 06:02:19 5517
三、解決方案
建議添加如下索引（where條件中可篩選的，已經連接條件中篩選度高的）：
create index SUNGL.IDX_GLA_GLIS_H on SUNGL.GLA_GLIS_H (SYSTID, STACID, GELDTP, ITEMCD, BRCHCD, CRCYCD, ASSIS1, ASSIS0) local;
添加索引后再次執行，獲取sql monitor如下：
SQL Monitoring ReportSQL Text
------------------------------
update gla_glis_h g set (dybsam,cybsam)= (select nvl(sum(drtsam),0),nvl(sum(crtsam),0) from gla_glis_h h where h.stacid=:1 and h.systid='0000' and h.acctdt >=substr(:2 ,0,4)||'0101' and h.acctdt <=:3 and h.geldtp=:4 and g.brchcd=h.brchcd and g.itemcd=h.itemcd and g.crcycd=h.crcycd and h.centcd=g.centcd and h.prsncd=g.prsncd and h.custcd=g.custcd and h.prducd=g.prducd and h.prlncd =g.prlncd and h.acctno=g.acctno and h.assis0=g.assis0 and h.assis1=g.assis1 and h.assis2=g.assis2 and
h.assis3=g.assis3 and h.assis4=g.assis4 and h.assis5=g.assis5 and h.assis6=g.assis6 and h.assis7=g.assis7 and h.assis8=g.assis8 and h.assis9=g.assis9 ) where g.stacid =:5 and g.geldtp=:6 and g.acctdt=:7 and g.systid='0000'Global Information
------------------------------Status : DONE Instance ID : 1 Session : SUNGL (3932:6295) SQL ID : 8vmgcmug21gvp SQL Execution ID : 16777217 Execution Started : 03/31/2020 08:56:11 First Refresh Time : 03/31/2020 08:56:21 Last Refresh Time : 03/31/2020 08:57:59 Duration : 108s Module/Action : JDBC Thin Client/- Service : uattapp Program : JDBC Thin Client Binds
========================================================================================================================
| Name | Position | Type | Value |
========================================================================================================================
| :1 | 1 | NUMBER | 201 |
| :2 | 2 | VARCHAR2(32) | 20191231 |
| :3 | 3 | VARCHAR2(32) | 20191231 |
| :4 | 4 | VARCHAR2(32) | H |
| :5 | 5 | NUMBER | 201 |
| :6 | 6 | VARCHAR2(32) | H |
| :7 | 7 | VARCHAR2(32) | 20191231 |
========================================================================================================================Global Stats
===================================================================
| Elapsed | Cpu | IO | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===================================================================
| 108 | 57 | 0.07 | 51 | 43M | 40 | 320KB |
===================================================================SQL Plan Monitoring Details (Plan Hash Value=2193660895)
======================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
======================================================================================================================================================
| 0 | UPDATE STATEMENT | | | | | | 1 | | | |
| 1 | UPDATE | GLA_GLIS_H | | | 106 | +3 | 1 | 0 | 20.37 | Cpu (22) |
| 2 | PARTITION RANGE SINGLE | | 23106 | 900 | 99 | +10 | 1 | 755K | | |
| 3 | TABLE ACCESS FULL | GLA_GLIS_H | 23106 | 900 | 109 | +0 | 1 | 755K | 0.93 | Cpu (1) |
| 4 | SORT AGGREGATE | | 1 | | 99 | +10 | 755K | 755K | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 1158 | 107 | +2 | 755K | 755K | 4.63 | Cpu (5) |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID | GLA_GLIS_H | 1 | 1158 | 102 | +7 | 13M | 755K | 10.19 | Cpu (11) |
| 7 | INDEX RANGE SCAN | IDX_GLA_GLIS_H | 1 | 1157 | 108 | +1 | 13M | 755K | 63.89 | Cpu (69) |
======================================================================================================================================================優化結果前后對比：
原先主鍵索引對比：
加索引前：4520s（未完成）
加索引后：108s
墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用戶構建信賴可托付的數據庫環境，并為數據庫廠商提供中立的生態支持。
服務官網：https://www.modb.pro/service
