今天通过监控发现，促销数据库存在大量的行级锁等待，详见下图。

通过监控发现，导致问题的是BI的两条UPDATE语句造成的。

以下是相关的SQL信息：

update MEDA_PROM.ZT_YPH_USER a set a.level_id= (select b.cst_gd_cd from MEDA_PROM.TEMP_USER_OLD b where b.CST_ID=a.id) where a.id in(select d.cst_id from MEDA_PROM.TEMP_USER_OLD d)

update MEDA_PROM.ZT_YPH_USER a set a.total_buy_count= (select b.total_buy_count from MEDA_PROM.TEMP_USER_JE b where b.CST_ID=a.id) where a.id in(select b.CST_ID from MEDA_PROM.TEMP_USER_JE b)

以下是锁的相关信息：

通过查询，发现只有ZT_YPH_USER表的ID字段存在主键，其他表的字段均没有索引。

SQL> select index_name,COLUMN_NAME,table_name from dba_ind_columns where TABLE_OWNER='MEDA_PROM' and table_name in ('ZT_YPH_USER','TEMP_USER_OLD','TEMP_USER_JE');

INDEX_NAME                     COLUMN_NAME          TABLE_NAME
------------------------------ -------------------- ------------------------------
ZT_YPH_USER_PK                 ID                   ZT_YPH_USER

以下是这三张表的数据量相关信息。

SQL> select count(*) from MEDA_PROM.ZT_YPH_USER;

  COUNT(*)
----------
  14555416

SQL> select count(*) from MEDA_PROM.TEMP_USER_OLD;

  COUNT(*)
----------
       481

SQL> select count(*) from MEDA_PROM.TEMP_USER_JE;

  COUNT(*)
----------
       670

以下是第一条SQL的执行计划信息。

通过查看，表关联使用的字段，字段类型不匹配，该SQL需要进行隐式转换，ZT_YPH_USER表的ID字段是NUMBER类型，而和它关联的TEMP_USER_OLD表的CST_ID和TEMP_USER_JE表的CST_ID都是VARCHAR2类型。

SQL> desc  MEDA_PROM.ZT_YPH_USER
 Name                      Null?              Type
 --------------------     --------            -----------
 ID                       NOT NULL            NUMBER(20)
 USER_NAME                                    VARCHAR2(200)
 GNR_TP_CD                                    VARCHAR2(7)
 BRY_DATE                                     VARCHAR2(12)
 LEVEL_ID                                     NUMBER(20)
 PVC                                          VARCHAR2(300)
 CITY                                         VARCHAR2(300)
 JOIN_DTM                                     DATE
 TOTAL_BUY_COUNT                              NUMBER(20)
 TOTAL_BUY_MONEY                              NUMBER(20)
 TOTAL_BUY_POINT                              NUMBER(20)
 CHANNEL                                      VARCHAR2(40)
 SYNC_DATE                NOT NULL            DATE

SQL> desc MEDA_PROM.TEMP_USER_OLD
 Name                     Null?               Type
 ----------------------  --------            --------------
 CST_ID                                     VARCHAR2(40)
 CST_GD_CD                                  VARCHAR2(7)

SQL> desc MEDA_PROM.TEMP_USER_JE;
Name                     Null?               Type
 ----------------------  --------            --------------
CST_ID                                      VARCHAR2(40)
 TOTAL_BUY_COUNT                             NUMBER(38)
 TOTAL_BUY_MONEY                             NUMBER(20)

通过在TEMP_USER_OLD和TEMP_USER_JE表的CST_ID字段创建函数索引(TO_NUMBER)，问题即可解决。

SQL> select index_name,COLUMN_NAME,table_name from dba_ind_columns where TABLE_OWNER='MEDA_PROM' and table_name in ('ZT_YPH_USER','TEMP_USER_OLD','TEMP_USER_JE');

INDEX_NAME                     COLUMN_NAME          TABLE_NAME
------------------------------ -------------------- ------------------------------
IDX_TEMP_USER_OLD_CST_ID       CST_ID               TEMP_USER_OLD
IDX_TEMP_USER_JE_CST _ID       CST_ID               TEMP_USER_JE
ZT_YPH_USER_PK                 ID                   ZT_YPH_USER

经过和BI的负责人沟通，知道这条两条SQL为每小时运行一次，从BI数据库向这个数据库推送数据，经沟通后，最终将TEMP_USER_OLD和TEMP_USER_JE表的CST_ID字段修改为NUMBER类型，问题解决。

本文固定链接: http://www.dbdream.com.cn/2016/03/%e4%b8%80%e6%ac%a1oracle%e6%95%b0%e6%8d%ae%e5%ba%93%e8%a1%8c%e7%ba%a7%e9%94%81%e5%bc%82%e5%b8%b8%e6%95%85%e9%9a%9c/ | 信春哥，系统稳，闭眼上线不回滚！