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

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

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

3、句。語句。 一、一、ORACLE 數(shù)據(jù)加載優(yōu)化數(shù)據(jù)加載優(yōu)化(SQLLoader) 用于外部數(shù)據(jù)加載的表應(yīng)該盡量簡單，盡量不要創(chuàng)建主鍵、字段的Default值以及其它 約束。這樣可以充分利用Oracle數(shù)據(jù)庫的直接路徑加載（Direct=y） 、并行加載（PARALLEL =true）提高數(shù)據(jù)加載性能。 示示 例例 用SQL Loader加載數(shù)據(jù)時使用直接路徑加載（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ù)庫中對大表（通常更新的記錄數(shù)在 1 萬條以上）進行 Update 操作的代價是 非常高的，但是對表的 INSERT、SELECT 操作則相對較快。因此應(yīng)該從數(shù)據(jù)庫結(jié)構(gòu)設(shè)計、 SQL 語句中盡量不使用對大表的 Update 操作，針對不同情況，可以參考以下幾種做法： 1、對全表的 Update 操作 對全表的 Update 操作，可以先把數(shù)據(jù) Select 到一個臨時表中，再把被 Update 的表用 truncate 刪除，然后把數(shù)據(jù)再從臨時表 Insert 到被 Update 表中。例如： update ea_custacc.inv_acc set inv_ac

5、c=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_status, open_date, close_date, note FROM ea_custacct.inv_acc; -2. 清空被更新的表 TRUNCATE TABLE ea_custacct.inv_acc;

6、-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）Insert 到臨時表； 2、 再根據(jù) ROWID，將未更新的記錄 Inser 到臨時表； 3、 清空被更新的表； 4、 將數(shù)據(jù)從臨時表 Insert 到被更新的表；

7、如下，下列 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_SRCDATA.HA_JSMX_TEMP A WHERE A.QSBZ = 080; COMMIT; -2. 再根據(jù)ROWID，將未更新的記錄Insert到臨時表 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ù)從臨時表Insert到被更新的表 INSERT INTO EI_SRCDATA.HA_JSMX_TEMP(表的字段列表) SELECT 表的字段列表 FROM HA_JSMX_T

9、EMP_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 到臨時表； 3、 清空被更新的表； 4、 將數(shù)據(jù)從臨時表 Insert 到被更新的表； 例如，下面較復雜的 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 到臨時表 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 到臨時表 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ù)從臨時表再INSERT回來 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 萬數(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 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īng)該改為這種寫 法，使用”，將無法使用索引。 COMMIT; -2.清空數(shù)據(jù)表 TRUNCATE TABLE tab; -3.將數(shù)據(jù)從臨時表INSERT回來 INSERT INTO ttab SE

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

24、建議將每個 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 sampl

25、e 20 percent; 五、其它優(yōu)化提示五、其它優(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 可以理解為: for x in ( select * from rollup ) loop if ( not exists ( that query ) ) then OUTPUT end if; end; 注意:NOT EXISTS 與 NOT IN 不能完全互相替換，看具體的需求。如果選擇的列可以為 空，則不能被替換。 例如下面語句，看他