trigger，dblink造成update，insert慢

接到公司同事的电话求助，说客户那边一个table更新超级慢，更新一笔要20-30S，表的大小只有2W多笔，而且相关的栏位也都有索引。执行计划如下。

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  G_WO_BASE (cr=3 pr=0 pw=0 time=35289752 us)
      1   TABLE ACCESS BY INDEX ROWID G_WO_BASE (cr=3 pr=0 pw=0 time=85 us)
      1    INDEX RANGE SCAN G_WO_BASE_WO_IDX (cr=2 pr=0 pw=0 time=51 us)(object id 79366)

照理来说应该很快能更新完。开始百思不得其解。后来做了10046发现了问题。

 

 

TKPROF: Release 10.2.0.1.0 - Production on Sun Jul 17 14:20:07 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: F:\ORACLE\admin\ADATAMES\udump\adatames_ora_10840.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

UPDATE SAJET.RMA_WO_BASE@TAIWAN_RMA SET WORK_ORDER = :B4 , PART_ID = :B3 , TARGET_QTY = :B2 , WO_CREATE_DATE = :B1 WHERE WORK_ORDER = :B5

Error encountered: ORA-12170
********************************************************************************

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 8'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

UPDATE SAJET.G_WO_BASE SET WO_OPTION5='TEST2' 
WHERE
 WORK_ORDER='33002475'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.01      35.28          0          3          7           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03      35.29          0          3          7           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 67 

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  G_WO_BASE (cr=3 pr=0 pw=0 time=35289752 us)
      1   TABLE ACCESS BY INDEX ROWID G_WO_BASE (cr=3 pr=0 pw=0 time=85 us)
      1    INDEX RANGE SCAN G_WO_BASE_WO_IDX (cr=2 pr=0 pw=0 time=51 us)(object id 79366)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67 

 

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      3      0.01      35.29          0          3          7           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.03      35.29          0          3          7           1

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     4       13.08         21.66
  library cache pin                               5        2.99         14.13
  single-task message                             1        0.00          0.00

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    3  user  SQL statements in session.
    0  internal SQL statements in session.
    3  SQL statements in session.
********************************************************************************
Trace file: F:\ORACLE\admin\ADATAMES\udump\adatames_ora_10840.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       3  SQL statements in trace file.
       3  unique SQL statements in trace file.
      64  lines in trace file.
      57  elapsed seconds in trace file.

注意粗体部分。对g_wo_base做动作还要UPDATE SAJET.RMA_WO_BASE@TAIWAN_RMA ，怀疑有建trigger

 

一查果然如此。

CREATE OR REPLACE TRIGGER TRI_TAIWAN_WO_BASE_SYNC
AFTER DELETE OR INSERT OR UPDATE
   ON SAJET.G_WO_BASE
REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
BEGIN
   --sync with G_WO_BASE of Taiwan RMA system
   IF INSERTING
   THEN
      INSERT INTO SAJET.RMA_WO_BASE@TAIWAN_RMA (WORK_ORDER,
                                             PART_ID,
                                             TARGET_QTY,
                                             WO_CREATE_DATE)
        VALUES   (:new.WORK_ORDER,
                  :new.PART_ID,
                  :new.TARGET_QTY,
                  :new.WO_CREATE_DATE);
   ELSIF UPDATING
   THEN
      UPDATE   SAJET.RMA_WO_BASE@TAIWAN_RMA
         SET   WORK_ORDER = :new.WORK_ORDER,
               PART_ID = :new.PART_ID,
               TARGET_QTY = :new.TARGET_QTY,
               WO_CREATE_DATE = :new.WO_CREATE_DATE
       WHERE   WORK_ORDER = :old.WORK_ORDER;
   ELSIF DELETING
   THEN
      DELETE FROM  SAJET.RMA_WO_BASE@TAIWAN_RMA
            WHERE   WORK_ORDER = :old.WORK_ORDER;
   END IF;
  
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
END;

 

通过DBLink更新另外主机的table，而DBlink又不通，所以造成以上的问题。

这次避免了公司的责任，是由于客户的原因造成。为公司挽回声誉。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7958405/viewspace-702392/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7958405/viewspace-702392/