故障"/>
oracle 11g的ae锁,一次ORACLE数据库行级锁异常故障
今天通过监控发现,促销数据库存在大量的行级锁等待,详见下图。
通过监控发现,导致问题的是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类型,问题解决。
更多推荐
oracle 11g的ae锁,一次ORACLE数据库行级锁异常故障
发布评论