ORACLE SQL性能优化规范.doc

ORACLE SQL性能优化规范 目 录 1. 说明： 4 2. 高效SQL的特点： 4 3. 选用适合的ORACLE优化器 4 4. 对表或索引进行统计 5 5. 选择合适的多表关联方式： 6 1. HASH JOIN 6 2. NESTED LOOP JOIN 7 3. SORT MERGE JOIN 7 6. 访问Table的方式 8 7. HINT 8 8. 避免WHERE条件与索引字段类型不一致 8 9. 共享SQL语句 9 10. 选择最有效率的表名顺序(只在基于规则的优化器中有效) 10 11. 使用DECODE函数来减少处理时间 11 12. 删除重复记录 11 13. 用TRUNCATE替代DELETE 12 14. 尽量多使用COMMIT(针对OLTP性质系统) 12 15. 减少SQL语句中的表的关联数。 13 16. EXISTS与IN的区别 14 17. 避免在索引列上使用IS NULL和IS NOT NULL 15 18. 总是使用索引的第一个列 15 19. 用UNION-ALL 替换UNION ( 不等价) 16  说明： 本文档的目的是面向应用开发人员，为应用开发人员编写SQL提供帮助。因此，其调整定位于SQL级。对于整个性能调优而言，SQL级或应用级的调优是最经济最高效的。 相对于SQL级的调整，数据库或系统级的调整对性能的影响也十份重要。如内存参数的调整、REDO日志的大小对检查点的影响等等。该部分内容不在此表述。 本文档的前部分主要讲述优化SQL原则性、原理性内容，从第8节开始，主要描述常见的技巧。因此，前部分需引起足够的重视。 高效SQL的特点： “幸福的家庭都是相似的,不幸的家庭各有不同 RULE (基于规则) COST (基于成本) CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件或spfile中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 当然可在SQL语句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 必须经常运行analyze 命令或dbms_stats的包,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过统计有关. 如果table已经被统计过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器。 注意： 各项目在做压力测试时的必须清楚数据库系统当前使用的是何种优化器选择方式，有哪些表上是具有统计信息等。在上线后，需尽量与压力测试时保持一致，否则会带来不可预知的性能。 ORACLE在9i以后的版本中，已不提供基于规则的优化器。因此，使用基于成本的优化器是一种必然。 建议新的项目使用基于成本的优化器！！ 对表或索引进行统计 ORACLE在9i中，提供两种方式对数据库对象进行统计： DBMS_STATS包 ANALYZE DBMS_STATS提供了全面的对数据对象的分析功能，能够使基于成本的优化器工作更更精确。其提供对以下内容的统计： 对象类型 分析内容 表 记录数 数据块数 平均行的长度 列 列中不同值的数目 列中空值数 列中值的分步情况 索引 叶子占用数据块数 索引层数 系统统计 I/O性能使用率 CPU性能与使用率 该包提供以下过程： 过程 功能 举例 GATHER_INDEX_STATS 对索引的统计 dbms_stats.gather_table_stats(CC,EVENT_CDR) 该语句将对该表及所有字段及索引进行统计。 GATHER_TABLE_STATS 对表、索引、列的统计 GATHER_SCHEMA_STATS 对指定用户下的所有对象进行统计 GATHER_SYSTEM_STATS 统计CPU与I/O的性能 注意： ORACLE公司强烈建议用户使用dbsm_stats包进行统计，其为这个包相比analyze，不仅提供列精确细致的统计内容，而且还能进行并行分析等。 在实际使用中，常常会觉得analyze比dbms_stats要快。这是因为analyze分析的内容比较简单等原因，因而计算速度比较快。 ORACLE在以后的版本中，将仅提供对dbms_stats的功能。 并不需要每天或频繁地对数据对象进行统计，只有当数据分步或数据量发生比较大的变化时才需要。 选择合适的多表关联方式： 常见的表的关联方式