oracle的常用性能优化

oracle 常用性能优化

1.选择最有效率的表名顺序

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。
当ORACLE处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表(FROM子句中最后的那个表)并对记录进行派序，然后扫描第二个表(FROM子句中最后第二个表)，最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
只在基于规则的优化器中有效。
举例：

表 TAB1 16,384 条记录

表 TAB2 1 条记录

 /*选择TAB2作为基础表 (最好的方法)*/
  select count(*) from tab1,tab2   执行时间0.96秒
  
   /*选择TAB2作为基础表 (不佳的方法)*/
  select count(*) from tab2,tab1   执行时间26.09秒

2.使用Truncate而非Delete

Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。Truncate删除表中记录的时候不保存删除信息，不能恢复。因此Truncate删除记录比Delete快，而且占用资源少。
删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。
Truncate仅适用于删除全表的记录。
3. SELECT子句中避免使用“*”

Oracle在解析SQL语句的时候，对于“”将通过查询数据库字典来将其转换成对应的列名。
如果在Select子句中需要列出所有的Column时，建议列出所有的Column名称，而不是简单的用“”来替代，这样可以减少多于的数据库查询开销。
4. 用Where子句替换Having子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。 如果能通过WHERE子句限制记录的数目，就能减少这方面的开销。
5. 使用表的别名（Alias）

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属。
6.用 >= 替代 >

如果DEPTNO上有一个索引

高效:

SELECT *
     FROM EMP
   WHERE DEPTNO >=4

低效：

SELECT *
     FROM EMP
   WHERE DEPTNO >3

7.用Union替换OR（适用于索引列）

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。 注意，以上规则只针对多个索引列有效。

高效：

SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
   WHERE LOC_ID = 10
   UNION
   SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
   WHERE REGION = “MELBOURNE”

低效：

SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
   WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

8.用IN替换OR

低效：

SELECT….
  FROM LOCATION
WHERE LOC_ID = 10
       OR  LOC_ID = 20
       OR  LOC_ID = 30

高效：

SELECT…
  FROM LOCATION
WHERE LOC_IN IN （10，20，30）

SELECT…
  FROM LOCATION
WHERE LOC_IN IN （10，20，30）

9.SQL语句执行步骤

语法分析> 语义分析> 视图转换 >表达式转换> 选择优化器 >选择连接方式 >选择连接顺序 >选择数据的搜索路径 >运行“执行计划”
10.Where子句中的连接顺序

Oracle采用自下而上的顺序解析WHERE子句。 根据这个原理,表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

/*低效,执行时间156.3秒*/
SELECT … 
  FROM EMP E
WHERE  SAL > 50000
     AND  JOB = ‘MANAGER’
     AND  25 < (SELECT COUNT(*) FROM EMP
                         WHERE MGR = E.EMPNO)

/*高效,执行时间10.6秒*/
SELECT … 
  FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
                        WHERE MGR=E.EMPNO)
     AND SAL > 50000
     AND JOB = ‘MANAGER’

10.用表连接替换EXISTS

通常来说 ，采用表连接的方式比EXISTS更有效率 。

低效：

SELECT ENAME
   FROM EMP E
WHERE EXISTS （SELECT ‘X’ 
                  FROM DEPT
              WHERE DEPT_NO = E.DEPT_NO
                           AND DEPT_CAT = ‘A’） 

高效：

SELECT ENAME
   FROM DEPT D，EMP E
WHERE E.DEPT_NO = D.DEPT_NO
     AND DEPT_CAT = ‘A’ 

11.用索引提高效率

（1）特点

优点： 提高效率 主键的唯一性验证

代价： 需要空间存储 定期维护

重构索引：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（2）Oracle对索引有两种访问模式

索引唯一扫描 (Index Unique Scan)
索引范围扫描 (Index Range Scan)
（3）基础表的选择

基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问)。 根据优化器的不同，SQL语句中基础表的选择是不一样的。
如果你使用的是CBO (COST BASED OPTIMIZER)，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。
如果你用RBO (RULE BASED OPTIMIZER)， 并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM 子句中列在最后的那个表。
（4）多个平等的索引

当SQL语句的执行路径可以使用分布在多个表上的多个索引时，ORACLE会同时使用多个索引并在运行时对它们的记录进行合并，检索出仅对全部索引有效的记录。
在ORACLE选择执行路径时，唯一性索引的等级高于非唯一性索引。然而这个规则只有当WHERE子句中索引列和常量比较才有效。如果索引列和其他表的索引类相比较。这种子句在优化器中的等级是非常低的。
如果不同表中两个相同等级的索引将被引用，FROM子句中表的顺序将决定哪个会被率先使用。 FROM子句中最后的表的索引将有最高的优先级。
如果相同表中两个相同等级的索引将被引用，WHERE子句中最先被引用的索引将有最高的优先级。
（5）自动选择索引

如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性索引。在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引。

SELECT ENAME
  FROM EMP
WHERE EMPNO = 2326  
     AND DEPTNO  = 20 ;

12.几种不能使用索引的WHERE子句

（1）下面的例子中，‘!=’ 将不使用索引 ，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。

不使用索引：

 SELECT ACCOUNT_NAME
      FROM TRANSACTION
   WHERE AMOUNT !=0；

使用索引：

SELECT ACCOUNT_NAME
      FROM TRANSACTION
    WHERE AMOUNT > 0；

（2）下面的例子中，‘||’是字符连接函数。就象其他函数那样，停用了索引。

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’；

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
     AND ACCOUNT_TYPE=’ A’；

（3）下面的例子中，‘+’是数学函数。就象其他数学函数那样，停用了索引。

不使用索引：

SELECT ACCOUNT_NAME，AMOUNT
  FROM TRANSACTION
WHERE AMOUNT + 3000 >5000；

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ；

（4）下面的例子中，相同的索引列不能互相比较，这将会启用全表扫描。

不使用索引：

SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME, ACCOUNT_NAME)

使用索引：

SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME, ’%’)

13. 避免使用耗费资源的操作

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。
通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其他方式重写。
14. 优化GROUP BY

提高GROUP BY语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉。

低效：

SELECT JOB ，AVG（SAL）
    FROM EMP
  GROUP BY JOB
HAVING JOB = ‘PRESIDENT’
         OR JOB = ‘MANAGER’

高效：

SELECT JOB，AVG（SAL）
   FROM EMP
WHERE JOB = ‘PRESIDENT’
        OR JOB = ‘MANAGER’
GROUP BY JOB

15. 使用日期

当使用日期时，需要注意如果有超过5位小数加到日期上，这个日期会进到下一天!

SELECT TO_DATE（‘01-JAN-93’+.99999）
  FROM DUAL
Returns：
’01-JAN-93 23:59:59’

SELECT TO_DATE（‘01-JAN-93’+.999999）
  FROM DUAL
Returns：
’02-JAN-93 00:00:00’

16.分离表和索引

总是将你的表和索引建立在不同的表空间内（TABLESPACES）。
决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。
确保数据表空间和索引表空间置于不同的硬盘上。