Oracle數(shù)據(jù)庫編程調(diào)優(yōu)手冊

1、Oracle數(shù)據(jù)庫編程調(diào)優(yōu)手冊 目錄前 言4一、ORACLE數(shù)據(jù)加載優(yōu)化(SQLLoader)4二、UPDATE優(yōu)化41、對全表的Update操作42、有條件的Update操作53、多表連接Update操作54、兩張表關(guān)聯(lián)Update 的三種方式65、用視圖代替對表的Update操作6三、DELETE優(yōu)化61、通過分拆表，避免DELETE操作62、通過中間表，用INSERT代替DELETE操作6四、DBA級別優(yōu)化提示6五、其它優(yōu)化提示6前 言本文根據(jù)最近一段時間，技術(shù)部與6.0項目組共同在做系統(tǒng)優(yōu)化過程中，最常用的做法做一個總結(jié)。目前只收錄最近優(yōu)化時的一些做法，供各項目組參考。各個項

2、目組在實際開發(fā)過程中，如有其它優(yōu)化SQL的技巧，也請?zhí)峤患夹g(shù)部，形成技術(shù)文檔，供各項目組參考。性能優(yōu)化的幾個原則：1. 簡化業(yè)務流程是提高性能的最可靠的方法-它可以給你的性能帶來成倍的提升。2. 不要把希望寄托在DBA得身上，它不可能給你的性能帶來成倍的提升。3. 盡量少用或者不用Update和Delete語句。一、ORACLE數(shù)據(jù)加載優(yōu)化(SQLLoader)用于外部數(shù)據(jù)加載的表應該盡量簡單，盡量不要創(chuàng)建主鍵、字段的Default值以及其它約束。這樣可以充分利用Oracle數(shù)據(jù)庫的直接路徑加載（Direct=y）、并行加載（PARALLEL=true）提高數(shù)據(jù)加載性能。示 例用SQL Loa

3、der加載數(shù)據(jù)時使用直接路徑加載（Direct Path Loads）參數(shù) Direct=y（或者DIRECT=true），可以使用下列方法：方法：sqlldr scott/tiger control=ldr.ctl direct=y二、UPDATE優(yōu)化Oracle數(shù)據(jù)庫中對大表（通常更新的記錄數(shù)在1萬條以上）進行Update操作的代價是非常高的，但是對表的INSERT、SELECT操作則相對較快。因此應該從數(shù)據(jù)庫結(jié)構(gòu)設(shè)計、SQL語句中盡量不使用對大表的Update操作，針對不同情況，可以參考以下幾種做法：1、對全表的Update操作對全表的Update操作，可以先把數(shù)據(jù)Select到一個臨時

4、表中，再把被Update的表用truncate刪除，然后把數(shù)據(jù)再從臨時表Insert到被Update表中。例如：update ea_custacc.inv_acc set inv_acc=nvl(trim(inv_acc),'1');上述全表的Update操作可以改為下面的一系列SQL語句替代：-1.將更新后的數(shù)據(jù)Inser到臨時表CREATE TABLE inv_acc_tmp AS SELECT nvl(trim(inv_acc),'1') inv_acc, mkt_code, inv_name, cert_code, inv_prop, inv_statu

5、s, open_date, close_date, note FROM ea_custacct.inv_acc;-2. 清空被更新的表TRUNCATE TABLE ea_custacct.inv_acc;-3. 將數(shù)據(jù)從臨時表再Insert到被更新的表中INSERT INTO ea_custacct.inv_acc SELECT * FROM inv_acc_tmp;COMMIT;2、有條件的Update操作當對大表中的部分數(shù)據(jù)，以一定的條件進行Update時，可以利用Oracle的ROWID字段，用以下方式實現(xiàn)有條件的Update：1、 將滿足Update條件的記錄（包含原表中的ROWID）

6、Insert到臨時表；2、 再根據(jù)ROWID，將未更新的記錄Inser到臨時表；3、 清空被更新的表；4、 將數(shù)據(jù)從臨時表Insert到被更新的表；如下，下列Update語句：UPDATE EI_SRCDATA.HA_JSMX_TEMP A SET END_FLAG = '1' WHERE A.QSBZ = '080'可以使用下列語句替換： -1. 將滿足Update條件的記錄（包含原表中的ROWID）Insert到臨時表； CREATE TABLE HA_JSMX_TEMP_TMP AS SELECT ROWID AS ROW_ID,A.* FROM EI_S

