本文介绍了一次关于慢Delete操作的排查经历。开发人员在删除少量数据时遇到长时间延迟问题，通过10046跟踪发现大量全表扫描SQL而非预期的Delete语句。最终定位到原因是由于存在大量的外键约束导致。
今天同事转给我一个问题，一个开发人员反馈在执行一个delete语句（delete from l_branch_mas l where l.branch_code in ('18','1801','1888');）的时候很慢，20分钟没出结果，这个表一共只有185条记录，同事怀疑有碎片，但是从dba_segments中查只有不到1M的数据，按理应该不会这么慢！
接到问题之后，我对它做了一个10046 的trace，发现trace半天里面内容全是类似下面的内容，很多根据br_code字段进行全表扫描的sql，完全没有我的delete语句！
select /*+ all_rows */ count(1)
from
"TLOANDATA"."LN_DUE_ANALYSE_STAT" where "BR_CODE" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.06 0.04 0 0 0 0
Fetch 3 0.57 0.56 0 15876 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.63 0.60 0 15876 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
********************************************************************************
select /*+ all_rows */ count(1)
from
"TLOANDATA"."L_APV_TRX" where "REQ_BR" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3 0.00 0.02 0 0 0 0
Fetch 3 0.52 0.80 12846 12864 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.52 0.82 12846 12864 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 816 0.03 0.54
********************************************************************************
select /*+ all_rows */ count(1)
from
"TLOANDATA"."LN_RISK_MANAGER_STAT" where "BR_CODE" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 3 0.04 0.03 0 0 0 0
Fetch 3 85.28 355.12 1065789 1067499 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 85.32 355.16 1065789 1067499 0 3
Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 66773 0.46 314.29
latch: shared pool 39 0.00 0.02
latch: cache buffers chains 3 0.00 0.00
latch free 1 0.00 0.00
db file sequential read 1 0.00 0.00
难道我搞错了？查了下我的表结构，我的表中branch_code字段在其它表中估计是叫br_code。这样就有头绪了，有别的表以l_branch_mas的branch_code字段做外键的约束，delete之前肯定是在检查这些约束。dba_constraints里面查了下，哇塞这么多，难怪我trace了半天全是些上面的结果，从最慢的那个表中查了下，有如下的外键：
alter table TLOANDATA.LN_RISK_MANAGER_STAT
add constraint FK_LN_RISK_MANAGER_STAT_BR foreign key (BR_CODE)
references TLOANDATA.L_BRANCH_MAS (BRANCH_CODE);
SQL> select owner,constraint_name,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where r_constraint_name in (select CONSTRAINT_NAME from dba_constraints where table_name='L_BRANCH_MAS');
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME
------------------------------ ------------------------------ --------------- ------------------------------
CFSSMEDATA FK_L_APPL_CUST_BRANCH R L_APPL_CUST
TLOANDATA FK_L_APV_STATUS_MAS_BR_CODE R L_APV_STATUS_MAS
TLOANDATA FK_L_TA_MAS_BR_CODE R L_TA_MAS
TLOANDATA FK_YSHOU_FIN_BR_CODE R LN_YSHOU_FIN_STAT
CGIDATA FK_I_SU_ACCOUNT_MAS_SU_DEPT_NO R I_SU_ACCOUNT_MAS
TLOANDATA FK_L_BR_ROLE_REL_BR_CODE R L_BR_ROLE_REL
TLOANDATA FK_L_CUSTOMER_MAS_CREATED_BR R L_CUSTOMER_MAS
TLOANDATA FK_L_LOAN_SEQ_CTL_BR_CODE R L_LOAN_SEQ_CTL
TLOANDATA FK_L_LAUNDER_RP_BR_CODE R L_ANTILAUNDER_MAS
CGIDATA FK_I_DUE_ANALYSE_STAT R I_DUE_ANALYSE_STAT
CFSAPVDATA FK_APV_STEP_AREAID R APV_TASK_MAS
TLOANDATA FK_L_LOAN_MAS_BR_CODE R L_LOAN_MAS
TLOANDATA FK_L_BOOKING_MAS_APPL_BRCODE R L_BOOKING_MAS
TLOANDATA FK_L_CCIS_RMK_BR_CODE R L_CCIS_RMK
TLOANDATA FK_LN_DUE_AN_STAT_HIST_BR R LN_DUE_ANALYSE_STAT_HIST
TLOANDATA FK_L_LN_TRX_ORG_ENTER_BY_BR R L_LN_TRX
TLOANDATA FK_LN_RISK_MANAGER_STAT_BR R LN_RISK_MANAGER_STAT
TLOANDATA FK_L_BR_VOUCHER_TRX_BR_CODE R L_BR_VOUCHER_TRX
TLOANDATA FK_YSHOU_BUSS_BR_CODE R LN_YSHOU_BUSS_STAT
CFSSMEDATA FK_L_APPL_MAS_BRCODE R L_APPLICATION_MAS
TLOANDATA FK_L_AP_DETAIL_MAS_ENTER_BY_BR R L_AP_DETAIL_MAS
TLOANDATA FK_L_ENROLL_MAS_BR_CODE R L_ENROLL_MAS
TLOANDATA FK_L_BR_APV_PROCESS_BR_CODE R L_BR_APV_PROCESS
TLOANDATA FK_L_BR_APV_STAFF_BR_CODE R L_BR_APV_STAFF
TLOANDATA FK_L_BR_JOURNAL_TRX_BR_CODE R L_BR_JOURNAL_TRX
TLOANDATA FK_L_LN_ACCT_CTL_BR_CODE R L_LN_ACCT_CTL
TLOANDATA FK_L_OVERDUE_STAT_BR_CODE R L_OVERDUE_STAT
TLOANDATA FK_LN_DUE_AN_STAT_BR_CODE R LN_DUE_ANALYSE_STAT
TLOANDATA FK_L_BRLOAN_MAS_BR_CODE R L_BRLOAN_MAS
TLOANDATA FK_L_LN_TRX_ENTER_BY_BR R L_LN_TRX
TLOANDATA FK_L_STAFF_MAS_BR_CODE R L_STAFF_MAS
TLOANDATA FK_L_BRANCH_INSU_REL_BC R L_BRANCH_INSU_REL
CFSCSDATA FK_L_CALL_CUSTOMER_BR_CODE R L_CALL_CUSTOMER_INFO
TLOANDATA FK_L_APV_TRX_REQ_BR R L_APV_TRX
TLOANDATA FK_L_BR_DOCUMENT_MAS_BR_CODE R L_BR_DOCUMENT_MAS
TLOANDATA FK_L_CUSTOMER_TMP_CREATED_BR R L_CUSTOMER_TMP
TLOANDATA FK_L_LN_STAT_TRX_BR_CODE R L_LN_STAT_TRX
TLOANDATA FK_L_LN_TRX_LN_BR R L_LN_TRX
38 rows selected
这个问题说明，不能一条道走到黑的，有时候换一个角度也许就柳暗花明了！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15779287/viewspace-678580/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15779287/viewspace-678580/
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
