Oracle数据库编程调优手册.doc

ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 1 页 共 15 页 Oracle 数据库编程调优手册数据库编程调优手册 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 2 页 共 15 页 目录目录 前 言 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 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 3 页 共 15 页 前前 言言 本文根据最近一段时间 技术部与 6 0 项目组共同在做系统优化过程中 最常用的做 法做一个总结 目前只收录最近优化时的一些做法 供各项目组参考 各个项目组在实际开发过程中 如有其它优化 SQL 的技巧 也请提交技术部 形成技术文档 供各项目组参考 性能优化的几个原则 1 简化业务流程是提高性能的最可靠的方法简化业务流程是提高性能的最可靠的方法 它可以给你的性能带来成倍的提升 它可以给你的性能带来成倍的提升 2 不要把希望寄托在不要把希望寄托在 DBA 得身上 它不可能给你的性能带来成倍的提升 得身上 它不可能给你的性能带来成倍的提升 3 尽量少用或者不用尽量少用或者不用 Update 和和 Delete 语句 语句 一 一 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 优化优化 Oracle 数据库中对大表 通常更新的记录数在 1 万条以上 进行 Update 操作的代价是 非常高的 但是对表的 INSERT SELECT 操作则相对较快 因此应该从数据库结构设计 SQL 语句中尽量不使用对大表的 Update 操作 针对不同情况 可以参考以下几种做法 1 对全表的 Update 操作 对全表的 Update 操作 可以先把数据 Select 到一个临时表中 再把被 Update 的表用 truncate 删除 然后把数据再从临时表 Insert 到被 Update 表中 例如 update ea custacc inv acc set inv acc 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 清空被更新的表 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 4 页 共 15 页 TRUNCATE TABLE ea custacct inv acc 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 到被更新的表 如下 下列 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 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 TEMP 4 将数据从临时表Insert到被更新的表 INSERT INTO EI SRCDATA HA JSMX TEMP 表的字段列表 SELECT 表的字段列表 FROM HA JSMX TEMP TMP COMMIT 3 多表连接 Update 操作 很多情况下 需要通过一张表的数据来关联更新另一张表的数据 ORACLE 的关联表 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 5 页 共 15 页 更新语法非常复杂 性能同样不佳 对这类 UPDATE 的优化思路与有条件的 UPDATE 操 作相同 也是利用 Oracle 的 ROWID 字段 1 将满足 Update 条件的记录 包含原表中的 ROWID Insert 到临时表 2 再根据 ROWID 将未更新的记录 Inser 到临时表 3 清空被更新的表 4 将数据从临时表 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 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 AND B MKT CODE AC I MKT CODE COMMIT 可以参照下列做法 1 将满足更新条件的数据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 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 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 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 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 6 页 共 15 页 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 CODE 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 清空被更新的表 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 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 JSFS 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 FUND PROP A XWH3 A SETTLE DEPT CODE A SUB DEPT CODE FROM TMP HA JSMX CL A COMMIT 4 两张表关联 Update 的三种方式 适合于以下情况 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 7 页 共 15 页 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 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 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 TRIM 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 col1 abc 的SQL语句应该改为这种写 法 使用 将无法使用索引 COMMIT 2 清空数据表 TRUNCATE TABLE tab 3 将数据从临时表INSERT回来 INSERT INTO ttab SELECT FROM tab tmp COMMIT 四 四 DBA 级别优化提示级别优化提示 1 合理的表空间分布 尽量把不同的表空间分别放在不同的磁盘上 如果条件不允许的话建议把 Oracle 的系 统表空间和应用程序的用户表空间分别放在不同的物理磁盘上 2 合理的表空间大小 在创建表空间时预先计算好表空间的大小 表空间的计算公式 表空间大小 表 1 的大小 表 2 的大小 表 n 的大小 每个表大小 最大行的大小 预测记录数 120 3 合理配置 Oracle 内存参数 内存建议设置到整个系统的 60 比较合适 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 11 页 共 15 页 4 合理调整 RedoLog 文件大小 建议将每个 RedoLog 大小都调到 512M 如果条件允许把它 和系统表空间分别放在不同的磁盘中 Redo01 log ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 12 页 共 15 页 Redo02 log Redo03 log 5 定期对表进行分析 使用 analyze table 表名 compute statistics 对表进行分析 ORACLE 数据库编程优化手册 V1 0 新意科技 版权所有 第 13 页 共 15 页 Analyze table ei srcdata ha hsmx cl compute statistics 对表进行全表分析 分析速度较慢 对表进行 20 抽样分析 分析速度较快 analyze table ei srcdata ha jsmx cl estimate statistics sample 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 不能完全互相替换 看具体的需求 如果选择的列