Oracle Sql语句调优

Oracle Sql语句调优

 

下面给出SQL优化经常遇到的情形。它们是在很多人长期积累的经验上总结出来的，这些都是一些一般性的结论，在实际工作中，还应当具体情况具体分析，并加以灵活运用。

(1) 用NOT EXISTS替换NOT IN 将更显著地提高效率，尽量用NOT EXISTS替代NOT IN。在子查询中，NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

例如，

SELECT ENMAE

FROM EMP

WHERE DEPTNO NOT IN

(

SELECT DEPTNO

FROM DEPT

WHERE LOC='BOSTON'

);

为了提高效率，可改写为，

(方法一：高效)

SELECT ENAME

FROM EMP A,DEPT B

WHERE A.DEPTNO = B.DEPTNO(+)

AND B.DEPTNO IS NULL

AND B.LOC(+) = 'BOSTON'

(方法二：最高效)

SELECT ENAME

FROM EMP E

WHERE NOT EXISTS

( SELECT 'X' FROM DEPT D WHERE

D.DEPTNO = E.DEPTNO AND LOC='BOSTON'

);

除此之外，推荐使用NOT EXISTS的一个重要的原因是NOT EXISTS在当子查询可能返回NULL的情况下，更加可靠，这是因为在比较列表中如果包含NULL，NOT IN条件将为false。考虑下面的语句，该语句试图在EMP表中查询出来没有一个下属的人员列表：

SELECT ENAME, JOB FROM EMP

WHERE EMPNO NOT IN (SELECT MGR FROM EMP);

上面查询不会返回任何结果，因为某些记录的MGR字段是NULL值。正确的写法是：

SELECT ENAME, JOB FROM EMP E

WHERE NOT EXISTS (SELECT MGR FROM EMP WHERE MGR=E.EMPNO);

(2) 尽量避免在索引列上进行计算。

WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。例如：

低效的写法：

SELECT …

FROM DEPT

WHERE SAL * 12 > 25000;

高效的写法：

SELECT …

FROM DEPT

WHERE SAL > 25000/12;

这是一个非常实用的规则，请务必牢记。

(3) 用>=替代>

如果DEPTNO上有一个索引，

高效的写法：

SELECT *

FROM EMP

WHERE DEPTNO >=4

低效的写法：

SELECT *

FROM EMP

WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

(4) 用UNION替换OR (适用于索引列)

通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。在下面的例子中, LOC_ID 和REGION上都建有索引。

高效的写法：

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”

如果你坚持要用OR, 那就请记住返回记录最少的索引列写在最前面。

(5) 不管在什么情况下，总是使用索引的第一个列（对于组合索引而言）。

如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。

(6) SQL语句中尽量使用绑定变量（binding variable）

使用绑定变量，可以减少数据库服务器对SQL语句的parse时间，从而提高效率。举例说明：

第一种情形。假定下面两条命令是由两个不同的用户安先后顺序发出，那么第二发出命令的用户执行该命令所需要的时间要比第一个用户少很多，这是因为第一个用户发出指令后，数据库服务器已经将该语句进行了parse，并且cache到内存中，第二个用户发出同样的命令后，数据库服务器首先将在cache中查找是否有同样的语句。

select pin, name from people where pin = :blk1.pin;

select pin, name from people where pin = :blk1.pin;

事实上上面的语句就是oracle数据库服务器parse以后的形式。

第二种情形。假定下面两条命令是由两个不同的用户安先后顺序发出，那么第二发出命令的用户执行该命令所需要的时间和第一个用户少所需要的时间差不多。

select pin, name from people where pin = 2;

select pin, name from people where pin = 3;

(7) 在From子句中，选择有效的表名顺序。

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。案例二就是这种情况。

(8) WHERE子句中的连接顺序．

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

例如：

低效的写法：

SELECT …

FROM EMP E

WHERE SAL > 50000

AND JOB = ‘MANAGER'

AND 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO);

高效的写法：

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL > 50000

AND JOB = ‘MANAGER';

(9) 减少访问数据库的次数

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句, 估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。例如，以下有两种种方法可以检索出雇员号等于342或291的职员。

低效的写法：

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE EMP_NO = 291;

高效的写法：

SELECT A.EMP_NAME, A.SALARY, A.GRADE,

B.EMP_NAME, B.SALARY, B.GRADE

FROM EMP A, EMP B

WHERE A.EMP_NO = 342

AND B.EMP_NO = 291;

注意： 在SQL*Plus , SQL*Forms和Pro*C(对于Oracle，在C或者C++中，写嵌入式SQL语句的开发包)中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。

(10) 使用DECODE函数来减少处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

例如：

SELECT COUNT(*)，SUM(SAL)

FROM　EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE　‘SMITH%';

SELECT COUNT(*)，SUM(SAL)

FROM　EMP

WHERE DEPT_NO = 0030

AND ENAME LIKE　‘SMITH%';

你可以用DECODE函数高效地得到相同结果，

SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,

COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,

SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%';

类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

(11) 尽量避免使用colname like '%somestring%' 和colname like '%somestring'。如果在字段colname上建有索引，那么类似的like语句，会使索引在该查询中失效。如此种需要不可避免，建议使用全文索引（full-text index）。详细情况请见http://blog.sina.com.cn/s/blog_5f5fff010100cs7g.html。

(12) 尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。COMMIT所释放的资源包括：

回滚段上用于恢复数据的信息.

被程序语句获得的锁

redo log buffer 中的空间

