1、Oracle 數(shù)據(jù)庫(kù)編程調(diào)優(yōu)手冊(cè)數(shù)據(jù)庫(kù)編程調(diào)優(yōu)手冊(cè) 目錄目錄 前 言.4 一、ORACLE 數(shù)據(jù)加載優(yōu)化(SQLLoader).4 二、UPDATE 優(yōu)化.4 1、對(duì)全表的 Update 操作.4 2、有條件的 Update 操作.5 3、多表連接 Update 操作.5 4、兩張表關(guān)聯(lián) Update 的三種方式.6 5、用視圖代替對(duì)表的 Update 操作.6 三、DELETE 優(yōu)化 .6 1、通過(guò)分拆表，避免 DELETE 操作 .6 2、通過(guò)中間表，用 INSERT 代替 DELETE 操作 .6 四、DBA 級(jí)別優(yōu)化提示.6 五、其它優(yōu)化提示.6 前前 言言 本文根據(jù)最近一段時(shí)間，技術(shù)

2、部與 6.0 項(xiàng)目組共同在做系統(tǒng)優(yōu)化過(guò)程中，最常用的做 法做一個(gè)總結(jié)。 目前只收錄最近優(yōu)化時(shí)的一些做法，供各項(xiàng)目組參考。各個(gè)項(xiàng)目組在實(shí)際開發(fā)過(guò)程中， 如有其它優(yōu)化 SQL 的技巧，也請(qǐng)?zhí)峤患夹g(shù)部，形成技術(shù)文檔，供各項(xiàng)目組參考。 性能優(yōu)化的幾個(gè)原則： 1.簡(jiǎn)化業(yè)務(wù)流程是提高性能的最可靠的方法簡(jiǎn)化業(yè)務(wù)流程是提高性能的最可靠的方法-它可以給你的性能帶來(lái)成倍的提升。它可以給你的性能帶來(lái)成倍的提升。 2.不要把希望寄托在不要把希望寄托在 DBA 得身上，它不可能給你的性能帶來(lái)成倍的提升。得身上，它不可能給你的性能帶來(lái)成倍的提升。 3.盡量少用或者不用盡量少用或者不用 Update 和和 Delete 語(yǔ)

3、句。語(yǔ)句。 一、一、ORACLE 數(shù)據(jù)加載優(yōu)化數(shù)據(jù)加載優(yōu)化(SQLLoader) 用于外部數(shù)據(jù)加載的表應(yīng)該盡量簡(jiǎn)單，盡量不要?jiǎng)?chuàng)建主鍵、字段的Default值以及其它 約束。這樣可以充分利用Oracle數(shù)據(jù)庫(kù)的直接路徑加載（Direct=y） 、并行加載（PARALLEL =true）提高數(shù)據(jù)加載性能。 示示 例例 用SQL Loader加載數(shù)據(jù)時(shí)使用直接路徑加載（Direct Path Loads）參數(shù) Direct=y（或 者DIRECT=true） ，可以使用下列方法： 方法：方法：sqlldr scott/tiger control=ldr.ctl direct=y 二、二、UPDATE

4、 優(yōu)化優(yōu)化 Oracle 數(shù)據(jù)庫(kù)中對(duì)大表（通常更新的記錄數(shù)在 1 萬(wàn)條以上）進(jìn)行 Update 操作的代價(jià)是 非常高的，但是對(duì)表的 INSERT、SELECT 操作則相對(duì)較快。因此應(yīng)該從數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)、 SQL 語(yǔ)句中盡量不使用對(duì)大表的 Update 操作，針對(duì)不同情況，可以參考以下幾種做法： 1、對(duì)全表的 Update 操作 對(duì)全表的 Update 操作，可以先把數(shù)據(jù) Select 到一個(gè)臨時(shí)表中，再把被 Update 的表用 truncate 刪除，然后把數(shù)據(jù)再?gòu)呐R時(shí)表 Insert 到被 Update 表中。例如： update ea_custacc.inv_acc set inv_ac

5、c=nvl(trim(inv_acc),1); 上述全表的 Update 操作可以改為下面的一系列 SQL 語(yǔ)句替代： -1.將更新后的數(shù)據(jù)Inser到臨時(shí)表 CREATE TABLE inv_acc_tmp AS SELECT nvl(trim(inv_acc),1) inv_acc, mkt_code, inv_name, cert_code, inv_prop, inv_status, open_date, close_date, note FROM ea_custacct.inv_acc; -2. 清空被更新的表 TRUNCATE TABLE ea_custacct.inv_acc;

6、-3. 將數(shù)據(jù)從臨時(shí)表再Insert到被更新的表中 INSERT INTO ea_custacct.inv_acc SELECT * FROM inv_acc_tmp; COMMIT; 2、有條件的 Update 操作 當(dāng)對(duì)大表中的部分?jǐn)?shù)據(jù)，以一定的條件進(jìn)行 Update 時(shí)，可以利用 Oracle 的 ROWID 字 段，用以下方式實(shí)現(xiàn)有條件的 Update： 1、 將滿足 Update 條件的記錄（包含原表中的 ROWID）Insert 到臨時(shí)表； 2、 再根據(jù) ROWID，將未更新的記錄 Inser 到臨時(shí)表； 3、 清空被更新的表； 4、 將數(shù)據(jù)從臨時(shí)表 Insert 到被更新的表；

7、如下，下列 Update 語(yǔ)句： UPDATE EI_SRCDATA.HA_JSMX_TEMP A SET END_FLAG = 1 WHERE A.QSBZ = 080 可以使用下列語(yǔ)句替換： -1. 將滿足Update條件的記錄（包含原表中的ROWID）Insert到臨時(shí)表； CREATE TABLE HA_JSMX_TEMP_TMP AS SELECT ROWID AS ROW_ID,A.* FROM EI_SRCDATA.HA_JSMX_TEMP A WHERE A.QSBZ = 080; COMMIT; -2. 再根據(jù)ROWID，將未更新的記錄Insert到臨時(shí)表 INSERT IN

8、TO HA_JSMX_TEMP_TMP SELECT A.ROWID,A.* FROM EI_SRCDATA.HA_JSMX_TEMP A WHERE NOT EXISTS(SELECT 1 FROM HA_JSMX_TEMP_TMP B WHERE A.ROWID=B.ROW_ID); COMMIT; -3. 清空被更新的表 TRUNCATE TABLE EI_SRCDATA.HA_JSMX_TEMP; -4.將數(shù)據(jù)從臨時(shí)表Insert到被更新的表 INSERT INTO EI_SRCDATA.HA_JSMX_TEMP(表的字段列表) SELECT 表的字段列表 FROM HA_JSMX_T

9、EMP_TMP; COMMIT; 3、多表連接 Update 操作 很多情況下，需要通過(guò)一張表的數(shù)據(jù)來(lái)關(guān)聯(lián)更新另一張表的數(shù)據(jù)，ORACLE 的關(guān)聯(lián)表 更新語(yǔ)法非常復(fù)雜，性能同樣不佳。對(duì)這類 UPDATE 的優(yōu)化思路與有條件的 UPDATE 操 作相同。也是利用 Oracle 的 ROWID 字段： 1、 將滿足 Update 條件的記錄（包含原表中的 ROWID）Insert 到臨時(shí)表； 2、 再根據(jù) ROWID，將未更新的記錄 Inser 到臨時(shí)表； 3、 清空被更新的表； 4、 將數(shù)據(jù)從臨時(shí)表 Insert 到被更新的表； 例如，下面較復(fù)雜的 UPDATE 操作： UPDATE EI_SR

10、CDATA.HA_JSMX_CL A SET (SETTLE_DEPT_CODE, SUB_DEPT_CODE) = (SELECT C.SETTLE_DEPT_CODE, C.SUB_DEPT_CODE FROM EA_PUB.SYS_SEAT_INFO B, EA_PUB.BR_CONTRACT C WHERE A.XWH3 = B.SEAT_CODE AND B.MKT_CODE = C.MKT_CODE AND B.SEAT_CODE = C.SEAT_CODE AND B.IS_SHARE = 0 AND B.MKT_CODE = AC_I_MKT_CODE) WHERE A.SET

11、TLE_DEPT_CODE = AC_UNKNOWN_SETTLE_DEP AND EXISTS (SELECT 1 FROM EA_PUB.SYS_SEAT_INFO B, EA_PUB.BR_CONTRACT C WHERE A.XWH3 = B.SEAT_CODE AND B.MKT_CODE = C.MKT_CODE AND B.SEAT_CODE = C.SEAT_CODE AND B.IS_SHARE = 0 AND B.MKT_CODE = AC_I_MKT_CODE); COMMIT; 可以參照下列做法： -1.將滿足更新條件的數(shù)據(jù)Inser 到臨時(shí)表 INSERT /*+AP

12、PEND*/ INTO TMP_HA_JSMX_CL(ROW_ID, SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,ZQZH,ZQDM1,ZQDM2,ZQLB, LTLX,QYLB,GPNF,MMBZ,SL,CJSL,ZJZH,BZ,JG1,JG2,QSJE,YHS,JSF,GHF,ZGF, SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM,FJSM,MKT_CODE,SEC_TYPE,CL_SQBH,RZ

13、RQBZ,XYZH, DATA_TYPE,SETTLE_ENTITY_ID,FUND_PROP,XWH3, SETTLE_DEPT_CODE,SUB_DEPT_CODE) SELECT A.ROWID AS ROW_ID, SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,ZQZH,ZQDM1,ZQDM2,ZQLB, LTLX,QYLB,GPNF,MMBZ,SL,CJSL,ZJZH,BZ,JG1,JG2,QSJE,YHS,J

14、SF,GHF,ZGF, SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM,FJSM,A.MKT_CODE,A.SEC_TYPE,CL_SQBH,RZRQBZ,XYZH, DATA_TYPE,A.SETTLE_ENTITY_ID,FUND_PROP,XWH3, C.SETTLE_DEPT_CODE, C.SUB_DEPT_CODE FROM EI_SRCDATA.HA_JSMX_CL A, EA_PUB.SYS_SEAT_INFO B, EA_PUB.BR_CONTRACT C WHERE A.XWH1 = B.SEAT_CODE AND B.MKT_CODE = C.MKT_CO

15、DE AND B.SEAT_CODE = C.SEAT_CODE AND B.IS_SHARE = 0 AND B.MKT_CODE = AC_I_MKT_CODE; COMMIT; -2.將不滿足更新條件的數(shù)據(jù)Inser 到臨時(shí)表 INSERT /*+APPEND*/ INTO TMP_HA_JSMX_CL SELECT A.ROWID,A.* FROM EI_SRCDATA.HA_JSMX_CL A WHERE NOT EXISTS(SELECT 1 FROM TMP_HA_JSMX_CL WHERE A.ROWID=TMP_HA_JSMX_CL.ROW_ID); COMMIT; -3.清

16、空被更新的表 EXECUTE IMMEDIATE TRUNCATE TABLE EI_SRCDATA.HA_JSMX_CL; -4.將數(shù)據(jù)從臨時(shí)表再INSERT回來(lái) INSERT /*+APPEND*/INTO EI_SRCDATA.HA_JSMX_CL( SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,ZQZH,ZQDM1,ZQDM2,ZQLB, LTLX,QYLB,GPNF,MMBZ,SL,CJSL,ZJZH,BZ,J

17、G1,JG2,QSJE,YHS,JSF,GHF,ZGF, SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM,FJSM,MKT_CODE,SEC_TYPE,CL_SQBH,RZRQBZ,XYZH, DATA_TYPE,SETTLE_ENTITY_ID,FUND_PROP,XWH3, SETTLE_DEPT_CODE,SUB_DEPT_CODE) SELECT SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,A.

18、ZQZH,ZQDM1,ZQDM2,ZQLB, LTLX,QYLB,GPNF,MMBZ,SL,CJSL,A.ZJZH,BZ,JG1,JG2,QSJE,YHS,JSF,GHF,ZGF, SXF,QTJE1,QTJE2,QTJE3,SJSF,JGDM,FJSM,A.MKT_CODE,A.SEC_TYPE,CL_SQBH,RZRQBZ,XYZH, DATA_TYPE,A.SETTLE_ENTITY_ID,FUND_PROP,A.XWH3, A.SETTLE_DEPT_CODE,A.SUB_DEPT_CODE FROM TMP_HA_JSMX_CL A; COMMIT; 4、兩張表關(guān)聯(lián) Update 的

19、三種方式 適合于以下情況： A 有兩張表，主表 A 有 100 萬(wàn)數(shù)據(jù)，被關(guān)聯(lián)表 B 有 50 萬(wàn)數(shù)據(jù)，要根據(jù) B 表的內(nèi)容更 新 A 表的內(nèi)容。更新方式有以下三種方式，請(qǐng)根據(jù)實(shí)際情況選擇不同的寫法： 方法 1： Update (select /*+ BYPASS_UJVC */ a.KHXM As A1,a.KHLX As A2,a.ZHLX As A3,a.ZJXZ As A4,a.CGFS As A5,a.YHDM As A6,a.YHWD As A7,a.ZHZT As A8 ,a.KHRQ As A9,a.CGRQ As A10,a.BZSM As A11 ,trim(b.KHXM)

20、 As B1,trim(b.KHLX) As B2,trim(b.ZHLX) As B3,trim(b.ZJXZ) As B4,trim(b.CGFS) As B5,trim(b.YHDM) As B6,trim(b.YHWD) As B7,trim(b.ZHZT) As B8 ,trim(b.KHRQ) As B9,trim(b.CGRQ) As B10,trim(b.BZSM) As B11 From CUST_DATA_INFO_OLD a,CUST_DATA_INFO_TMP b Where a.ZJZH=trim(b.ZJZH) And a.HBZL=trim(b.HBZL) And

21、 a.YYBDM=trim(b.YYBDM) Set A1=B1,A2=B2,A3=B3,A4=B4,A5=B5,A6=B6,A7=B7,A8=B8,A9=B9,A10=B10,A11=B11; 方法 2： update CUST_DATA_INFO_OLD A set (KHXM, KHLX, ZHLX, ZJXZ, CGFS, YHDM, YHWD,ZHZT, KHRQ, CGRQ, BZSM ) = (select TRIM(B.KHXM), TRIM(B.KHLX), TRIM(B.ZHLX), TRIM(B.ZJXZ), TRIM(B.CGFS), TRIM(B.YHDM), TRI

22、M(B.YHWD),TRIM(B.ZHZT), TRIM(B.KHRQ), TRIM(B.CGRQ), TRIM(B.BZSM) FROM CUST_DATA_INFO_TMP B WHERE a.ZJZH=trim(b.ZJZH) And a.HBZL=trim(b.HBZL) And a.YYBDM=trim(b.YYBDM) AND ROWNUM abc or col1abc;-“”的SQL語(yǔ)句應(yīng)該改為這種寫 法，使用”，將無(wú)法使用索引。 COMMIT; -2.清空數(shù)據(jù)表 TRUNCATE TABLE tab; -3.將數(shù)據(jù)從臨時(shí)表INSERT回來(lái) INSERT INTO ttab SE

23、LECT * FROM tab_tmp COMMIT; 四、四、DBA 級(jí)別優(yōu)化提示級(jí)別優(yōu)化提示 1. 合理的表空間分布 盡量把不同的表空間分別放在不同的磁盤上。如果條件不允許的話建議把 Oracle 的系 統(tǒng)表空間和應(yīng)用程序的用戶表空間分別放在不同的物理磁盤上。 2合理的表空間大小 在創(chuàng)建表空間時(shí)預(yù)先計(jì)算好表空間的大小。 表空間的計(jì)算公式： 表空間大小 = 表 1 的大小 + 表 2 的大小 +表 n 的大小 每個(gè)表大小 = 最大行的大小 * 預(yù)測(cè)記錄數(shù) * 120% 3. 合理配置 Oracle 內(nèi)存參數(shù) 內(nèi)存建議設(shè)置到整個(gè)系統(tǒng)的 60%比較合適。 4.合理調(diào)整 RedoLog 文件大小,

24、建議將每個(gè) RedoLog 大小都調(diào)到 512M,如果條件允許把它 和系統(tǒng)表空間分別放在不同的磁盤中 Redo01.log: Redo02.log: Redo03.log: 5. 定期對(duì)表進(jìn)行分析 使用 analyze table 表名 compute statistics ; 對(duì)表進(jìn)行分析 Analyze table ei_srcdata.ha_hsmx_cl compute statistics; 對(duì)表進(jìn)行全表分析-分析速度較慢 對(duì)表進(jìn)行 20%抽樣分析，分析速度較快 analyze table ei_srcdata.ha_jsmx_cl estimate statistics sampl

25、e 20 percent; 五、其它優(yōu)化提示五、其它優(yōu)化提示 1. 對(duì)大表的操作以及多 CPU 情況下可以試試用并行提示(/*+ PARALLEL(表名,進(jìn)程數(shù)量) */) 例子: 對(duì)表 ei_srcdata.ha_jsmx_cl 分 2 段進(jìn)行并行統(tǒng)計(jì). select /*+ PARALLEL(jsmx,2) */ count(*) from ei_srcdata.ha_jsmx_cl jsmx; 2. 對(duì)大量數(shù)據(jù)進(jìn)行 Insert 時(shí)請(qǐng)?jiān)囋囂崾? /*+ APPEND */ 例子: Insert /*+ APPEND */ into ei_srcdata.ha_jsmx_cl Select * from ei_srcdata.ha_jsmx; IN 可以理解為: for x in ( select * from rollup ) loop if ( not exists ( that query ) ) then OUTPUT end if; end; 注意:NOT EXISTS 與 NOT IN 不能完全互相替換，看具體的需求。如果選擇的列可以為 空，則不能被替換。 例如下面語(yǔ)句，看他