《Oracle sql 性能优化调整.doc》由会员分享，可在线阅读，更多相关《Oracle sql 性能优化调整.doc（22页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、如有侵权，请联系网站删除，仅供学习与交流Oracle sql 性能优化调整【精品文档】第 22 页Oracle sql 性能优化调整 1. 共享SQL语句 特别是动态SQL，必须采用变量绑定，避免重复解析相同的SQL语句。2. 选择最有效率的表名顺序(只在基于规则的优化器中有效) ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中
2、最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 执行时间0.96秒 选择TAB2作为基础表 (不佳的方法) select count(*) from tab2,tab1 执行时间26.09秒 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
3、例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000
4、3. 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 50000 AND JOB = MANAGER; 4. S
5、ELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 * 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间. 5. 减少访问数据库的次数 当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量. 例如, 以下有三种方法可以检索出雇员号等于0342或0291的职员.
6、方法1 (最低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 方法2 (次低效) DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE FROM EMP WHERE EMP_NO = E_NO; BEGIN OPEN C1(342); FETCH C1 INTO ,.,. ; OPEN C1(291); FETCH C
7、1 INTO ,.,. ; CLOSE C1; END; 方法3 (高效) 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中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200. 6. 使用DECODE函数来减少处理时间 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 例
8、如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%; 你可以用DECODE函数高效地得到相同结果 SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,002
9、0,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SAL FROM EMP WHERE ENAME LIKE SMITH%; 类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中. 7. 用TRUNCATE替代DELETE 当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可
10、被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. TRUNCATE只在删除全表或分区适用,TRUNCATE是DDL不是DML8. 用Where子句替换HAVING子句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 例如: 低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY AND REGI
11、ON != PERTH 高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中9. 减少对表的查询 在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TA
12、B_COLUMNS WHERE VERSION = 604) ANDDB_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) Update 多个Column 例子: 低效: UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CAT
13、EGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 高效: UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 10. 通过内部函数提高SQL效率. SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*) FROM HISTOR
14、Y_TYPE T,EMP E,EMP_HISTORY H WHERE H.EMPNO = E.EMPNO AND H.HIST_TYPE = T.HIST_TYPE GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC; 通过调用下面的函数可以提高效率. FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2 AS TDESC VARCHAR2(30); CURSOR C1 IS SELECT TYPE_DESC FROM HISTORY_TYPE WHERE HIST_TYPE = TYP;
15、 BEGIN OPEN C1; FETCH C1 INTO TDESC; CLOSE C1; RETURN (NVL(TDESC,?); END; FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2 AS ENAME VARCHAR2(30); CURSOR C1 IS SELECT ENAME FROM EMP WHERE EMPNO=EMP; BEGIN OPEN C1; FETCH C1 INTO ENAME; CLOSE C1; RETURN (NVL(ENAME,?); END; SELECT H.EMPNO,LOOKUP_EMP(H
16、.EMPNO), H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*) FROM EMP_HISTORY H GROUP BY H.EMPNO , H.HIST_TYPE; 经常在论坛中看到如 能不能用一个SQL写出. 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的11. 使用表的别名(Alias) 当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. Column歧义指的是由于S
17、QL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属12. 用EXISTS替代IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 低效: SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB) 高效: SELECT * FROM EMP (基础表) WHERE EMPNO 0 AND EX
18、ISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB) 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出13. 用NOT EXISTS替代NOT IN 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例如: SELECT FROM EMP WHERE DEPT_NO NO
19、T IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=A); 为了提高效率.改写为: (方法一: 高效) SELECT . FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = A (方法二: 最高效) SELECT . FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A); 14. 用表连接替换EX
20、ISTS 通常来说 , 采用表连接的方式比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 ; 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP15. 用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和
21、雇员表)的查询时,避免在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核心模块将在子查询的条件一旦满足后,立刻返回结果. 16. 基础表的选择
22、 基础表(Driving Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同, SQL语句中基础表的选择是不一样的. 如果你使用的是CBO (COST BASED OPTIMIZER),优化器会检查SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径. 如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的连接条件都有索引对应, 在这种情况下, 基础表就是FROM 子句中列在最后的那个表. 举例: SELECT A.NAME , B.MANAGER FROMWORKER A, LODGING B WHEREA.LODGING
23、 = B.LODING; 由于LODGING表的LODING列上有一个索引, 而且WORKER表中没有相比较的索引, WORKER表将被作为查询中的基础表. 17. 多个平等的索引 当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录. 在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有 当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的. 如果不同表中两个想同等级的索引将被引用, FROM子句中表的顺
24、序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级. 如果相同表中两个想同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级. 举例: DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME, FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = A; 这里,DEPTNO索引将被最先检索,然后同EMP_CAT索引检索出的记录进行合并. 执行路径如下: TABLE ACCESS BY ROWID ON EMP AND-EQUAL INDEX RANGE SCAN ON DEPT_ID
25、X INDEX RANGE SCAN ON CAT_IDX 18. 等式比较和范围比较 当WHERE子句中有索引列, ORACLE不能合并它们,ORACLE将用范围比较. 举例: DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME FROM EMP WHERE DEPTNO 20 AND EMP_CAT = A; 这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON CAT_IDX 19. 不明确的索引等级
26、 当ORACLE无法判断索引的等级高低差别,优化器将只使用一个索引,它就是在WHERE子句中被列在最前面的. 举例: DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME FROM EMP WHERE DEPTNO 20 AND EMP_CAT A; 这里, ORACLE只用到了DEPT_NO索引. 执行路径如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX 我们来试一下以下这种情况: SQL select index_name, uniqueness from user_ind
27、exes where table_name = EMP; INDEX_NAME UNIQUENES - - EMPNO UNIQUE EMPTYPE NONUNIQUE SQL select * from emp where empno = 2 and emp_type = A ; no rows selected Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF EMP 2 1 INDEX (RANGE SCAN) OF EMPTYPE (NON-UNIQUE)
28、虽然EMPNO是唯一性索引,但是由于它所做的是范围比较, 等级要比非唯一性索引的等式比较低! 20. 强制索引失效 如果两个或以上索引具有相同的等级,你可以强制命令ORACLE优化器使用其中的一个(通过它,检索出的记录数量少) . 举例: SELECT ENAME FROM EMP WHERE EMPNO = 7935 AND DEPTNO + 0 = 10 /*DEPTNO上的索引将失效*/ AND EMP_TYPE | = A /*EMP_TYPE上的索引将失效*/ 这是一种相当直接的提高查询效率的办法. 但是你必须谨慎考虑这种策略,一般来说,只有在你希望单独优化几个SQL时才能采用它.
29、这里有一个例子关于何时采用这种策略, 假设在EMP表的EMP_TYPE列上有一个非唯一性的索引而EMP_CLASS上没有索引. SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS = X; 优化器会注意到EMP_TYPE上的索引并使用它. 这是目前唯一的选择. 如果,一段时间以后, 另一个非唯一性建立在EMP_CLASS上,优化器必须对两个索引进行选择,在通常情况下,优化器将使用两个索引并在他们的结果集合上执行排序及合并. 然而,如果其中一个索引（EMP_TYPE）接近于唯一性而另一个索引（EMP_CLASS）上有几千个重复的值. 排序
30、及合并就会成为一种不必要的负担. 在这种情况下,你希望使优化器屏蔽掉EMP_CLASS索引. 用下面的方案就可以解决问题. SELECT ENAME FROM EMP WHERE EMP_TYPE = A AND EMP_CLASS| = X; 21. 避免在索引列上使用计算 WHERE子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描 举例: 低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效: SELECT FROM DEPT WHERE SAL 25000/12; 这是一个非常实用的规则，请务必牢记 22. 自动选择索引 如果表中有两
31、个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性 在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引 举例: SELECT ENAME FROM EMP WHERE EMPNO = 2326 AND DEPTNO = 20 ; 这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录 TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX 23. 避免在索引列上使用NOT 通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的 影响. 当ORACLE”遇到”N
32、OT ,!=,他就会停止使用索引转而执行全表扫描. 举例: 低效: (这里,不使用索引) SELECT FROM DEPT WHERE DEPT_CODE != 0; 高效: (这里,使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0; 需要注意的是,在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符. NOT to = to NOT = NOT 24. 用=替代 如果DEPTNO上有一个索引, 高效: SELECT * FROM EMP WHERE DEPTNO =4 低效: SELECT * FROM EMP WHERE DEPTNO 3
33、 两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录. 25. 用UNION替换OR (适用于索引列) 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引. 高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE
34、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, 那就需要返回记录最少的索引列写在最前面. 建议别坚持，别和自己过不去。注意: WHERE KEY1 = 10 (返回最少记录) OR KEY2 = 20 (返回最多记录) ORACLE 内部将以上转换为
35、WHERE KEY1 = 10 AND (NOT KEY1 = 10) AND KEY2 = 20) 下面的测试数据仅供参考: (a = 1003 返回一条记录 , b = 1 返回1003条记录) SQL select * from unionvsor /*1st test*/ 2 where a = 1003 or b = 1; 1003 rows selected. Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) OF UNIO
36、NVSOR 3 2 INDEX (RANGE SCAN) OF UB (NON-UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF UNIONVSOR 5 4 INDEX (RANGE SCAN) OF UA (NON-UNIQUE) Statistics - 0 recursive calls 0 db block gets 144 consistent gets 0 physical reads 0 redo size 63749 bytes sent via SQL*Net to client 7751 bytes received via SQL*
37、Net from client 68 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1003 rows processed SQL select * from unionvsor /*2nd test*/ 2 where b = 1 or a = 1003 ; 1003 rows selected. Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION 2 1 TABLE ACCESS (BY INDEX ROWID) O
38、F UNIONVSOR 3 2 INDEX (RANGE SCAN) OF UA (NON-UNIQUE) 4 1 TABLE ACCESS (BY INDEX ROWID) OF UNIONVSOR 5 4 INDEX (RANGE SCAN) OF UB (NON-UNIQUE) Statistics - 0 recursive calls 0 db block gets 143 consistent gets 0 physical reads 0 redo size 63749 bytes sent via SQL*Net to client 7751 bytes received vi
39、a SQL*Net from client 68 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1003 rows processed SQL select * from unionvsor /*3rd test*/ 2 where a = 1003 3 union 4 select * from unionvsor 5 where b = 1; 1003 rows selected. Execution Plan - 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (UNIQUE) 2 1 UNION-ALL 3 2 TABLE ACCESS (BY INDEX ROWID) OF UNIONVSOR 4 3 INDEX (RANGE SCAN) OF UA (NON-UNIQUE) 5 2 TABLE ACCESS (BY INDEX ROWID) OF UNIONVSOR 6 5 INDEX (RANGE SCAN) OF UB (NON-UNIQUE) Statistics - 0 recursive calls 0 db block gets 10 consistent gets 0 physical reads