客户核心生产环境碰到系统性能问题，本文记录了如何来一步步排查和判断原因、如何解决问题的步骤。
故障描述
2024年07月11日下午根据客户的描述，核心业务系统数据库从07月11日开始在12点30分开始到17点15分左右卡顿。当天下午16点后问题更加严重导致业务中断。二次发生问题时间点在7月15下午14点30份开始一直到16点，问题依旧。
问题分析
AWR报告定位
通过7月11日的问题时间段的AWR报告定位，基本上可以判断由于SQL性能引起的行锁，造成系统卡顿。
深入定位
通过查询dba_hist_active_sess_history，在故障时间有大量enq: TX - row lock contention行锁同时伴随着db file sequential read的索引读IO，基本上可以判断SQL查询引起的死锁，如下图：
再根据上图sql_id我们可以定位到死锁和被锁的SQL语句分别是如下图红色和蓝色（被锁：UPDATE SCM_BIZ_STOCK SET ALLO_QTY = ALLO_QTY - :B6 , ALLO_OUT_DATE = :B5 ,SYSOPTDATE = SYSDATE WHERE COMPID = :B4 AND OWNERID = :B3 AND GOODID = :B2 AND STATUS = :B1，产生死锁：SELECT TRUNC(SUM(REALVALUE*NVL(TOINTEGRALRATE,0))) FROM SCM_SALBILL_HDR WHERE COMPID = :B4 AND OWNERID = :B3 AND SALEDEPTID = :B2 AND GRPID = :B1 AND STOPFLAG = ‘00’）
二次问题定位
7月15下午2点30份开始，通过有问题时间段的ASH报告我们同样发现还是enq: TX - row lock contention行锁伴随着大量的db file sequential read索引读IO，sql_id是byjmapum4yvm0，（SELECT SUM(NVL(SUMVALUE,0)),SUM(NVL(OFFVALUE,0)),SUM(NVL(REALVALUE,0)) FROM SCM_SALBILL_HDR SSH WHERE COMPID = :B4 AND OWNERID = :B3 AND SALEDEPTID = :B2 AND GRPID = :B1 AND STOPFLAG = ‘00’）如下图：
继续根据等待事件定位到具体SQL，如下图：
因为我们已经知道了有问题的SQL_ID(byjmapum4yvm0)了，再通过AWRSQRPT报告来解读一下，我们发现这个SQL语句执行需要18,872,257/72=262114.68毫秒，也就是说每次执行这条SQL语句需要4分钟，再往下看都在等IO，执行计划的细节是走了IDX_SCM_SALBILL_COSGSC索引：
按照正常情况下应该走如下索引：
但是实际走了错误的IDX_SCM_SALBILL_COSGSC索引
问题解决
核心业务调用SCM_SALBILL_RTLFLOW.SCM_SHOPSALE_POSTDEAL包时间过长，SQL走了错误的索引，造成行锁。IDX_SCM_SALBILL_COSGSC索引不合理，解决方式删除该索引drop index CMSDTP.IDX_SCM_SALBILL_COSGSC;后续再跟踪一下系统性能情况。由于生产环境内存sga和pga只有8G和2G，可以考虑升级一下硬件比如增加内存，上SSD存储磁盘。
oracle@gyoa01:/home/oracle> gocmsdtp
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 7月 15 18:42:26 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show user;
USER is "SYS"
SQL> drop index CMSDTP.IDX_SCM_SALBILL_COSGSC;
Index dropped.
SQL>
总结
我们可以通过AWR、ASH、AWRSQRPT等报告结合dba_hist_active_sess_history视图来定位问题所在，本次案例还是由于SQL性能，走错误索引问题导致。希望给小伙伴们一些排查系统性能问题的思路。
