oracle 數(shù)據(jù)按主鍵刪除慢問題的解決方法

問題描述：

根據(jù)表主鍵id刪除一條數(shù)據(jù)，在PL/SQL上執(zhí)行commit后執(zhí)行時間都大于5秒。?。。?/p>

問題分析：

需求是刪除一個主表A，另有兩個附表建有此表的主鍵ID的外鍵。刪除A表的數(shù)據(jù)級聯(lián)刪除另兩個表的關(guān)聯(lián)數(shù)據(jù)。增刪改查使用hibernate實現(xiàn)。

一開始一直以為是hibernate的內(nèi)部處理上有關(guān)聯(lián)操作導(dǎo)致的刪除和更新數(shù)據(jù)緩慢。所以將原先使用hibernate的saveOrupdate方法，改查jdbc的
sql語句來處理update和delete數(shù)據(jù)操作。但是依然沒效果！?。?br /> 懷疑數(shù)據(jù)庫出問題了！~   

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

任何數(shù)據(jù)庫刪除一條數(shù)據(jù)不可能耗費5秒以上的時間??！那就要查看sql的執(zhí)行過程了！

網(wǎng)上搜了一堆資料查看。最后確定查看sql執(zhí)行跟蹤文件。 sql執(zhí)行是一次session，Oracle數(shù)據(jù)庫很好的支持sesion的跟蹤，鎖表情況等?？紤]要操作生

產(chǎn)數(shù)據(jù)庫。不能大量跟蹤session。于是選擇跟蹤指定sesion的方式，只查看自己執(zhí)行的sql執(zhí)行計劃！ 方式如下：

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/traceora11g_ora_42990.trc

然后到服務(wù)器上取下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"

原來在刪除之后都有個物化視圖的刷新操作！?。?/p>

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

通過這次問題處理，總結(jié)以下教訓(xùn)：

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

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

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

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

2. 查看等待事件，

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

4. 查看系統(tǒng)IO，

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

1. 使用 AUTOTRACE 查看執(zhí)行計劃

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

2.  啟用 sql_trace 跟蹤當前 session

開啟會話跟蹤：alter session set sql_trace=true;
關(guān)閉會話跟蹤：alter session set sql_trace=false

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

開啟會話跟蹤：alter session set events '10046 trace name context forever, level 12';
關(guān)閉會話跟蹤：alter session set events '10046 trace name context off';
對跟蹤文件加標識：alter session set tracefile_identifier='dragon';
SQL> host dir E:\ORACLEPRODUCT10.2.0ADMINBYISDBUDUMP

驅(qū)動器 E 中的卷是 DISK1_VOL3

卷的序列號是 609E-62D9

E:\ORACLEPRODUCT10.2.0ADMINBYISDBUDUMP 的目錄
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 字節(jié)
   2 個目錄 22,060,634,112 可用字節(jié)

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

這將會對整個系統(tǒng)的性能產(chǎn)生嚴重的影響，所以一般不建議開啟。

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

關(guān)閉會話跟蹤：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 系統(tǒng)包 DBMS_SYSTEM.SET_EV 跟蹤指定 session

PROCEDURE SET_EV

參數(shù)名稱                       類型                    輸入/輸出默認值?

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

 SI                             BINARY_INTEGER          IN

 SE                            BINARY_INTEGER          IN

 EV                            BINARY_INTEGER          IN

 LE                            BINARY_INTEGER          IN

 NM                           VARCHAR2                    IN

參數(shù)說明：

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, '');

關(guān)閉會話跟蹤：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

以上就是小編為大家?guī)淼膐racle 數(shù)據(jù)按主鍵刪除慢問題的解決方法全部內(nèi)容了，希望大家多多支持腳本之家~