oracle sql性能优化规范

《oracle sql性能优化规范》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。

1、ORACLESQL性能优化规范目录1.说明：42.高效SQL的特点：43.选用适合的ORACLE优化器44.对表或索引进行统计55.选择合适的多表关联方式：61.HASHJOIN62.NESTEDLOOPJOIN73.SORTMERGEJOIN76.访问TABLE的方式87.HINT88.避免WHERE条件与索引字段类型不一致89.共享SQL语句910.选择最有效率的表名顺序(只在基于规则的优化器中有效)1011.使用DECODE函数来减少处理时间1112.删除重复记录1113.用TRUNCATE替代DELETE1214.尽量多使用COMMIT(针对OLTP性质系统)1215.减少SQL

2、语句中的表的关联数。1316.EXISTS与IN的区别1417.避免在索引列上使用ISNULL和ISNOTNULL1518.总是使用索引的第一个列1519.用UNION-ALL替换UNION(不等价)161.说明：本文档的目的是面向应用开发人员，为应用开发人员编写SQL提供帮助。因此，其调整定位于SQL级。对于整个性能调优而言，SQL级或应用级的调优是最经济最高效的。相对于SQL级的调整，数据库或系统级的调整对性能的影响也十份重要。如内存参数的调整、REDO日志的大小对检查点的影响等等。该部分内容不在此表述。本文档的前部分主要讲述优化SQL原则性、原理性内容，从第8节开始，主要描述常见的

3、技巧。因此，前部分需引起足够的重视。2.高效SQL的特点：“幸福的家庭都是相似的,不幸的家庭各有不同”。引用到SQL编写：“高效的SQL都是相似的，低效的SQL却各不相同”。以下是高效SQL的通常特点：n较少的物理I/O次数n较少的内存GET次数n使用高选择性的索引n较少的排序操作n避免在大表上做全表扫描3.选用适合的ORACLE优化器ORACLE的优化器共有3种:nRULE(基于规则)nCOST(基于成本)nCHOOSE(选择性)设置缺省的优化器,可以通过对init.ora文件或spfile中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROW

4、S,FIRST_ROWS.当然可在SQL语句级或是会话(session)级对其进行覆盖.为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),必须经常运行analyze命令或dbms_stats的包,以增加数据库中的对象统计信息(objectstatistics)的准确性.如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过统计有关.如果table已经被统计过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.在缺省情况下,ORACLE采用CHOOSE优化器。注意：1.各项目在做压力测试时的必须清楚数据库系统当前使

5、用的是何种优化器选择方式，有哪些表上是具有统计信息等。在上线后，需尽量与压力测试时保持一致，否则会带来不可预知的性能。2.ORACLE在9i以后的版本中，已不提供基于规则的优化器。因此，使用基于成本的优化器是一种必然。3.建议新的项目使用基于成本的优化器！！1.对表或索引进行统计ORACLE在9i中，提供两种方式对数据库对象进行统计：nDBMS_STATS包nANALYZEDBMS_STATS提供了全面的对数据对象的分析功能，能够使基于成本的优化器工作更更精确。其提供对以下内容的统计：对象类型分析内容表记录数数据块数平均行的长度列列中不同值的数目列中空值数列中值的分步情况索引叶子占用数据

6、块数索引层数系统统计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的性能注意：1．ORACLE公司强烈建议用户使用dbsm_stats包进行统计，其为这个包相比analyze，不仅提供列精确细致的统计内容，而且还能

7、进行并行分析等。2．在实际使用中，常常会觉得analyze比dbms_stats要快。这是因为analyze分析的内容比较简单等原因，因而计算速度比较快。3．ORACLE在以后的版本中，将仅提供对dbms_stats的功能。4．并不需要每天或频繁地对数据对象进行统计，只有当数据分步或数据量发生比较大的变化时才需要。2.选择合适的多表关联方式：常见的表的关联方式有三种：1.HASHJOIN1．当需要进行多表关联操作时，选择使用全表扫描