程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE DELETE數據慢的案例,oracledelete案例

ORACLE DELETE數據慢的案例,oracledelete案例

編輯:Oracle教程

ORACLE DELETE數據慢的案例,oracledelete案例


    今天遇到一個有意思的案例,一開發同事告訴我他刪除一個表的記錄非常慢,已經快1個多小時了還沒有完成。而且刪除的記錄只有1百多條。真是大跌眼鏡的一件事情。最後發現該表與多個表有外鍵關聯關系(這個表即是主表、又是從表),最後我禁用引用該表的外鍵約束後。一秒內刪除了記錄。然後啟用外鍵約束關系。下面記錄、分析一下解決過程的思路(下面是在測試環境的記錄,數據量不一樣)。

我去處理這個問題時,首先懷疑可能是SQL的阻塞、觸發器、外鍵約束、高水位線等因素中的某一個導致DELETE操作慢,於是我打算一個一個排除,我先試著刪除一條記錄,然後去檢查SQL的阻塞情況,結果使用下面SQL語句並沒有發現SQL被阻塞。於是SQL的阻塞導致DELETE慢的原因被我排除了。

SELECT '節點 ' || A.INST_ID || ' SESSION ' || A.SID || ',' || A_S.SERIAL# ||
       ' 阻塞了 節點 ' || B.INST_ID || ' SESSION ' || B.SID || ',' || B_S.SERIAL# BLOCKINFO,
       A.INST_ID,
       A_S.SID,
       A_S.SCHEMANAME,
       A_S.MODULE,
       A_S.STATUS,
       A.TYPE LOCK_TYPE,
       A.ID1,
       A.ID2,
       DECODE(A.LMODE,
              0,
              'NONE',
              1,
              NULL,
              2,
              'ROW-S (SS)',
              3,
              'ROW-X (SX)',
              4,
              'SHARE (S)',
              5,
              'S/ROW-X (SSX)',
              6,
              'EXCLUSIVE (X)') LOCK_MODE,
       
       '後為被阻塞信息' ,
       B.INST_ID BLOCKED_INST_ID,
       B_S.SID BLOCKED_SID,
       B.TYPE BLOCKED_LOCK_TYPE,
       DECODE(B.REQUEST,
              0,
              'NONE',
              1,
              NULL,
              2,
              'ROW-S (SS)',
              3,
              'ROW-X (SX)',
              4,
              'SHARE (S)',
              5,
              'S/ROW-X (SSX)',
              6,
              'EXCLUSIVE (X)') BLOCKED_LOCK_REQUEST,
       B_S.SCHEMANAME BLOCKED_SCHEMANAME,
       B_S.MODULE BLOCKED_MODULE,
       B_S.STATUS BLOCKED_STATUS,
       B_S.SQL_ID BLOCKED_SQL_ID,
       OBJ.OWNER BLOCKED_OWNER,
       OBJ.OBJECT_NAME BLOCKED_OBJECT_NAME,
       OBJ.OBJECT_TYPE BLOCKED_OBJECT_TYPE,
       CASE
          WHEN B_S.ROW_WAIT_OBJ# <> -1 THEN
             DBMS_ROWID.ROWID_CREATE(1,
                                     OBJ.DATA_OBJECT_ID,
                                     B_S.ROW_WAIT_FILE#,
                                     B_S.ROW_WAIT_BLOCK#,
                                     B_S.ROW_WAIT_ROW#) 
          ELSE 
            '-1' 
          END BLOCKED_ROWID, --THE BLOCKED ROWID
       DECODE(OBJ.OBJECT_TYPE,
              'TABLE',
              'SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||
              ' WHERE ROWID=''' ||
              DBMS_ROWID.ROWID_CREATE(1,
                                      OBJ.DATA_OBJECT_ID,
                                      B_S.ROW_WAIT_FILE#,
                                      B_S.ROW_WAIT_BLOCK#,
                                      B_S.ROW_WAIT_ROW#) || '''',
              NULL)  BLOCKED_DATA_QUERYSQL
  FROM GV$LOCK     A,
       GV$LOCK     B,
       GV$SESSION  A_S,
       GV$SESSION  B_S,
       DBA_OBJECTS OBJ
 WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND A.BLOCK > 0 --BLOCK THE OTHER SQL
   AND B.REQUEST > 0
   AND ((A.INST_ID = B.INST_ID AND A.SID <> B.SID) OR
       (A.INST_ID <> B.INST_ID))
   AND A.SID = A_S.SID
   AND A.INST_ID = A_S.INST_ID
   AND B.SID = B_S.SID
   AND B.INST_ID = B_S.INST_ID
   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)
 ORDER BY A.INST_ID,A.SID;

接下來,我檢查了該表的的觸發器,結果並沒有發現DELETE觸發器。也就是說DELETE操作並不會觸發任何觸發器。觸發器導致DELETE慢的懷疑也可以排除掉了。

SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='INV_LOCATION_PALLETS'

我用show_space檢查了一下這個表的高水位線,發現並沒有問題,不需要收縮高水位線。高水位線這個因素也可以排除了。只剩下外鍵約束的影響了。於是檢查了一下有哪些表是該表的從表,如下所示

SELECT /*+RULE*/ D.CONSTRAINT_NAME PK_NAME,
                 D.TABLE_NAME 
                 || '.' 
                 || D.COLUMN_NAME  PK_COLUMN,
                 A.CONSTRAINT_TYPE,
                 B.CONSTRAINT_NAME FK_NAME,
                 B.TABLE_NAME 
                 || '.' 
                 || B.COLUMN_NAME  FK_COLUMN
FROM   DBA_CONSTRAINTS A 
       JOIN DBA_CONS_COLUMNS B 
         ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
            AND A.OWNER = B.OWNER
       JOIN DBA_CONSTRAINTS C 
         ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
            AND A.R_OWNER = C.OWNER
       JOIN DBA_CONS_COLUMNS D 
         ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
            AND C.OWNER = D.OWNER
WHERE  D.TABLE_NAME = 'INV_LOCATION_PALLETS' 

有時候也可以用下面語句查看引用這個表的外鍵引用關系

SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='PK_INV_LOCATION_PALLETS'

如上所示,INV_LOCATION_PALLETS這個表有三個從表,而這裡面有個表的記錄很大,大概2千多萬。所以導致DELETE操作很慢。

我們可以用跟蹤當前會話,查看一下DELETE操作,就會發現它會去處理從表,檢查從表有沒有對應的記錄,而這個外鍵剛好也沒有索引(下面是使用tkprof命令格式化的內容)。在這篇Delete the data on the table very slow(刪除數據慢)博客裡面跟深入的介紹、分析了刪除表刪除數據慢的原因。在此不做過多贅述了。

DELETE INVENTORY.INV_LOCATION_PALLETS  
WHERE
 PALLET_ID =1039928
 
 
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          3         26           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          3         26           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  DELETE  INV_LOCATION_PALLETS (cr=752149 pr=735050 pw=0 time=7550289 us)
      1   INDEX UNIQUE SCAN PK_INV_LOCATION_PALLETS (cr=3 pr=0 pw=0 time=38 us)(object id 59532)
 
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_HD" where "TO_PALLET_ID" = :1
 
 
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        1      0.75       0.74      70540      78205          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.75       0.74      70540      78205          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=78205 pr=70540 pw=0 time=743169 us)
      0   TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70540 pw=0 time=743155 us)
 
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_HD" where "FROM_PALLET_ID" = :1
 
 
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        1      0.69       0.67      70528      78205          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.69       0.68      70528      78205          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=78205 pr=70528 pw=0 time=680000 us)
      0   TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70528 pw=0 time=679987 us)
 
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_LINES" where "TO_PALLET_ID" = :1
 
 
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        1      3.30       3.23     296991     297868          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.30       3.23     296991     297868          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=3232134 us)
      0   TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=3232122 us)
 
********************************************************************************
 
select /*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_LINES" where "FROM_PALLET_ID" = :1
 
 
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        1      2.94       2.88     296991     297868          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      2.94       2.88     296991     297868          0           1
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=2885783 us)
      0   TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=2885772 us)
 
********************************************************************************
 
begin
  sys.dbms_output.get_line(line => :line, status => :status);
end;
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.00       0.00          0          0          0           4
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5  

 

 

參考資料

http://www.anbob.com/archives/1962.html/comment-page-1

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved