OracleSQL性能优化方法.docx
《OracleSQL性能优化方法.docx》由会员分享，可在线阅读，更多相关《OracleSQL性能优化方法.docx（17页珍藏版）》请在冰豆网上搜索。
OracleSQL性能优化方法
OracleSQL性能优化方法
Oracle性能优化方法（SQL篇）1
1輕2
2表分区的应用2
3访咨询Table的方式3
4共享SQL语句3
5选择最有效率的表名顺序5
6WHERE子句中的连接顺序.6
7SELECT子句中幸免使用*6
8减少访咨询数据库的次数6
9使用DECODE函数来减少处理时刻7
1P整合简单.无关联的数据库访咨询8
H删除重复记录8
\2用TRUNCATE替代DELETE9
13尽量多使用COMMIT9
14运算记录条数9
15用Where子句替换HAVING子句9
16减少对表的查询10
17通过内部函数提高SQL效率.11
18使用表的不名（Alias）12
用EXISTS替代IN12
20用NOTEXISTS替代NOTIN13
21识不低效执行的SQL语句13
22使用TKPROF工具来查询SQL性能状态14
23用EXPLAINPLAN分析SQL语句14
24实时批量的处理16
1综述
ORACLE数拯库的性能调整是个重要，却又有难度的话题，如何有效地进行调整,需要通过反反复复的过程。
在数据库建立时，就能依照顾用的需要合理设讣分配表空间以及储备参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后,又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存体会，从而更好地进行数据库的调优。
数据库性能调优的方法
•调整内存
•调整I/O
•调整资源的争用咨询题
•调整操作系统参数
•调整数据库的设计
•调整应用程序
本文针对应用程序的调整，来讲明对数据库性能如何进行优化。
2表分区的应用
关于海量数据的表，能够考虑建立分区以提髙操作效率。
建立分区一样以关键字为分区的标志，也能够以英他字段作为分区的标志，但效率不如关键字髙。
建立分区的语句在建表时能够进行讲明：
createtableTABLENAME（）
partitionbyrange（PutOutNo）
（partitionPARTIvalueslessthan（200312319999）
partitionPART2valueslessthan（200412319999）
如此，在进行大部分数据査询，数据更新和数据插入时，Oracle自动判泄操作应该在哪个分区进行，幸免了整表操作，提髙了执行的效率
3访咨询Table的方式
ORACLE采纳两种访咨询表中记录的方式：
•全表扫描
全表扫描确实是顺序地访咨询表中每条记录.ORACLE采纳一次读入多个数据块（databaseblock）的方式优化全表扫描.
•通过ROWID访咨询表
能够采纳基于ROWID的访咨询方式情形，提髙访咨询表的效率,，ROWID包含了表中记录的物理位置信息..0RACLE采纳索引（INDEX）实现了数据和存放数据的物理位置（ROWID）之间的联系.通常索引提供了快速访咨询ROWID的方法，因此那些基于索引列的查询就能够得到性能上的提高.
4共享SQL语句
数据库治理员必须在init.ora中为那个区域设置合适的参数，当那个内存区域越大,就能够保留更多的语句,因此被共享的可能性也就越大了.
当向ORACLE提交一个SQL语句,0RACLE会第一在这块内存中査找相同的语句.那个地点需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同（包括空格，换行等）.
共享的语句必须满足三个条件：
•字符级的比较：
当前被执行的语句和共享池中的语句必须完全相同•例如：
SELECT*FROMEMP;
和下列每一个都不同
SELECT*fromEMP;
Select*FromEmp;
SELECT*FROMEMP;
•两个语句所指的对象必须完全相同：
例如：
用户对象名如何访咨询
Jacksaljimitprivatesynonym
Work_citypublicsynonym
Plant_detailpublicsynonym
Jillsaljimitprivatesynonym
Work_citypublicsynonym
Plant_detailtableowner
下列SQL语句不能在这两个用户之间共享.
selectmax（sal_cap）fromsaljimit;
缘故
每个用户都有一个privatesynonym-saljimit,它们是不同的对象下列SQL语句能在这两个用户之间共享.
selectcount（*）fromwork_citywheresdesclike"NEW%';
缘故：
两个用戸访咨询相同的对象publicsynonym-work_city
下列SQL语句不能在这两个用户之间共享.
selecta.sdesc9b.locationfromwork_citya，plant.detailbwherea.city_id=b.cityid
缘故:
用户jack通过privatesynonym访咨询plant_detail而jill是表的所有者，对象不同.
•两个SQL语句中必须使用相同的名字的绑左变量（bindvariables）
1・
selectpin,namefrompeoplewherepin=:
blkl.pin;
selectpin,namefrompeoplewherepin=:
blkl.pin;
2.
selectpin,namefrompeoplewherepin=:
blkl.ot」nd;
selectpin,namefrompeoplewherepin=:
blkl.ov_ind;
5选择最有效率的表名顺序
例如：
表TAB116384条记录，表TAB21条记录
选择TAB2作为基础表（最好的方法）
selectcount（*）fromtabl9tab2
选择TAB2作为基础表（不佳的方法）
selectcount（*）fromtab2,tabl
假如有3个以上的表连接查询，那就需要选择交叉表（intersectiontabic）作为基础表,交叉表是指那个被其他表所引用的表.
例如：
EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT*FROMLOCATIONL,CATEGORYC,EMPEWHEREE.EMP.NO
BETWEEN1000AND2000ANDE.CAT_NO=C.CAT_NOANDE.LOCN=L.LOCN
将比下列SQL更有效率
SELECT*FROMEMPE,LOCATIONL，CATEGORYCWHEREE-CAT.NO=C.CAT.NOANDE.LOCN=L.LOCNANDE.EMP.NOBETWEEN1000AND2000
6WHERE子句中的连接顺序.
ORACLE采纳自下而上的顺序解析WHERE子句,依照那个原理，表之间的连接必须写在其他WHERE条件之前，那些能够过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如：
（低效）
SELECT...FROMEMPEWHERESAL>50000ANDJOB='MANAGER'AND
25<（SELECTCOUNT（*）FROMEMPWHEREMGR=E.EMPNO）;
（髙效）
SELECT...FROMEMPEWHERE25<（SELECTCOUNT（*）FROMEMP
WHEREMGR=E.EMPNO）ANDSAL>50000ANDJOB—MANAGER';
7SELECT子句中幸免使用计
当在SELECT子句中列岀所有的COLUMN时，使用动态SQL列引用'*'是一个方便的方法.但是，这是一个专门低效的方法.实际上QRACLE在解析的过程中，会将’*'依次转换成所有的列名，那个工作是通过査询数据字典完成的，这意味着将耗费更多的时刻.
8减少访咨询数据库的次数
当执行每条SQL语句时‘ORACLE在内部执行了许多工作：
解析SQL语句，估算索引的利用率，绑立变疑，读数据块等等.由此可见，减少访咨询数据库的次数，就能实际上减少ORACLE的工作量.
例如，
以下有三种方法能够检索出雇员号等于0342或0291的职员.
方法1（最低效）
SELECTEMP.NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=342;
SELECTEMP_NAME,SALARY,GRADEFROMEMPWHEREEMP_NO=291;
方法2（次低效）
DECLARE
CURSORCl（E_NONUMBER）IS
SELECTEMP.NAME^ALARY,GRADEFROMEMPWHEREEMP_NO=E_NO;
BEGIN
OPENC1（342）;
FETCHClINTO;
•••
OPENC1（291）;
FETCHClINTO…,・・,・・；
CLOSECl;
END;
方法3（高效）
SELECTA.EMP_NAME,A.SALARY,A.GRADE,B.EMP_NAME,B.SALARY,B.GRADEFROMEMPA,EMPBWHEREA・E\IP_NO=342ANDB.EMP.NO=291;
9使用DECODE函数来减少处理时刻
使用DECODE函数能够幸免重复扫描相同记录或重复连接相同的表.
例如：
SELECTCOUNT（*）,SUM（SAL）FROMEMPWHEREDEPT_NO=0020ANDENAMELIKE“SMITH%，;
SELECTCOUNT（*）,SUM（SAL）FROMEMPWHEREDEPT_NO=0030ANDENAMELIKE“SMITH%';
你能够用DECODE函数髙效地得到相同结果
SELECTCOUNT（DECODE（DEPT_NO50020；X\NULL））D0020_COUNT,COUNT（DECODE（DEPT_NO,003（VX'$ULL））D0030_COUNT,
SUM（DECODE（DEPT_NO,0020,SAL,NULL））D0020_SAL,
SUM（DECODE（DEPT_NO,0030,SAL,NULL））D0030_SALFROMEMPWHEREENAMELIKE^SMITH%5;
类似的.DECODE函数也能够运用于GROUPBY和ORDERBY子句中.
10整合简单，无关联的数据库访咨询
假如有几个简单的数据库査询语句，能够把它们整合到一个查询中（即使它们之间没有关系）
例如：
SELECTNAMEFROMEMPWHEREEMP_NO=1234;
SELECTNAMEFROMDPTWHEREDPT_NO=10;
SELECTNAMEFROMCATWHERECAT_TYPE=CRD?
;
上而的3个査询能够被合并成一个：
SELECTE.NAME,D.NAME,C.NAMEFROMCATC，DPTD,EMPE.DUALXWHERENATL（XDUMMY）=NM.（CX\E.RO^ID（+））ANDN'T^X^X.DUMMY）=NM（WA・ROWID（+））ANDNATL（XDUMMY）=N\TL（尽管采取这种方法，效率得到提髙,然而程序的可读性大大降低，因此依旧要权衡之间的利弊
11删除重复记录
最髙效的删除重复记录方法（因为使用了ROWID）
DELETEFROMEMPEWHEREE.ROWID>（SELECTMIN（X.ROWID）FROMEMPXWHEREX.EMP.NO=E.EMP_NO）;
12用TRUNCATE替代DELETE
当删除表中的记录时，在通常情形下，回滚段（rollbacksegments）用来存放能够被复原的信息.假如你没有COMMIT事务.ORACLE会将数据复原到删除之前的状态（准确地讲是复原到执行删除命令之前的状况）
而当运用TRUNCATE时，回滚段不再存放任何可被复原的信息•当命令运行后，数据不能被复原•因此专门少的资源被调用，执行时刻也会专门短.
（注意：
TRUNCATE只在删除全表适用.TRUNCATE是DDL不是DML）
13尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT.如此程序的性能得到提髙，需求也会因
为COMMIT所开释的资源而减少:
COMMIT所开释的资源：
•回滚段上用于复原数据的信息.
•被程序语句获得的锁
•redologbuffer中的空间
•ORACLE为治理上述3种资源中的内部花费
14运算记录条数
和一样的观点相反，count（*）比count（l）稍快，因此假如能够通过索引检索，对索引
列的计数仍旧是最快的.例如COUNT（EMPNO）
（并不十分准确，通过实际的测试，上述三种方法并没有显著的性能差不）
15用Where子句替换HAVING子句
幸免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤.那个处理需要排序，总计等操作.假如能通过WHERE子句限制记录的数目，那就能减少这方而的开销.
例如：
低效：
SELECTREGION,AVG（LOG_SIZE）FROMLOCATIONGROUPBYREGIONHAMNGREGIONREGIONhSYDNE、”ANDREGION!
=TERTH^
高效
SELECTREGION,AVG（LOG_SIZE）FROMLOCATIONWHEREREGIONREGION!
='SYDNEY‘ANDREGION!
=TERTH^GROUPBYREGION
（HAVING中的条件一样用于对一些集合函数的比较，如COUNT0等等.除此而外,
一样的条件应该写在WHERE子句中）
16减少对表的查询
在含有子查询的SQL语句中，要专门注意减少对表的查询.
例如：
低效
SELECTTAB.NAMEFROMTABLESWHERETAB.NAME=（SELECTTAB_NAMEFROMTAB.COLUMNSWHEREVERSION=604）ANDDB_VER=（SELECTDB_VERFROMTAB_COLUMNSWHEREVERSION=604）
高效
SELECTTAB.NAMEFROMTABLESWHERE（TAB_NAME,DB_VER）=（SELECTTAB_NAME,DB_VER）FROMTAB.COLUMNSWHEREVERSION=604）
Update多个Column例子：
低效：
UPDATEEMPSETEMP.CAT=（SELECTMAX（CATEGORY）FROMEMP.CATEGORIES）,SAL.RANGE=（SELECTMAX（SAL_RANGE）FROMEMP_CATEGORIES）WHEREEMP.DEPT=0020;
高效:
UPDATEEMPSET（EMP_CAT,SAL_RANGE）=（SELECTMAX（CATEGORY）,MAX（SAL_RANGE）FROMEMP.CATEGORIES）WHEREEMP_DEPT=0020;
17通过内部函数提高SQL效率.
SELECTH.EMPNO^.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT（*）
FROMHISTORY.TYPET.EMPE,EMP_HISTORYHWHEREH.EMPNO=E.EMPNOANDH.HIST_TYPE=T.HIST_TYPEGROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE^DESC;
通过调用下而的函数能够提髙效率.
FUNCTIONLOOKUP_HIST_TYPE（TYPINNUMBER）RETURNVARCHAR2
AS
TDESCVARCHAR2（30）;
CURSORClIS
SELECTTYPE.DESC
FROMHISTORY_TYPE
WHEREHIST_TYPE=TYP;
BEGIN
OPENCl;
FETCHClINTOTDESC;
CLOSECl;
RETURN（NVL（TDESC/?
，））;
END;
FUNCTIONLOOKUP_EMP（EMPINNUMBER）RETURNVARCHAR2
AS
ENAMEVARCHAR2（30）;
CURSORClIS
SELECTENAME
FROMEMP
WHEREEMPNO=EMP;
BEGIN
OPENCl;
FETCHClINTOENAME;
CLOSECl;
RETURN（NVL（ENAMEJ?
J）;
END;
SELECTH.EMPNO,LOOKUP_EMP（H.EMPNO）,
H.HIST_TYPE,LOOKUP_HIST_TYPE（H.HIST_TYPE）,COUNT（*）
FROMEMP_HISTORYH
GROUPBYH.EMPNO,H.HIST_TYPE;
18使用表的不名（Alias）
当在SQL语句中连接多个表时，请使用表的不划并把不名前缀于每个Column上.如此一来，就能够减少解析的时刻并减少那些由Column歧义引起的语法错误.
（Column歧义指的是由于SQL中不同的表具有相同的Column乳当SQL语句中显现那个Column时,SQL解析器无法判左那个Column的归属）
19用EXISTS替代IN
在许多基于基础表的査询中，为了满足一个条件，往往需要对另一个表进行联接•在这种情形下，使用EXISTS（或NOTEXISTS）通常将提髙查询的效率.
低效：
SELECT*FROMEMP（基础表）WHEREEMPNO>0ANDDEPTNOIN（SELECTDEPTNOFROMDEPTWHERELOC=高效:
SELECT*FROMEMP（基础表）WHEREEMPNO>0ANDEXISTS（SELECT
XFROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=*MELB）
20用NOTEXISTS替代NOTIN
在子査询中.NOTIN子句将执行一个内部的排序和合并.不管在哪种情形下.NOTIN差不多上最低效的（因为它对子査询中的表执行了一个全表遍历）.为了幸免使用NOTIN，我们能够把它改写成外连接（OuterJoins）或NOTEXISTS.
例如：
SELECT・・・FROMEMPWHEREDEPT_NONOTIN（SELECTDEPT_NOFROMDEPTHUEREDEPT_CAT=W）;
为了提髙效率.改写为：
（方法一：
高效）
SELECT....FROMEMPA,DEPTBWHEREA.DEPT_NO=B.DEPT（+）AND
B・DEPT_NOISNULLANDBDEPT_CAT什）
（方法二：
最高效）
SELECT....FROMEMPEWHERENOTEXISTS（SELECTXFROMDEPTD
WHERED・DEPT_NO=EDEPT_NOANDDEPT_CAT—A'）;
21识不低效执行的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;
（尽管目前各种关于SQL优化的图形化工具层出不穷，然而写出自己的SQL工具来解决咨询题始终是一个最好的方法）
22使用TKPROF工具来查询SQL性能状态
SQLtrace工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中.那个跟踪文件提供了许多有用的信息，例如解析次数•执行次数,CPU使用时刻等.这些数据将能够用来优化系统.
设置SQLTRACE在会话级不：
有效
ALTERSESSIONSETSQL.TRACETRUE
设置SQLTRACE在整个数据库有效仿，必须将SQL_TRACE参数在init.ora中设
为TRUE,USER_DUMP_DEST参数讲明了生成跟踪文件的名目
（设置SQLTRACE第一要在init.ora中设立TIMED_STATISTICS,如此才能得到那些重要的时刻状态.生成的trace文件是不可读的，因此要用TKPROF工具对苴进行转换.TKPROF有许多执行参数•能够参考ORACLE手册来了解具体的配豐）
23用EXPLAINPLAN分析SQL语句
EXPLAINPLAN是一个专门好的分析SQL语句的工具,它甚至能够在不执行SQL的情形下分析语句.通过分析，我们就能够明白ORACLE是如何样连接表,使用什么方式扫描表（索引扫描或全表扫描）以及使用到的索引名称.
需要按照从里到外,从上到下的次序解读分析的结果.EXPLAINPLAN分析的结果是用缩进的格式排列的，最内部的操作将被最先解读，假如两个操作处于同一层中，带有最小操作号的将被第一执行.
（通过实践，感到依旧用SQLPLUS中的SETTRACE功能比较方便.）
举例：
SQL>list
1SELECT*
2FROMdept,emp
3*WHEREemp.deptno=dept.deptno
SQL>setautotracetraceonly/*traceonly能够不显示执行结果*/
SQL>/
升级会员 