Oracle数据库编程调优手册

1、Oracle 数据库编程调优手册数据库编程调优手册 目录目录 前 言.4 一、ORACLE 数据加载优化(SQLLoader).4 二、UPDATE 优化.4 1、对全表的 Update 操作.4 2、有条件的 Update 操作.5 3、多表连接 Update 操作.5 4、两张表关联 Update 的三种方式.6 5、用视图代替对表的 Update 操作.6 三、DELETE 优化 .6 1、通过分拆表，避免 DELETE 操作 .6 2、通过中间表，用 INSERT 代替 DELETE 操作 .6 四、DBA 级别优化提示.6 五、其它优化提示.6 前前 言言 本文根据最近一段时间，技术

2、部与 6.0 项目组共同在做系统优化过程中，最常用的做 法做一个总结。 目前只收录最近优化时的一些做法，供各项目组参考。各个项目组在实际开发过程中， 如有其它优化 SQL 的技巧，也请提交技术部，形成技术文档，供各项目组参考。 性能优化的几个原则： 1.简化业务流程是提高性能的最可靠的方法简化业务流程是提高性能的最可靠的方法-它可以给你的性能带来成倍的提升。它可以给你的性能带来成倍的提升。 2.不要把希望寄托在不要把希望寄托在 DBA 得身上，它不可能给你的性能带来成倍的提升。得身上，它不可能给你的性能带来成倍的提升。 3.尽量少用或者不用尽量少用或者不用 Update 和和 Delete 语

3、句。语句。 一、一、ORACLE 数据加载优化数据加载优化(SQLLoader) 用于外部数据加载的表应该尽量简单，尽量不要创建主键、字段的Default值以及其它 约束。这样可以充分利用Oracle数据库的直接路径加载（Direct=y） 、并行加载（PARALLEL =true）提高数据加载性能。 示示 例例 用SQL Loader加载数据时使用直接路径加载（Direct Path Loads）参数 Direct=y（或 者DIRECT=true） ，可以使用下列方法： 方法：方法：sqlldr scott/tiger control=ldr.ctl direct=y 二、二、UPDATE

4、 优化优化 Oracle 数据库中对大表（通常更新的记录数在 1 万条以上）进行 Update 操作的代价是 非常高的，但是对表的 INSERT、SELECT 操作则相对较快。因此应该从数据库结构设计、 SQL 语句中尽量不使用对大表的 Update 操作，针对不同情况，可以参考以下几种做法： 1、对全表的 Update 操作 对全表的 Update 操作，可以先把数据 Select 到一个临时表中，再把被 Update 的表用 truncate 删除，然后把数据再从临时表 Insert 到被 Update 表中。例如： update ea_custacc.inv_acc set inv_ac

5、c=nvl(trim(inv_acc),1); 上述全表的 Update 操作可以改为下面的一系列 SQL 语句替代： -1.将更新后的数据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. 将数据从临时表再Insert到被更新的表中 INSERT INTO ea_custacct.inv_acc SELECT * FROM inv_acc_tmp; COMMIT; 2、有条件的 Update 操作 当对大表中的部分数据，以一定的条件进行 Update 时，可以利用 Oracle 的 ROWID 字 段，用以下方式实现有条件的 Update： 1、 将满足 Update 条件的记录（包含原表中的 ROWID）Insert 到临时表； 2、 再根据 ROWID，将未更新的记录 Inser 到临时表； 3、 清空被更新的表； 4、 将数据从临时表 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. 再根据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.将数据从临时表Insert到被更新的表 INSERT INTO EI_SRCDATA.HA_JSMX_TEMP(表的字段列表) SELECT 表的字段列表 FROM HA_JSMX_T

9、EMP_TMP; COMMIT; 3、多表连接 Update 操作 很多情况下，需要通过一张表的数据来关联更新另一张表的数据，ORACLE 的关联表 更新语法非常复杂，性能同样不佳。对这类 UPDATE 的优化思路与有条件的 UPDATE 操 作相同。也是利用 Oracle 的 ROWID 字段： 1、 将满足 Update 条件的记录（包含原表中的 ROWID）Insert 到临时表； 2、 再根据 ROWID，将未更新的记录 Inser 到临时表； 3、 清空被更新的表； 4、 将数据从临时表 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.将满足更新条件的数据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.将不满足更新条件的数据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.将数据从临时表再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、两张表关联 Update 的

19、三种方式 适合于以下情况： A 有两张表，主表 A 有 100 万数据，被关联表 B 有 50 万数据，要根据 B 表的内容更 新 A 表的内容。更新方式有以下三种方式，请根据实际情况选择不同的写法： 方法 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语句应该改为这种写 法，使用”，将无法使用索引。 COMMIT; -2.清空数据表 TRUNCATE TABLE tab; -3.将数据从临时表INSERT回来 INSERT INTO ttab SE

23、LECT * FROM tab_tmp COMMIT; 四、四、DBA 级别优化提示级别优化提示 1. 合理的表空间分布 尽量把不同的表空间分别放在不同的磁盘上。如果条件不允许的话建议把 Oracle 的系 统表空间和应用程序的用户表空间分别放在不同的物理磁盘上。 2合理的表空间大小 在创建表空间时预先计算好表空间的大小。 表空间的计算公式： 表空间大小 = 表 1 的大小 + 表 2 的大小 +表 n 的大小 每个表大小 = 最大行的大小 * 预测记录数 * 120% 3. 合理配置 Oracle 内存参数 内存建议设置到整个系统的 60%比较合适。 4.合理调整 RedoLog 文件大小,

24、建议将每个 RedoLog 大小都调到 512M,如果条件允许把它 和系统表空间分别放在不同的磁盘中 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; 五、其它优化提示五、其它优化提示 1. 对大表的操作以及多 CPU 情况下可以试试用并行提示(/*+ PARALLEL(表名,进程数量) */) 例子: 对表 ei_srcdata.ha_jsmx_cl 分 2 段进行并行统计. select /*+ PARALLEL(jsmx,2) */ count(*) from ei_srcdata.ha_jsmx_cl jsmx; 2. 对大量数据进行 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 不能完全互相替换，看具体的需求。如果选择的列可以为 空，则不能被替换。 例如下面语句，看他