問題描述：

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

問題分析：

需求是刪除一個(gè)主表A，另有兩個(gè)附表建有此表的主鍵ID的外鍵。刪除A表的數(shù)據(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查找存儲路徑：

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)改成別的方式獲?。∮谑莿h掉物化視圖。執(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

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

3. 啟用 10046 事件跟蹤當(dāng)前 session

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

驅(qū)動器 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 個(gè)文件        172,504 字節(jié)
   2 個(gè)目錄 22,060,634,112 可用字節(jié)

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

這將會對整個(gè)系統(tǒng)的性能產(chǎn)生嚴(yá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ù)名稱                       類型                    輸入/輸出默認(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的級別;

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)容了，希望大家多多支持腳本之家~