問題描述：
根據(jù)表主鍵id刪除一條數(shù)據(jù)，在PL/SQL上執(zhí)行commit后執(zhí)行時(shí)間都大于5秒。！！！
問題分析：
需求是刪除一個(gè)主表A，另有兩個(gè)附表建有此表的主鍵ID的外鍵。刪除A表的數(shù)據(jù)級(jí)聯(lián)刪除另兩個(gè)表的關(guān)聯(lián)數(shù)據(jù)。增刪改查使用hibernate實(shí)現(xiàn)。
一開始一直以為是hibernate的內(nèi)部處理上有關(guān)聯(lián)操作導(dǎo)致的刪除和更新數(shù)據(jù)緩慢。所以將原先使用hibernate的saveOrupdate方法，改查jdbc的
sql語句來處理update和delete數(shù)據(jù)操作。但是依然沒效果！！！
懷疑數(shù)據(jù)庫出問題了！~
于是拿sql語句在PL/SQL客戶端執(zhí)行，查看執(zhí)行計(jì)劃。刪除和更新都能使用到索引。但是commit后執(zhí)行依然很慢！ 因此可以判斷出是數(shù)據(jù)庫方面的問題。
任何數(shù)據(jù)庫刪除一條數(shù)據(jù)不可能耗費(fèi)5秒以上的時(shí)間啊！那就要查看sql的執(zhí)行過程了！
網(wǎng)上搜了一堆資料查看。最后確定查看sql執(zhí)行跟蹤文件。 sql執(zhí)行是一次session，Oracle數(shù)據(jù)庫很好的支持sesion的跟蹤，鎖表情況等。考慮要操作生
產(chǎn)數(shù)據(jù)庫。不能大量跟蹤session。于是選擇跟蹤指定sesion的方式，只查看自己執(zhí)行的sql執(zhí)行計(jì)劃！ 方式如下：
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跟蹤得到一個(gè)trace文件：
通過sql查找存儲(chǔ)路徑：
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
然后到服務(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"原來在刪除之后都有個(gè)物化視圖的刷新操作！！！
oh. 買噶！ 想起在做這個(gè)主表的操作時(shí)有個(gè)物化視圖隨基表變化而立即刷新的操作！基表有10多萬條數(shù)據(jù)，物化視圖關(guān)聯(lián)了多張表。單獨(dú)刷新也要幾秒時(shí)間！就是這樣原因了！實(shí)際現(xiàn)在已經(jīng)不需要這個(gè)物化視圖了，所需查詢數(shù)據(jù)已經(jīng)改成別的方式獲取！于是刪掉物化視圖。執(zhí)行刪除，更新，0.003秒！問題解決！
通過這次問題處理，總結(jié)以下教訓(xùn)：
1. 物化視圖盡量不要做成立即刷新模式，這樣如果基表更新頻繁性能問題立馬出現(xiàn)。如果確需做物化視圖，做成job定時(shí)在基表使用閑時(shí)執(zhí)行。
2. 在PL/SQL等客戶端執(zhí)行sql查詢基本的數(shù)據(jù)或刪除更新很少數(shù)據(jù)量而時(shí)間超過一秒的就要想法跟蹤下sql執(zhí)行計(jì)劃了。
3. sql執(zhí)行計(jì)劃跟蹤采用如下幾種方式：
1.首先查看SQL的執(zhí)行計(jì)劃，執(zhí)行計(jì)劃正常，cost只有4，用到了主鍵索引
2. 查看等待事件，
3. select * from v$session_wait where sid = 507
4. 查看系統(tǒng)IO，
--------------------------------------
1. 使用 AUTOTRACE 查看執(zhí)行計(jì)劃
set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
set autotrace OFF
2. 啟用 sql_trace 跟蹤當(dāng)前 session
開啟會(huì)話跟蹤：alter session set sql_trace=true;
關(guān)閉會(huì)話跟蹤：alter session set sql_trace=false
3. 啟用 10046 事件跟蹤當(dāng)前 session
開啟會(huì)話跟蹤：alter session set events '10046 trace name context forever, level 12';
關(guān)閉會(huì)話跟蹤：alter session set events '10046 trace name context off';
對(duì)跟蹤文件加標(biāo)識(shí)：alter session set tracefile_identifier='dragon';
SQL> host dir E:/ORACLE/PRODUCT/10.2.0/ADMIN/BYISDB/UDUMP/
驅(qū)動(dòng)器 E 中的卷是 DISK1_VOL3
卷的序列號(hào)是 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 個(gè)文件 172,504 字節(jié)
2 個(gè)目錄 22,060,634,112 可用字節(jié)
4. 啟用 10046 事件跟蹤全局 session
這將會(huì)對(duì)整個(gè)系統(tǒng)的性能產(chǎn)生嚴(yán)重的影響，所以一般不建議開啟。
開啟會(huì)話跟蹤：alter system set events ‘10046 trace name context forever, level 12';
關(guān)閉會(huì)話跟蹤：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.trc5. 使用 Oracle 系統(tǒng)包 DBMS_SYSTEM.SET_EV 跟蹤指定 session
PROCEDURE SET_EV
參數(shù)名稱 類型 輸入/輸出默認(rèn)值?
------------------------------ ----------------------- ------ --------
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的級(jí)別;
NM-指定SESSION的username;
SQL> select userenv('sid') sid from dual; SID---------- 143SQL> select sid, serial#, username from v$session where sid=143; SID SERIAL# USERNAME---------- ---------- ------------------------------ 143 112 UNA_HR開啟會(huì)話跟蹤：SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');
關(guān)閉會(huì)話跟蹤：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)容了，希望大家多多支持VeVb武林網(wǎng)~
新聞熱點(diǎn)
疑難解答
圖片精選