7、RCDATA.HA_JSMX_TEMP A WHERE A.QSBZ = '080' COMMIT; -2. 再根據(jù)ROWID，將未更新的記錄Insert到臨時表 INSERT INTO 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_TE

8、MP; -4.將數(shù)據(jù)從臨時表Insert到被更新的表INSERT INTO EI_SRCDATA.HA_JSMX_TEMP(表的字段列表) SELECT 表的字段列表 FROM HA_JSMX_TEMP_TMP; COMMIT;3、多表連接Update操作很多情況下，需要通過一張表的數(shù)據(jù)來關(guān)聯(lián)更新另一張表的數(shù)據(jù)，ORACLE的關(guān)聯(lián)表更新語法非常復雜，性能同樣不佳。對這類UPDATE的優(yōu)化思路與有條件的UPDATE操作相同。也是利用Oracle的ROWID字段：1、 將滿足Update條件的記錄（包含原表中的ROWID）Insert到臨時表；2、 再根據(jù)ROWID，將未更新的記錄Inser到臨時

9、表；3、 清空被更新的表；4、 將數(shù)據(jù)從臨時表Insert到被更新的表；例如，下面較復雜的UPDATE操作： UPDATE EI_SRCDATA.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_

10、CODE AND B.IS_SHARE = '0' AND B.MKT_CODE = AC_I_MKT_CODE) WHERE A.SETTLE_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'

11、AND B.MKT_CODE = AC_I_MKT_CODE); COMMIT;可以參照下列做法：-1.將滿足更新條件的數(shù)據(jù)Inser 到臨時表INSERT /*+APPEND*/ 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,QSJ

12、E,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 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,JSH

13、Y,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,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_SE

14、AT_INFO B, EA_PUB.BR_CONTRACT C WHERE A.XWH1 = 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;-2.將不滿足更新條件的數(shù)據(jù)Inser 到臨時表INSERT /*+APPEND*/ INTO TMP_HA_JSMX_CL SELECT A.ROWID,A.* FROM EI_SRCDATA.HA_JSMX_CL A WHERE

15、 NOT EXISTS(SELECT 1 FROM TMP_HA_JSMX_CL WHERE A.ROWID=TMP_HA_JSMX_CL.ROW_ID); COMMIT;-3.清空被更新的表EXECUTE IMMEDIATE 'TRUNCATE TABLE EI_SRCDATA.HA_JSMX_CL' -4.將數(shù)據(jù)從臨時表再INSERT回來INSERT /*+APPEND*/INTO EI_SRCDATA.HA_JSMX_CL( SCDM,JLLX,JYFS,JSFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTR

16、Q,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,RZRQBZ,XYZH, DATA_TYPE,SETTLE_ENTITY_ID,FUND_PROP,XWH3, SETTLE_DEPT_CODE,SUB_DEPT_CODE) SELECT SCDM,JLLX,JYFS,J

17、SFS,YWLX,QSBZ,GHLX,JSBH,CJBH,SQBH,WTBH,JYRQ,QSRQ, JSRQ,QTRQ,WTSJ,CJSJ,XWH1,XWH2,XWHY,JSHY,TGHY,A.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,FUN

18、D_PROP,A.XWH3, A.SETTLE_DEPT_CODE,A.SUB_DEPT_CODE FROM TMP_HA_JSMX_CL A; COMMIT;4、兩張表關(guān)聯(lián)Update 的三種方式適合于以下情況：A 有兩張表，主表A有100萬數(shù)據(jù)，被關(guān)聯(lián)表B有50萬數(shù)據(jù)，要根據(jù)B表的內(nèi)容更新A表的內(nèi)容。更新方式有以下三種方式，請根據(jù)實際情況選擇不同的寫法：方法1：        Update (select /*+ BYPASS_UJVC */ a.KHXM As A1,a.KHLX As A2,a.ZHLX A

19、s 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

20、A11                                 ,trim(b.KHXM) As B1,trim(b.KHLX) As B2,trim(b.ZHLX) As B3,trim(b.ZJXZ) As B4,trim(b.CGFS) As B5,trim(b.Y

21、HDM) 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

22、                    From CUST_DATA_INFO_OLD a,CUST_DATA_INFO_TMP b                        

23、 Where a.ZJZH=trim(b.ZJZH) And a.HBZL=trim(b.HBZL)                           And a.YYBDM=trim(b.YYBDM)            Set A1

24、=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 ) =                   

25、;       (select TRIM(B.KHXM), TRIM(B.KHLX), TRIM(B.ZHLX), TRIM(B.ZJXZ), TRIM(B.CGFS), TRIM(B.YHDM), TRIM(B.YHWD),TRIM(B.ZHZT), TRIM(B.KHRQ), TRIM(B.CGRQ), TRIM(B.BZSM)                  

26、         FROM CUST_DATA_INFO_TMP B                          WHERE a.ZJZH=trim(b.ZJZH) And a.HBZL=trim(b.HBZL)     &

27、#160;                     And a.YYBDM=trim(b.YYBDM) AND ROWNUM <2)WHERE exists(select 1 FROM CUST_DATA_INFO_TMP B              

28、            WHERE a.ZJZH=trim(b.ZJZH) And a.HBZL=trim(b.HBZL)                           And a.YYBDM=trim(b.YYBDM) ) 

29、0; 方法3：-1.把需要更新的數(shù)據(jù)寫入臨時表中CREATE table TMP_TAB AS  SELECT A.ROWID AS ROW_ID,   trim(b.KHXM) 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

30、,    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)   &

31、#160;AND a.HBZL=trim(b.HBZL)    AND a.YYBDM=trim(b.YYBDM);COMMIT; -2.用ROWID關(guān)聯(lián)更新數(shù)據(jù)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

32、 A11,        B.B1,B.B2,B.B3,B.B4,B.B5,B.B6,B.B7,B.B8        ,B.B9,B.B10,B.B11 FROM CUST_DATA_INFO_OLD a,     TMP_TAB b  WHERE a.ROWID=b.ROW_ID)SET A1=B1,A2=B2,A3=B3,A4=B4,A5=B5,A6=B6,A7=B7,A8=B8,A9=B9

