同事給我反饋一個問題，刪除某個表上的資料非常的慢。即使是透過主鍵刪除，也是非常的慢，刪除1條資料要1分鐘。我測試刪除了2000條，花了差不多12分鐘。

我看了執行計劃，和預期一樣，走了索引，效率貌似很高。

看了一下這個表，發現存在很多外來鍵關聯，把外來鍵全部刪除，再測試資料刪除，依然很慢。

透過v$session ,v$sqlarea 和 v$session_wait 找到該sql對應的event為“db file sequential read”，好像也正常。

懷疑是表空間碎片，執行如下語句

alter table xxx enable row movement;

alter table xxx shrink space;

select a.segment_name,a.bytes,a.blocks from user_segments a where segment_name = 'XXX';

前後變化不大，看來不是這問題。

後面，想到用trace方法跟蹤delete事件，發現trace檔案裡面多了幾個多餘的select語句，應該都是系統自動產生的。根據這幾個可疑的sql語句，發現原來是因為有幾個表的外來鍵引用了我的刪除表，而這些外來鍵上又沒有建立索引，導致每次刪除這個表的記錄，都要去全表掃描那幾個關聯表。

於是，在那幾個表的外來鍵上新建索引。再次刪除，測試效果，搞定。

附上trace指令碼：

alter session set events='10046 trace name context forever,level 12';

執行要跟蹤的SQL

alter session set events='10046 trace name context off';

完成後，到udump目錄找到trace檔案，拉到本地，使用命令轉換

D:\>tkprof orcl_ora_21999.trc myoutput.txt

另一種跟蹤方式，執行完sql用這個跟蹤

exec dbms_system.set_sql_trace_in_session(SID, SERIAL# ,true);

exec dbms_system.set_sql_trace_in_session(SID, SERIAL# ,false);

資料刪除慢的原因排查

相關文章