技术分享 | Oracle SQL优化案例一则

本文为墨天轮数据库管理服务团队第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) | 20191231

select 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