程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> oracle 數據按主鍵刪除慢問題的解決方法

oracle 數據按主鍵刪除慢問題的解決方法

編輯：關於Oracle數據庫

問題描述：

根據表主鍵id刪除一條數據，在PL/SQL上執行commit後執行時間都大於5秒。！！！

問題分析：

需求是刪除一個主表A，另有兩個附表建有此表的主鍵ID的外鍵。刪除A表的數據級聯刪除另兩個表的關聯數據。增刪改查使用hibernate實現。

一開始一直以為是hibernate的內部處理上有關聯操作導致的刪除和更新數據緩慢。所以將原先使用hibernate的saveOrupdate方法，改查jdbc的
sql語句來處理update和delete數據操作。但是依然沒效果！！！
懷疑數據庫出問題了！~

於是拿sql語句在PL/SQL客戶端執行，查看執行計劃。刪除和更新都能使用到索引。但是commit後執行依然很慢！因此可以判斷出是數據庫方面的問題。

任何數據庫刪除一條數據不可能耗費5秒以上的時間啊！那就要查看sql的執行過程了！

網上搜了一堆資料查看。最後確定查看sql執行跟蹤文件。 sql執行是一次session，Oracle數據庫很好的支持sesion的跟蹤，鎖表情況等。考慮要操作生

產數據庫。不能大量跟蹤session。於是選擇跟蹤指定sesion的方式，只查看自己執行的sql執行計劃！方式如下：

alter session set events='10046 trace name context forever,level 12'; --- 固定語句
delete from t_table1 where id = 23242342; --- 你要跟蹤的sql語句
alter session set events='10046 trace name context off';--- 固定語句

SQL跟蹤得到一個trace文件：

通過sql查找存儲路徑：

select pr.value || '\' || i.instance_name || '_ora_' || to_char(ps.spid) 
|| '.trc' "trace file name" from v$session s, v$process ps, v$parameter pr, v$instance i 
where s.paddr = ps.addr and s.sid = userenv('sid') and pr.name = 'user_dump_dest';

/home/oracle/DBSoftware/diag/rdbms/ora11g/ora11g/trace\ora11g_ora_42990.trc

然後到服務器上取下trc文件。

打開查看到：

/* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"

還有：

4311 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"
4402/*MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_PORT_REL_AREA"("ID","ACCOUNT_ID","PORT_ID","DEV_IP","PORT_IDEN","AREA_NAME") SELECT "PR"."ID","PR"."ACCOUNT_ID","PR"."PORT_ID","D"."DEV_IP","P"."PORT_IDEN","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL" "PR","TB_PORT" "P","TB_DEVICE" "D","TB_AREA" "A" WHERE "PR"."PORT_ID"="P"."ID" AND "P"."DEV_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID"
 5309 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_FTTH_REL_AREA"
5482 /* MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_FTTH_REL_AREA"("ID","ACCOUNT_ID","ONU_INFO_ID","DEV_IP","ONU_DESC","AREA_NAME") SELECT "PRH"."ID","PRH"."ACCOUNT_ID","PRH"."ONU_INFO_ID","D"."DEV_IP","O"."ONU_DESC","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL_FTTH" "PRH","TB_ONU_INFO" "O","TB_DEVICE" "D","TB_AREA" "A" WHERE "PRH"."ONU_INFO_ID"="O"."ID" AND "O"."OLT_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID"
 9984 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"
10061 /* MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_PORT_REL_AREA"("ID","ACCOUNT_ID","PORT_ID","DEV_IP","PORT_IDEN","AREA_NAME") SELECT "PR"."ID","PR"."ACCOUNT_ID","PR"."PORT_ID","D"."DEV_IP","P"."PORT_IDEN","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL" "PR","TB_PORT" "P","TB_DEVICE" "D","TB_AREA" "A" WHERE "PR"."PORT_ID"="P"."ID" AND "P"."DEV_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID"

原來在刪除之後都有個物化視圖的刷新操作！！！

oh. 買噶！想起在做這個主表的操作時有個物化視圖隨基表變化而立即刷新的操作！基表有10多萬條數據，物化視圖關聯了多張表。單獨刷新也要幾秒時間！就是這樣原因了！實際現在已經不需要這個物化視圖了，所需查詢數據已經改成別的方式獲取！於是刪掉物化視圖。執行刪除，更新，0.003秒！問題解決！

通過這次問題處理，總結以下教訓：

1. 物化視圖盡量不要做成立即刷新模式，這樣如果基表更新頻繁性能問題立馬出現。如果確需做物化視圖，做成job定時在基表使用閒時執行。

2. 在PL/SQL等客戶端執行sql查詢基本的數據或刪除更新很少數據量而時間超過一秒的就要想法跟蹤下sql執行計劃了。

3. sql執行計劃跟蹤采用如下幾種方式：

1.首先查看SQL的執行計劃，執行計劃正常，cost只有4，用到了主鍵索引

2. 查看等待事件，

3. select * from v$session_wait where sid = 507

4. 查看系統IO，

--------------------------------------

1. 使用 AUTOTRACE 查看執行計劃

set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
set autotrace OFF

2. 啟用 sql_trace 跟蹤當前 session

開啟會話跟蹤：alter session set sql_trace=true;
關閉會話跟蹤：alter session set sql_trace=false

3. 啟用 10046 事件跟蹤當前 session

開啟會話跟蹤：alter session set events '10046 trace name context forever, level 12';
關閉會話跟蹤：alter session set events '10046 trace name context off';
對跟蹤文件加標識：alter session set tracefile_identifier='dragon';
SQL> host dir E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\

驅動器 E 中的卷是 DISK1_VOL3

卷的序列號是 609E-62D9

E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP 的目錄
2012-07-19 17:58    <DIR>          .
2012-07-19 17:58    <DIR>          ..
2012-07-19 17:58             3,057 byisdb_ora_704.trc
2012-07-19 17:58           169,447 byisdb_ora_704_dragon.trc

2 個文件 172,504 字節
2 個目錄 22,060,634,112 可用字節

4. 啟用 10046 事件跟蹤全局 session

這將會對整個系統的性能產生嚴重的影響，所以一般不建議開啟。

開啟會話跟蹤：alter system set events ‘10046 trace name context forever, level 12';

關閉會話跟蹤：alter system set events ‘10046 trace name context off';

獲取跟蹤文件

SQL> select pr.value || '\' || i.instance_name || '_ora_' || to_char(ps.spid) 
|| '.trc' "trace file name" from v$session s, v$process ps, v$parameter pr, v$instance i 
where s.paddr = ps.addr and s.sid = userenv('sid') and pr.name = 'user_dump_dest';

trace file name
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\byisdb_ora_372.trc

5. 使用 Oracle 系統包 DBMS_SYSTEM.SET_EV 跟蹤指定 session

PROCEDURE SET_EV

參數名稱類型輸入/輸出默認值?

------------------------------ ----------------------- ------ --------

SI BINARY_INTEGER IN

SE BINARY_INTEGER IN

EV BINARY_INTEGER IN

LE BINARY_INTEGER IN

NM VARCHAR2 IN

參數說明：

SI-指定SESSION的SID;

SE-指定SESSION的SE;

EV-事件ID(如：10046);

LE-表示TRACE的級別;

NM-指定SESSION的username;

SQL> select userenv('sid') sid from dual;
    SID
----------
    143

SQL> select sid, serial#, username from v$session where sid=143;
    SID  SERIAL# USERNAME
---------- ---------- ------------------------------
    143    112 UNA_HR

開啟會話跟蹤：SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');

關閉會話跟蹤：SQL> exec dbms_system.set_ev(143, 112, 10046, 0, '');

6. 使用 TKPROF 工具格式化

tkprof tracefile outputfile [options]

E:\oracle\product\10.2.0\admin\byisdb\udump>tkprof byisdb_ora_704.trc 10046.txt sys=no sort=prsela, exeela, fchela

以上就是小編為大家帶來的oracle 數據按主鍵刪除慢問題的解決方法全部內容了，希望大家多多支持~