一条delete语句执行慢的处理

最新推荐文章于 2024-08-21 11:01:03 发布

转载最新推荐文章于 2024-08-21 11:01:03 发布 · 2.3k 阅读

文章标签：

本文介绍了一次关于慢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