ORACLE为管理上述3种资源中的内部花费

(13) 减少对表的查询

在含有子查询的SQL语句中,要特别注意减少对表的查询。例如：

低效的写法：

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = ( SELECT TAB_NAME

FROM TAB_COLUMNS

WHERE VERSION = 604)

AND　DB_VER= ( SELECT DB_VER

FROM TAB_COLUMNS

WHERE VERSION = 604)

高效的写法：

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME,DB_VER)

= ( SELECT TAB_NAME,DB_VER)

FROM TAB_COLUMNS

WHERE VERSION = 604)

(14) 用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换。例如：

低效的写法：

SELECT DISTINCT DEPT_NO,DEPT_NAME

FROM DEPT D,EMP E

WHERE D.DEPT_NO = E.DEPT_NO

高效的写法：

SELECT DEPT_NO,DEPT_NAME

FROM DEPT D

WHERE EXISTS ( SELECT ‘X'

FROM EMP E

WHERE E.DEPT_NO = D.DEPT_NO);

EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。

(15) 定期的重构索引是有必要的。语法为：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

(16) 尽量使用package。因为Oracle系统会尽量将package及其包含的存储过程和存储函数cache在内存中。下面是创建package的一些使用技巧：

创建包：

CREATE OR REPLACE PACKAGE comm_package

IS

g_comm_rate NUMBER := 0.1;

FUNCTION validate_comm_rate(v_comm_rate IN NUMBER) RETURN BOOLEAN;

PROCEDURE reset_comm_rate(v_comm_rate IN NUMBER);

END comm_package;

创建包体：

CREATE OR REPLACE PACKAGE BODY comm_package

IS

FUNCTION validate_comm_rate(v_comm_rate IN NUMBER) RETURN BOOLEAN

IS

v_max_comm_rate NUMBER;

BEGIN

SELECT MAX(comm/sal) INTO v_max_comm_rate FROM emp;

IF v_comm_rate > v_max_comm_rate THEN

RETURN(FALSE);

ELSE

RETURN(TRUE);

END IF;

END validate_comm_rate;

PROCEDURE reset_comm_rate(v_comm_rate IN NUMBER)

IS

v_valid BOOLEAN;

BEGIN

v_valid := validate_comm_rate(v_comm_rate);

IF v_valid = TRUE THEN

g_comm_rate := v_comm_rate;

ELSE

RAISE_APPLICATION_ERROR(-20210, 'Invalid comm rate.');

END IF;

END reset_comm_rate;

END comm_package;

注意：包和包体不能同时编译。正确的步骤是，先运行创建包的脚本，然后再运行创建包体的脚本。上述包体中包含了一个存储过程（reset_comm_rate）和一个存储函数（validate_comm_rate）。一般而言，存储过程和存储函数的差别是，存储过程没有返回值，而存储函数有返回值。

在sqlplus环境下，运行包中的存储过程，可以进行一下操作：

SQL> execute comm_package.reset_comm_rate(0.5);

SQL> execute scott.comm_package.g_comm_rate := 0.5;

上面两条条语句的效果是一样的，第一种方式是调用了存储过程，第二种方式是直接在sqlplus环境下给包中的变量赋值，但第二种方式只在同一个session中有效。在sqlplus环境下，显示包中的变量的值，可以如下操作：

SQL> set serverout on

SQL> execute dbms_output.put_line(comm_package.g_comm_rate);

.5

(17) 用下列SQL工具找出效率最低的SQL语句：

SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,

ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,

ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,

SQL_TEXT

FROM V$SQLAREA

WHERE EXECUTIONS>0

AND BUFFER_GETS > 0

AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

ORDER BY 4 DESC;

(18) 在SQL Plus环境下使用EXPLAIN PLAIN对效率低下的SQL语句进行诊断。

在使用这个命令之前，请先运行$ORACLE_HOME/rdbms/admin/utlxplan.sql，比如：

SQL> start D:\oracle\ora92\rdbms\admin\utlxplan.sql；

然后如下图方式，对相应的SQL语句进行诊断：

图1 explain plan的执行1

上图中，我们可以看到被分析的语句select * from emp where empno=7389使用的是基于规则的优化(rule based optimization)，扫描是根据索引PK_EMP来进行的（即主键，说明：主键一定是唯一索引的，即Unique Index），那么性能应该也是最好的，访问数据表emp中的记录是按照rowid来进行的，这也是oracle中最快的方式了。我们现在换一个效率比较差的查询来看看：

图2 explain plan的执行2

从上图中不难发现，这次查询根本没有用到任何索引，是对EMP进行了全表扫描的(TABLE ACCESS FULL)。Explain plan的输出各部分的含义，详细情况请翻阅有关资料。

(19) 必要的时候使用CACHE提示(HINT)

一般而言，如果一个表记录数不是很多而且被经常用到，比如字典表，可以通过提示的方式，将该表的记录存放在内存（SGA）中。比如：

SELECT * FROM EMP;

而且在数据库服务器启动后，只需要执行一次就可以了，在后续的工作中，如果需要从EMP表查询数据，则数据库系统会直接在内存中查找。

(20) 如果要在取值空间很小的字段上建立索引，那么请一定建立位图索引(Bitmap Index)，创建位图索引的基本语法如下：

create bitmap index idx_bitm on

table_name (column_name)

tablespace tablespace_name;

关于位图索引的详细情况在很多书上均有著述，在此就不赘述了。

(21) 如果可能，可以购买并使用专业的数据库调优工具，如Quest Central for Databases。