oracle数据库性能优化

本文参考的是以下链接：http://www.cnblogs.com/shined/archive/2012/06/29/2570335.html

写这篇文章的目的是实践一下性能调优。

一、简介

数据库是为存储数据而生的，数据表记录数很少的时候，我们可能不需要考虑查询性能，但是随着时间的推移，数据记录可能呈爆炸性增长，所以必须对查询进行优化。

二、调优出发点

要充分利用索引、SGA和存储过程等来提高查询效率。

三、调优方法

3.1、书写方式

3.1.1、根据oracle的共享内存SGA机制，oracle会对每个不同写法的sql语句进行分析，并占用共享内存。当sql语句完全相同时，oracle只分析一次，随后直接从共享内存中获取结果集，增加代码复用同时减少内存池开销。

3.1.2、使用动态SQL查询，避免重复解析。sql解析分为软解析和硬解析，关于解析的概念，可以参考：http://blog.chinaunix.net/uid-25909722-id-3363789.html

3.2、连接方式和顺序

3.2.1、表名顺序

表名顺序在一定情况下将影响查询的效率。From后面接表名，oracle解析器按照从右到左的顺序进行处理。首先，对from后接的最后一个表进行扫描，并对记录进行排序，接着扫描倒数第二个表，最后将倒数第一个表检索的记录与倒数第二个表中合适记录进行合并。

如：

RPT_HIST_COST_COMPARE_F_M表记录数为819， RPT_HIST_COST_COMPARE_F_D表记录数为368

sql1：     select count(*) from RPT_HIST_COST_COMPARE_F_D, RPT_HIST_COST_COMPARE_F_M;

sql2：     select count(*) from RPT_HIST_COST_COMPARE_F_M, RPT_HIST_COST_COMPARE_F_D;

sql1运行时间为0.125s，sql2运行时间为0.078s

3.2.2、查询条件顺序

ORACLE中采用自下而上的顺序解析where子句，因此，那些可以过滤掉大量数据的条件应该写在where条件的最后。

如：

FEE_BIG_NAME = '公共维护费'   记录条数100条，DATA_DATE = to_date('2015-12-31', 'YYYY-MM-DD')记录条数819条

sql1:    select * from RPT_HIST_COST_COMPARE_F_D where DATA_DATE = to_date('2015-12-31', 'YYYY-MM-DD') AND FEE_BIG_NAME = '公共维护费'
sql2:    select * from RPT_HIST_COST_COMPARE_F_D where FEE_BIG_NAME = '公共维护费' AND DATA_DATE = to_date('2015-12-31', 'YYYY-MM-DD')

sql1运行时间0.062s， sql2运行时间0.14s

3.3、语法和语义

3.3.1、select子句中避免使用‘select *’

使用'*'，oracle会查数据字典，依次解析各列名，增加查询时间，所以查询时应该改指定各列名，提高可读性的同时提高效率。

如：

sql1:  select * from RPT_HIST_COST_COMPARE_F_D

sql2: select DATA_DATE,FEE_BIG_CODE,FEE_BIG_NAME,FEE_SMALL_CODE,FEE_SMALL_NAME,BOOK_FLAG,SUBJECT_NO,SUBJECT_NAME,SUBACC_NO,SUBACC_NAME from RPT_HIST_COST_COMPARE_F_D

sql1执行时间0.078s，sql2执行时间0.047s

3.3.2、使用表别名（本条优化没有做实验，网上查找资料说不能提高查询效率，所以有待考证，也恳请知道的大神指点迷津）

多表查询时，使用表的别名，同样可以避免解析，避免歧义，提高效率。

3.4、函数和表达式

3.4.1、>=代替>

如：

sql1:   select BOOK_FLAG from rpt_hist_cost_compare_f_d where book_flag > 24
sql2:   select BOOK_FLAG from rpt_hist_cost_compare_f_d where book_flag >= 25

sql1耗时0.078s，sql2耗时0.062s

执行sql1时，先定位>24的记录，然后再从查找后的记录去筛选>24的记录，执行sql2时，直接定位到>=25的记录

