ORACLE SQL性能优化规范

ORACLESQL性能优化规范目录1.说明：..............................................................................................................................................32.高效SQL的特点：........................................................................................................................33.选用适合的ORACLE优化器.......................................................................................................34.对表或索引进行统计......................................................................................................................45.选择合适的多表关联方式：..........................................................................................................51.HASHJOIN.................................................................................................................................52.NESTEDLOOPJOIN..................................................................................................................63.SORTMERGEJOIN...................................................................................................................66.访问TABLE的方式......................................................................................................................77.HINT...............................................................................................................................................78.避免WHERE条件与索引字段类型不一致................................................................................79.共享SQL语句................................................................................................................................810.选择最有效率的表名顺序(只在基于规则的优化器中有效)...................................................911.使用DECODE函数来减少处理时间....................................................................................1012.删除重复记录...........................................................................................................................1013.用TRUNCATE替代DELETE..............................................................................................1114.尽量多使用COMMIT(针对OLTP性质系统)......................................................................1115.减少SQL语句中的表的关联数。..........................................................................................1216.EXISTS与IN的区别..............................................................................................................1317.避免在索引列上使用ISNULL和ISNOTNULL...............................................................1418.总是使用索引的第一个列.......................................................................................................1419.用UNION-ALL替换UNION(不等价)..............................................................................141.说明：本文档的目的是面向应用开发人员，为应用开发人员编写SQL提供帮助。因此，其调整定位于SQL级。对于整个性能调优而言，SQL级或应用级的调优是最经济最高效的。相对于SQL级的调整，数据库或系统级的调整对性能的影响也十份重要。如内存参数的调整、REDO日志的大小对检查点的影响等等。该部分内容不在此表述。本文档的前部分主要讲述优化SQL原则性、原理性内容，从第8节开始，主要描述常见的技巧。因此，前部分需引起足够的重视。2.高效SQL的特点：“幸福的家庭都是相似的,不幸的家庭各有不同”。引用到SQL编写：“高效的SQL都是相似的，低效的SQL却各不相同”。以下是高效SQL的通常特点：较少的物理I/O次数较少的内存GET次数使用高选择性的索引较少的排序操作避免在大表上做全表扫描3.选用适合的ORACLE优化器ORACLE的优化器共有3种:RULE(基于规则)COST(基于成本)CHOOSE(选择性)设置缺省的优化器,可以通过对init.ora文件或spfile中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.当然可在SQL语句级或是会话(session)级对其进行覆盖.为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),必须经常运行analyze命令或dbms_stats的包,以增加数据库中的对象统计信息(objectstatistics)的准确性.如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过统计有关.如果table已经被统计过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.在缺省情况下,ORACLE采用CHOOSE优化器。注意：1.各项目在做压力测试时的必须清楚数据库系统当前使用的是何种优化器选择方式，有哪些表上是具有统计信息等。在上线后，需尽量与压力测试时保持一致，否则会带来不可预知的性能。2.ORACLE在9i以后的版本中，已不提供基于规则的优化器。因此，使用基于成本的优化器是一种必然。3.建议新的项目使用基于成本的优化器！！4.对表或索引进行统计ORACLE在9i中，提供两种方式对数据库对象进行统计：DBMS_STATS包ANALYZEDBMS_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的性能注意：1．ORACLE公司强烈建议用户使用dbsm_stats包进行统计，其为这个包相比analyze，不仅提供列精确细致的统计内容，而且还能进行并行分析等。2．在实际使用中，常常会觉得analyze比dbms_stats要快。这是因为analyze分析的内容比较简单等原因，因而计算速度比较快。3．ORACLE在以后的版本中，将仅提供对dbms_stats的功能。4．并不需要每天或频繁地对数据对象进行统计，只有当数据分步或数据量发生比较大的变化时才需要。5.选择合适的多表关联方式：常见的表的关联方式有三种：1.HASHJOIN1．当需要进行多表关联操作时，选择使用全表扫描的方式在其中一个表的关联字段上创建HASH表（该表一般选择较小的表，以便可以存于SGA内存中，并达到提高探测的速度的目的），然后对另一表上进行全表扫描且以同样的算法构建HASH表，同时探测基于第一个表中上HASH表，查找匹配的键值。2．使用场景：大数据量等值关联查询，如关联查询大量用户资料及其清单数据3．等值联接2.NESTEDLOOPJOIN1.优化器选择一个驱动表A作为循环扫描的外部表2.另外一个相关联的表B作为内部表3.每扫描驱动表A中一条记录，ORACLE扫描B表中所有满足关联条件的记录selecta.cust_name,b.statefromcc.custa,cc.prodbwherea.id=b.idanda.id=:ID3.SORTMERGEJOIN1.当需要进行多表关联操作时，对相关联的表按关联字段进行排序，然后从各已排序好的结果中取得关联结果2.适用于非等值关联、数据量比较大的场合3.在对大数据量的关联查询，且条件为非等值条件，此时性能要好于nestedloopjoin4.在大多数情况下，HASHJOIN方式比该种关联方式高效6.访问Table的方式ORACLE采用两种访问表中记录的方式:1.全表扫描全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描.2.通过ROWID访问表可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息,ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以