Oracle SQL性能优化的40条军规文档格式.docx
《Oracle SQL性能优化的40条军规文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle SQL性能优化的40条军规文档格式.docx（21页珍藏版）》请在冰点文库上搜索。
/*选择TAB2作为基础表（不佳的方法）*/
selectcount（*）fromtab2,tab1执行时间26.09秒
如果有3个以上的表连接查询,那就需要选择交叉表（intersectiontable）作为基础表,交叉表是指那个被其他表所引用的表。
SELECT*FROMLOCATIONL,CATEGORYC,EMPE
WHEREE.EMP_NOBETWEEN1000AND2000
ANDE.CAT_NO=C.CAT_NO
ANDE.LOCN=L.LOCN
将比下列SQL更有效率
SELECT*FROMEMPE,LOCATIONL,CATEGORYC
WHEREE.CAT_NO=C.CAT_NO
ANDE.EMP_NOBETWEEN1000AND2000
6.Where子句中的连接顺序
Oracle采用自下而上的顺序解析WHERE子句。
根据这个原理,表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
/*低效,执行时间156.3秒*/
SELECT…
FROMEMPE
WHERESAL>
50000
ANDJOB=‘MANAGER’
AND25<
（SELECTCOUNT（*）FROMEMP
WHEREMGR=E.EMPNO）
/*高效,执行时间10.6秒*/
WHERE25<
WHEREMGR=E.EMPNO）
ANDSAL>
7.SELECT子句中避免使用“*”
Oracle在解析SQL语句的时候，对于“*”将通过查询数据库字典来将其转换成对应的列名。
如果在Select子句中需要列出所有的Column时，建议列出所有的Column名称，而不是简单的用“*”来替代，这样可以减少多于的数据库查询开销。
8.减少访问数据库的次数
当执行每条SQL语句时,ORACLE在内部执行了许多工作：
解析SQL语句>
估算索引的利用率>
绑定变量>
读数据块等等
由此可见,减少访问数据库的次数,就能实际上减少ORACLE的工作量。
9.整个简单无关联的数据库访问
如果有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系），以减少多于的数据库IO开销。
虽然采取这种方法，效率得到提高，但是程序的可读性大大降低，所以还是要权衡之间的利弊。
10.使用Truncate而非Delete
Delete表中记录的时候，Oracle会在Rollback段中保存删除信息以备恢复。
Truncate删除表中记录的时候不保存删除信息，不能恢复。
因此Truncate删除记录比Delete快，而且占用资源少。
删除表中记录的时候，如果不需要恢复的情况之下应该尽量使用Truncate而不是Delete。
Truncate仅适用于删除全表的记录。
11.尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。
COMMIT所释放的资源：
回滚段上用于恢复数据的信息.
被程序语句获得的锁
redologbuffer中的空间
ORACLE为管理上述3种资源中的内部花费
12.计算记录条数
Selectcount（*）fromtablename;
Selectcount
（1）fromtablename;
Selectmax（rownum）fromtablename;
一般认为，在没有索引的情况之下，第一种方式最快。
如果有索引列，使用索引列当然最快。
13.用Where子句替换Having子句
避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。
这个处理需要排序、总计等操作。
如果能通过WHERE子句限制记录的数目，就能减少这方面的开销。
14.减少对表的查询操作
在含有子查询的SQL语句中，要注意减少对表的查询操作。
低效：
SELECTTAB_NAMEFROMTABLES
WHERETAB_NAME=（SELECTTAB_NAME
FROMTAB_COLUMNS
WHEREVERSION=604）
ANDDB_VER=（SELECTDB_VER
WHEREVERSION=604）
高效：
WHERE（TAB_NAME，DB_VER）=
（SELECTTAB_NAME，DB_VER
15.使用表的别名（Alias）
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属。
16.用EXISTS替代IN
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。
在这种情况下，使用EXISTS（或NOTEXISTS）通常将提高查询的效率。
SELECT*FROMEMP（基础表）
WHEREEMPNO>
0
ANDDEPTNOIN（SELECTDEPTNO
FROMDEPT
WHERELOC=‘MELB’）
ANDEXISTS（SELECT‘X’
WHEREDEPT.DEPTNO=EMP.DEPTNO
ANDLOC=‘MELB’）
17.用NOTEXISTS替代NOTIN
在子查询中，NOTIN子句将执行一个内部的排序和合并，对子查询中的表执行一个全表遍历，因此是非常低效的。
为了避免使用NOTIN，可以把它改写成外连接（OuterJoins）或者NOTEXISTS。
SELECT…
FROMEMP
WHEREDEPT_NONOTIN（SELECTDEPT_NO
WHEREDEPT_CAT=’A’）
SELECT….
WHERENOTEXISTS（SELECT‘X’
FROMDEPTD
WHERED.DEPT_NO=E.DEPT_NO
ANDDEPT_CAT=‘A’）
18.用表连接替换EXISTS
通常来说，采用表连接的方式比EXISTS更有效率。
SELECTENAME
WHEREEXISTS（SELECT‘X’
FROMDEPT
WHEREDEPT_NO=E.DEPT_NO
FROMDEPTD，EMPE
WHEREE.DEPT_NO=D.DEPT_NO
ANDDEPT_CAT=‘A’
19.用EXISTS替换DISTINCT
当提交一个包含对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。
一般可以考虑用EXIST替换。
EXISTS使查询更为迅速，因为RDBMS核心模块将在子查询的条件一旦满足后，立刻返回结果。
SELECTDISTINCTDEPT_NO，DEPT_NAME
SELECTDEPT_NO，DEPT_NAME
WHEREEXISTS（SELECT‘X’
WHEREE.DEPT_NO=D.DEPT_NO
20.识别低效的SQL语句
下面的SQL工具可以找出低效SQL：
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND（（BUFFER_GETS-DISK_READS）/BUFFER_GETS,2）Hit_radio,
ROUND（DISK_READS/EXECUTIONS,2）Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>
ANDBUFFER_GETS>
0
AND（BUFFER_GETS-DISK_READS）/BUFFER_GETS<
0.8
ORDERBY4DESC
另外也可以使用SQLTrace工具来收集正在执行的SQL的性能状态数据，包括解析次数，执行次数，CPU使用时间等。
21.用ExplainPlan分析SQL语句
EXPLAINPLAN是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句.通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表（索引扫描或全表扫描）以及使用到的索引名称。
22.SQLPLUS的TRACE
SQL>
list
1SELECT*
2FROMdept,emp
3*WHEREemp.deptno=dept.deptno
setautotracetraceonly/*traceonly可以不显示执行结果*/
/
14rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10NESTEDLOOPS
21TABLEACCESS（FULL）OF'
EMP'
31TABLEACCESS（BYINDEXROWID）OF'
DEPT'
43INDEX（UNIQUESCAN）OF'
PK_DEPT'
（UNIQUE）
23.用索引提高效率
（1）特点
优点：
提高效率主键的唯一性验证
代价：
需要空间存储定期维护
重构索引：
ALTERINDEX<
INDEXNAME>
REBUILD<
TABLESPACENAME>
（2）Oracle对索引有两种访问模式
索引唯一扫描（IndexUniqueScan）
索引范围扫描（IndexRangeScan）
（3）基础表的选择
基础表（DrivingTable）是指被最先访问的表（通常以全表扫描的方式被访问）。
根据优化器的不同，SQL语句中基础表的选择是不一样的。
如果你使用的是CBO（COSTBASEDOPTIMIZER），优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。
如果你用RBO（RULEBASEDOPTIMIZER），并且所有的连接条件都有索引对应，在这种情况下，基础表就是FROM子句中列在最后的那个表。
（4）多个平等的索引
当SQL语句的执行路径可以使用分布在多个表上的多个索引时，ORACLE会同时使用多个索引并在运行时对它们的记录进行合并，检索出仅对全部索引有效的记录。
在ORACLE选择执行路径时，唯一性索引的等级高于非唯一性索引。
然而这个规则只有当WHERE子句中索引列和常量比较才有效。
如果索引列和其他表的索引类相比较。
这种子句在优化器中的等级是非常低的。
如果不同表中两个相同等级的索引将被引用，FROM子句中表的顺序将决定哪个会被率先使用。
FROM子句中最后的表的索引将有最高的优先级。
如果相同表中两个相同等级的索引将被引用，WHERE子句中最先被引用的索引将有最高的优先级。
（5）等式比较优先于范围比较
DEPTNO上有一个非唯一性索引，EMP_CAT也有一个非唯一性索引。
WHEREDEPTNO>
20
ANDEMP_CAT=‘A’;
这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较.执行路径如下:
TABLEACCESSBYROWIDONEMP
INDEXRANGESCANONCAT_IDX
即使是唯一性索引，如果做范围比较，其优先级也低于非唯一性索引的等式比较。
（6）不明确的索引等级
当ORACLE无法判断索引的等级高低差别，优化器将只使用一个索引,它就是在WHERE子句中被列在最前面的。
ANDEMP_CAT>
‘A’;
这里,ORACLE只用到了DEPT_NO索引.执行路径如下:
INDEXRANGESCANONDEPT_IDX
（7）强制索引失效
如果两个或以上索引具有相同的等级，你可以强制命令ORACLE优化器使用其中的一个（通过它,检索出的记录数量少）。
FROMEMP
WHEREEMPNO=7935
ANDDEPTNO+0=10/*DEPTNO上的索引将失效*/
ANDEMP_TYPE||‘’=‘A’/*EMP_TYPE上的索引将失效*/
（8）避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分。
优化器将不使用索引而使用全表扫描。
WHERESAL*12>
25000;
25000/12;
（9）自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性索引。
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引。
WHEREEMPNO=2326
ANDDEPTNO=20;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录。
INDEXUNIQUESCANONEMP_NO_IDX
（10）避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。
当ORACLE遇到NOT，它就会停止使用索引转而执行全表扫描。
低效:
（这里，不使用索引）
SELECT…
WHERENOTDEPT_CODE=0
（这里，使用了索引）
WHEREDEPT_CODE>
24.用>
=替代>
如果DEPTNO上有一个索引
高效:
SELECT*
=4
3
两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO等于3的记录并且向前扫描到第一个DEPT大于3的记录.
25.用Union替换OR（适用于索引列）
通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。
对索引列使用OR将造成全表扫描。
注意，以上规则只针对多个索引列有效。
SELECTLOC_ID,LOC_DESC,REGION
FROMLOCATION
WHERELOC_ID=10
UNION
SELECTLOC_ID,LOC_DESC,REGION
WHEREREGION=“MELBOURNE”
WHERELOC_ID=10ORREGION=“MELBOURNE”
26.用IN替换OR
SELECT….
WHERELOC_ID=10
ORLOC_ID=20
ORLOC_ID=30
SELECT…
WHERELOC_ININ（10，20，30）
实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的。
27.避免在索引列上使用isnull和isnotnull
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。
（索引失效）
FROMDEPARTMENT
WHEREDEPT_CODEISNOTNULL;
（索引有效）
WHEREDEPT_CODE>
=0;
28.总是使用索引的第一个列
如果索引是建立在多个列上，只有在它的第一个列（leadingcolumn）被where子句引用时，优化器才会选择使用该索引。
createindexmultindexonmultiindexusage（inda,indb）;
Indexcreated.
select*frommultiindexusagewhereindb=1;
10TABLEACCESS（FULL）OF'
MULTIINDEXUSAGE‘
很明显,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
29.使用UNIONALL替代UNION
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。
如果用UNIONALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。
由于UNIONALL的结果没有经过排序，而且不过滤重复的记录，因此是否进行替换需要根据业务需求而定。
30.对UNION的优化
由于UNION会对查询结果进行排序，而且过滤重复记录，因此其执行效率没有UNIONALL高。
UNION操作会使用到SORT_AREA_SIZE内存块，因此对这块内存的优化也非常重要。
可以使用下面的SQL来查询排序的消耗量：
selectsubstr（name，1，25）"
SortAreaName"
，
substr（value，1，15）"
Value"
fromv$sysstat
wherenamelike'
sort%'
31.避免改变索引列的类型
当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。
/*假设EMP_TYPE是一个字符类型的索引列.*/
WHEREEMP_TYPE=123
/*这个语句被ORACLE转换为:
*/
WHERETO_NUMBER（EMP_TYPE）=123
因为内部发生的类型转换，这个索引将不会被用到。
几点注意：
当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。
如果在索引列上面进行了隐式类型转换，在查询的时候将不会用到索引。
注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。
为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式表现出来。
32.使用提示（Hints）
FULLhint告诉ORACLE使用全表扫描的方式访问指定表。
ROWIDhint告诉ORACLE使用TABLEACCESSBYROWID的操作访问表。
CACHEhint来告诉优化器把查询结果数据保留在SGA中。
INDEXHint告诉ORACLE使用基于索引的扫描方式。
其他的OracleHints
ALL_ROW
升级会员 