3.4.2、使用decode函数减少处理时间

使用decode函数可以避免重复扫描相同记录或重复连接相同的表

3.4.3、用truncate代替delete

当删除表中的记录时，在通常情况下，rollback segments用来存放可以被恢复的信息，如果你没有commit事务，oracle会将数据恢复到删除以前的状态（执行删除命令之前）；而当运行truncate时，回滚段不再存放任何可被恢复的信息，当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短。但是truncate只在删除全表或分区适用，truncate是DDL不是DML。

3.5、常用关键字优先级

3.5.1、用exists替代in，用not exists代替not in

关于exists和in要根据表数据量大小来判断，如果数据了很大，建议用exists，表数据了小，建议用in。

先说in，通常情况下我们即认为是先将in子句里面的内容查询出来，然后对In的查询结果进行合并，再根据查询结果对主SQL进行一个个的查询，即相 当于以下转换。
　　select * from T1 where x in (select y from T2);　　
　　可以转换成如下　　
　　select * from T1,(select distinct y from T2) T2　　
　　where T1.x=T2.y;

再说exists，exists即先从主SQL中找到每一条合适的记录，然后将结果放到子SQL中与之匹配，即　　
　　select * from T1 where exists (select NULL from T2 where T2.y=T1.x);　　
　　可以转换成
　　for cursor1 in (select * from T1)　　
　　loop　　
　　if (exists (select NULL from T2 where T2.y=cursor1.x))　　
　　then　　
　　返回记录;　　
　　end if;　　
end loop;

3.5.2、用表连接替换exists（这个也没具体查证，请大神指点迷津）

3.5.3、用exists代替distinct（这个也等我仔细查证一下）

3.5.4、避免在索引列上使用is null和is not null

如果索引列包含任何可以为空的列，oracle将无法使用该索引，如：

索引失效：

select  * from A where index_a is not null;

索引有效：

select * from A where index_a >= 0;

3.5.5、用union-all替换union

如果情况中使用union all和union查询结果相同的情况下，应该选择union all摒弃union。

union对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

union all对两个结果集进行并集操作，包括重复行，不进行排序。

从这两个操作的流程来看，可以选择union all，就绝不选择union。

3.5.6、用union替换or（适用于索引列）

‘ 在存在多个索引列情况下，用union替换where子句中的or将会起到较好的效果，对索引列使用or将造成全表扫描。如果有字段没有被索引，查询效率or可能会更高。

3.5.7、用where替代order by

order by子句只在两种严格的条件下使用索引。

order by中所有的列必须半酣在相同的索引中并保持在索引中的排列顺序。

order by中所有的列必须定义为非空。

where子句使用的索引和order by子句中使用的索引不能并列

3.5.8、用where子句替代having子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 
例如: 
     低效: 
     SELECT REGION，AVG(LOG_SIZE) 
     FROM LOCATION 
     GROUP BY REGION 
     HAVING REGION REGION != ‘SYDNEY’ 
     AND REGION != ‘PERTH’ 
      高效 
     SELECT REGION，AVG(LOG_SIZE) 
     FROM LOCATION 
     WHERE REGION REGION != ‘SYDNEY’ 
     AND REGION != ‘PERTH’ 
     GROUP BY REGION 
 HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中.

3.5.9、group by

     提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下面两个查询返回相同结果但第二个明显就快了许多.

3.6、其他因素

3.6.1、减少访问数据库的次数

当执行每条SQL语句时，oracle的内部执行了许多工作：

解析sql语句；

估算索引的利用率；

绑定变量；

读数据块等；

所以，减少访问数据库的次数，能从实际上减少oracle的工作量。

3.6.2、常使用commit

在程序中尽量多用commit，这样程序的性能可以得到提高，需求也会因为commit所释放的资源而减少。commit释放资源：

回滚段上用于恢复的信息；

被程序语句获得的锁；

redo log buffer 中的空间；

oracle为管理上述3种资源的内部花费；