33、,A10=B10,A11=B11; COMMIT;   5、用視圖代替對表的Update操作當表中的數(shù)據(jù)需要更新后，作為后繼操作的條件時，可以避免對表的UPDATE操作，改成視圖。例如，原來有這么一段清分過程，需要先更新股東信庫： -更新股東信息庫的結(jié)算機構(gòu)代碼 UPDATE EI_SRCDATA.XY_GDXX A SET A.SETTLE_DEPT_CODE = NVL(TRIM(A.XTJGDM), NVL(SELECT SETTLE_DEPT_CODE FROM EA_PUB.SYS_CUST_DEPT B WHERE TRIM(A.GTJGDM) = T

34、RIM(B.CUST_DEPT_CODE), AC_UNKNOWN_SETTLE_DEP), A.ZHZT = NVL(TRIM(A.ZHZT), '0'), A.ZHXZ = NVL(TRIM(A.ZHXZ), '1') WHERE PKG_PUB_UTILS.PARSEINTCODE(A.JYSC) = AC_I_MKT_CODE; COMMIT;可以用視圖代替上述UPDATE：-用視圖代替對表的UPDATE操作CREATE OR REPLACE VIEW VW_XY_GDXX ASSELECT PKG_PUB_UTILS.PARSEINTCODE(A.JY

35、SC) AS MKT_CODE, GDDM AS INV_ACC, NVL(A.GDXM, A.GDDM) AS INV_NAME, SFZH AS CERT_CODE, ZDXW AS SEAT_CODE, FZDM AS SUB_DEPT_CODE, TRIM(A.ZJZH) AS TRAN_ACCOUNT, NVL(TRIM(A.ZHXZ), '1') AS ZHXZ, NVL(TRIM(A.ZHZT), '0') AS ZHZT, NVL(TRIM(A.BY3), '00001') AS BY3, NVL(NVL(TRIM(A.XTJGD

36、M), B.SETTLE_DEPT_CODE), F_GET_UNKNOWN_SETTLE_DEPT) AS SETTLE_DEPT_CODE, DECODE(TRIM(A.ZHZT), '0', DECODE(A.ZHXZ, '2', '1', '0'), '3') AS INV_PROP, DECODE(A.JYSC, '5', '01', '6', '02') AS MKT_GROUP FROM EI_SRCDATA.XY_GDXX A, EA_

37、PUB.SYS_CUST_DEPT B WHERE TRIM(A.GTJGDM) = B.CUST_DEPT_CODE(+);三、DELETE優(yōu)化Oracle數(shù)據(jù)庫中對大表進行DELETE操作與UPDATE一樣，同樣很慢。對一張表的刪除操作最快的方式是使用TRUNCATE。但是TRUNCATE操作只能對整張表清空，無法按條件過慮刪除。因此，為減少由于DELETE操作性能低下，可以從以下兩方面入手，避免對大表的DELETE操作：1、通過分拆表，避免DELETE操作在做表結(jié)構(gòu)設(shè)計時，通過將數(shù)據(jù)表進行分拆（分成多個表或使用分區(qū)表），在重復處理或刪除操作時，可以直接對表中的數(shù)據(jù)進行TRUNCATE，而

38、不需要按條件刪除。例如，E-SIM6.0原來股份清算憑證和手工調(diào)賬憑證都是合并在一張表，在做重復清算時，需要把所有清算憑證刪除，而保留手工調(diào)賬憑證。這樣在做重復清算時，就需要執(zhí)行DELETE操作來刪除所有的清算憑證，而不能使用TRUNCATE。這種情況下，就可以考慮分成兩張表，一張存放清算憑證，另一張存放手工記賬憑證，這樣重復處理時就要以直接對清算憑證表進行TRUNCATE操作，大大提高效率。2、通過中間表，用INSERT代替DELETE操作如果數(shù)據(jù)無法拆分表，必須進行DELETE操作，可以通過中間表，用INSERT、TRUNCATE代替UPDATE操作，這種方式與UPDATE的優(yōu)化思路類似，

39、通常執(zhí)行以下操作：1、不需要DELETE的數(shù)據(jù)INSERT到臨時表中。2、TRUNCATE需要刪除數(shù)據(jù)的表。3、將數(shù)據(jù)從臨時表INSERT回被刪除數(shù)據(jù)的表例如，下面的DELETE操作：DELETE tab WHERE col1=abc可以使用下列SQL代替：-1.將不需要刪除的記錄INSERT到臨時表INSERT INTO tab_tmpSELECT * FROM tab WHERE col1>abc or col1<abc;-“<>”的SQL語句應該改為這種寫法，使用”<>”，將無法使用索引。COMMIT;-2.清空數(shù)據(jù)表TRUNCATE TABLE ta

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

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

42、ent;五、其它優(yōu)化提示1. 對大表的操作以及多CPU情況下可以試試用并行提示(/*+ PARALLEL(表名,進程數(shù)量) */)例子: 對表ei_srcdata.ha_jsmx_cl 分2段進行并行統(tǒng)計.select /*+ PARALLEL(jsmx,2) */ count(*) from ei_srcdata.ha_jsmx_cl jsmx;2. 對大量數(shù)據(jù)進行Insert時請試試提示: /*+ APPEND */例子:Insert /*+ APPEND */ into ei_srcdata.ha_jsmx_cl Select * from ei_srcdata.ha_jsmx;IN&a

43、mp;EXISTS與NOT IN&NOT EXISTS 的優(yōu)化原則1.EXISTS的執(zhí)行流程        select * from t1 where exists ( select null from t2 where y = x )可以理解為:   for x in ( select * from t1 )   loop      if ( exists ( select null from t2 where y = x.x )   

44、0;  then          OUTPUT THE RECORD      end if   end loop對于in 和 exists的性能區(qū)別:   如果子查詢得出的結(jié)果集記錄較少，主查詢中的表較大且又有索引時應該用in,反之如果外層的主查詢記錄較少，子查詢中的表大，又有索引時使用exists。   其實我們區(qū)分in和exists主要是造成了驅(qū)動順序的改變（這是性能變化的關(guān)鍵），如果是exists，那么以外層表為驅(qū)動

45、表，先被訪問，如果是IN，那么先執(zhí)行子查詢，所以我們會以驅(qū)動表的快速返回為目標，那么就會考慮到索引及結(jié)果集的關(guān)系了                            另外IN時不對NULL進行處理如：select 1 from dual where null  in (0,1,2,null)為空2.NOT IN 與NOT EXISTS:        N

46、OT EXISTS的執(zhí)行流程select .  from rollup Rwhere not exists ( select 'Found' from title T                              where R.source_id = T.Title_ID);可以理解為:for x in ( select * from rollup

47、 )       loop          if ( not exists ( that query ) ) then                 OUTPUT          end if;       end;注意:NOT EXISTS 與 NOT IN 不能完全互相

48、替換，看具體的需求。如果選擇的列可以為空，則不能被替換。例如下面語句，看他們的區(qū)別：select x,y from t;x              y-         -1              33        11        21 

49、       13        15select * from t where  x not in (select y from t t2  )no rows        select * from t where  not exists (select null from t t2