ORACLE SQL性能优化规范.docx
- 文档编号:23316978
- 上传时间:2023-05-16
- 格式:DOCX
- 页数:16
- 大小:33.50KB
ORACLE SQL性能优化规范.docx
《ORACLE SQL性能优化规范.docx》由会员分享,可在线阅读,更多相关《ORACLE SQL性能优化规范.docx(16页珍藏版)》请在冰豆网上搜索。
ORACLESQL性能优化规范
ORACLESQL性能优化规范
目录
1.说明:
4
2.高效SQL的特点:
4
3.选用适合的ORACLE优化器4
4.对表或索引进行统计5
5.选择合适的多表关联方式:
6
1.HASHJOIN6
2.NESTEDLOOPJOIN7
3.SORTMERGEJOIN7
6.访问Table的方式8
7.HINT8
8.避免WHERE条件与索引字段类型不一致8
9.共享SQL语句9
10.选择最有效率的表名顺序(只在基于规则的优化器中有效)10
11.使用DECODE函数来减少处理时间11
12.删除重复记录11
13.用TRUNCATE替代DELETE12
14.尽量多使用COMMIT(针对OLTP性质系统)12
15.减少SQL语句中的表的关联数。
13
16.EXISTS与IN的区别14
17.避免在索引列上使用ISNULL和ISNOTNULL15
18.总是使用索引的第一个列15
19.用UNION-ALL替换UNION(不等价)16
1.说明:
本文档的目的是面向应用开发人员,为应用开发人员编写SQL提供帮助。
因此,其调整定位于SQL级。
对于整个性能调优而言,SQL级或应用级的调优是最经济最高效的。
相对于SQL级的调整,数据库或系统级的调整对性能的影响也十份重要。
如内存参数的调整、REDO日志的大小对检查点的影响等等。
该部分内容不在此表述。
本文档的前部分主要讲述优化SQL原则性、原理性内容,从第8节开始,主要描述常见的技巧。
因此,前部分需引起足够的重视。
2.高效SQL的特点:
“幸福的家庭都是相似的,不幸的家庭各有不同”。
引用到SQL编写:
“高效的SQL都是相似的,低效的SQL却各不相同”。
以下是高效SQL的通常特点:
⏹较少的物理I/O次数
⏹较少的内存GET次数
⏹使用高选择性的索引
⏹较少的排序操作
⏹避免在大表上做全表扫描
3.选用适合的ORACLE优化器
ORACLE的优化器共有3种:
⏹RULE(基于规则)
⏹COST(基于成本)
⏹CHOOSE(选择性)
设置缺省的优化器,可以通过对init.ora文件或spfile中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS.当然可在SQL语句级或是会话(session)级对其进行覆盖.
为了使用基于成本的优化器(CBO,Cost-BasedOptimizer),必须经常运行analyze命令或dbms_stats的包,以增加数据库中的对象统计信息(objectstatistics)的准确性.
如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过统计有关.如果table已经被统计过,优化器模式将自动成为CBO,反之,数据库将采用RULE形式的优化器.
在缺省情况下,ORACLE采用CHOOSE优化器。
注意:
1.各项目在做压力测试时的必须清楚数据库系统当前使用的是何种优化器选择方式,有哪些表上是具有统计信息等。
在上线后,需尽量与压力测试时保持一致,否则会带来不可预知的性能。
2.ORACLE在9i以后的版本中,已不提供基于规则的优化器。
因此,使用基于成本的优化器是一种必然。
3.建议新的项目使用基于成本的优化器!
!
4.对表或索引进行统计
ORACLE在9i中,提供两种方式对数据库对象进行统计:
⏹DBMS_STATS包
⏹ANALYZE
DBMS_STATS提供了全面的对数据对象的分析功能,能够使基于成本的优化器工作更更精确。
其提供对以下内容的统计:
对象类型
分析内容
表
记录数
数据块数
平均行的长度
列
列中不同值的数目
列中空值数
列中值的分步情况
索引
叶子占用数据块数
索引层数
系统统计
I/O性能使用率
CPU性能与使用率
该包提供以下过程:
过程
功能
举例
GATHER_INDEX_STATS
对索引的统计
dbms_stats.gather_table_stats('CC','EVENT_CDR')
该语句将对该表及所有字段及索引进行统计。
GATHER_TABLE_STATS
对表、索引、列的统计
GATHER_SCHEMA_STATS
对指定用户下的所有对象进行统计
GATHER_SYSTEM_STATS
统计CPU与I/O的性能
注意:
1.ORACLE公司强烈建议用户使用dbsm_stats包进行统计,其为这个包相比analyze,不仅提供列精确细致的统计内容,而且还能进行并行分析等。
2.在实际使用中,常常会觉得analyze比dbms_stats要快。
这是因为analyze分析的内容比较简单等原因,因而计算速度比较快。
3.ORACLE在以后的版本中,将仅提供对dbms_stats的功能。
4.并不需要每天或频繁地对数据对象进行统计,只有当数据分步或数据量发生比较大的变化时才需要。
5.选择合适的多表关联方式:
常见的表的关联方式有三种:
1.HASHJOIN
1.当需要进行多表关联操作时,选择使用全表扫描的方式在其中一个表的关联字段上创建HASH表(该表一般选择较小的表,以便可以存于SGA内存中,并达到提高探测的速度的目的),然后对另一表上进行全表扫描且以同样的算法构建HASH表,同时探测基于第一个表中上HASH表,查找匹配的键值。
2.使用场景:
大数据量等值关联查询,如关联查询大量用户资料及其清单数据
3.等值联接
2.NESTEDLOOPJOIN
1.优化器选择一个驱动表A作为循环扫描的外部表
2.另外一个相关联的表B作为内部表
3.每扫描驱动表A中一条记录,ORACLE扫描B表中所有满足关联条件的记录
selecta.cust_name,b.state
fromcc.custa,cc.prodb
wherea.id=b.idanda.id=:
ID
3.SORTMERGEJOIN
1.当需要进行多表关联操作时,对相关联的表按关联字段进行排序,然后从各已排序好的结果中取得关联结果
2.适用于非等值关联、数据量比较大的场合
3.在对大数据量的关联查询,且条件为非等值条件,此时性能要好于nestedloopjoin
4.在大多数情况下,HASHJOIN方式比该种关联方式高效
6.访问Table的方式
ORACLE采用两种访问表中记录的方式:
1.>全表扫描
全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描.
2.>通过ROWID访问表
可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息,ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
7.HINT
很多数据库系统提供一种手段,让用户来决定其SQL执行计划。
ORACLE使用HINT来实现这一目的。
基于ORACLE的执行计划是异常复杂、且跟实际的数据量及数据分步关系密切。
因此,并不建议由应用程序开发人员来使用HINT。
另外,当ORACLE使用基于成本的优化器,且对表进行了较为细致的分析,则ORACLE的优化器对执行计划的选择是非常优越的。
我们要对其有足够的信心!
另外,HINT提示不够或错误使用,将导致无法从应用以外的地方更改其执行计划,为SQL的优化带来被动。
8.避免WHERE条件与索引字段类型不一致
当where条件中,有索引前缀字段时,需注意其传入WHERE条件字段的类型与数据库中字段定义类型保持一致。
否则ORACLE使用不上索引。
参数类型与字段定义相符:
(高效,使用上索引)
SQL>selectcount(msrn)fromMOB_PPS_CDR_112wherebilling_nbr='951518515';
COUNT(MSRN)
-----------
0
Elapsed:
00:
00:
00.15
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=2Card=1Bytes=28)
10SORT(AGGREGATE)
21TABLEACCESS(BYINDEXROWID)OF'MOB_PPS_CDR_112'(Cost=2Card=358Bytes=10024)
32INDEX(RANGESCAN)OF'IDX_MPCDR_B_NBR_112'(NON-UNIQUE)(Cost=1Card=358)
参数类型与字段定义不相符:
(低高效,没使用上索引)
SQL>selectcount(msrn)fromMOB_PPS_CDR_112wherebilling_nbr=951518515;
COUNT(MSRN)
-----------
0
Elapsed:
00:
00:
06.50
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=12843Card=1Bytes=28)
10SORT(AGGREGATE)
21TABLEACCESS(FULL)OF'MOB_PPS_CDR_112'(Cost=12843Card=358Bytes=10024)
SQL>
9.共享SQL语句
为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(systemglobalarea)的共享池(sharedbufferpool)中的内存可以被所有的数据库用户共享.因此,当执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同,ORACLE就能很快获得已经被解析的语句以及最好的执行路径.ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cachebuffering),这个功能并不适用于多表连接查询.数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等).
共享的语句必须满足以下条件:
A.字符级的比较:
当前被执行的语句和共享池中的语句必须完全相同.
例如:
SELECT*FROMEMP;
和下列每一个都不同
SELECT*fromEMP;
Select*FromEmp;
SELECT*FROMEMP;
B.两个SQL语句中必须使用相同的名字的绑定变量(bindvariables)
例如:
第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
a.
selectpin,namefrompeoplewherepin=:
blk1.pin;
selectpin,namefrompeoplewherepin=:
blk1.pin;
b.
selectpin,namefrompeoplewherepin=:
blk1.ot_ind;
selectpin,namefrompeoplewherepin=:
blk1.ov_ind;
10.选择最有效率的表名顺序(只在基于规则的优化器中有效)
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表drivingtable)将被最先处理.在FROM子句中包含多个表的情况下,必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时,会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
表TAB1101000条记录
表TAB21条记录
选择TAB2作为基础表(最好的方法)
SQL>select/*+rule(tab1,tab2)*/count(*)fromtab1,tab2;
COUNT(*)
----------
101000
Elapsed:
00:
00:
00.04
选择TAB2作为基础表(不佳的方法)
SQL>select/*+rule(tab1,tab2)*/count(*)fromtab2,tab1;
COUNT(*)
----------
101000
Elapsed:
00:
00:
00.42
11.使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
SELECTCOUNT(*),SUM(SAL)
FROM EMP
WHEREDEPT_NO=0020
ANDENAMELIKE ‘SMITH%';
SELECTCOUNT(*),SUM(SAL)
FROM EMP
WHEREDEPT_NO=0030
ANDENAMELIKE ‘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
FROMEMP
WHEREENAMELIKE‘SMITH%';
类似的,DECODE函数也可以运用于GROUPBY和ORDERBY子句中.
12.删除重复记录
最高效的删除重复记录方法(因为使用了ROWID)
DELETEFROMEMPE
WHEREE.ROWID>
(SELECTMIN(X.ROWID)
FROMEMPX
WHEREX.EMP_NO=E.EMP_NO
);
13.用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)
而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.
注意:
TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML,因此,需注意事务完整性。
14.尽量多使用COMMIT(针对OLTP性质系统)
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
⏹回滚段上用于恢复数据的信息.
⏹被程序语句获得的锁
⏹redologbuffer中的空间
⏹ORACLE为管理上述3种资源中的内部花费
注意:
在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼。
15.减少SQL语句中的表的关联数。
在含有子查询的SQL语句中,要特别注意减少对表关联数.
当一个SQL语句中关联的表个数太多时,其执行计划会相关的复杂,导致执行计划难以理解,也会导致执行计划不稳定。
例如:
低效
SELECTH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM
HISTORY_TYPET,
EMPE,
EMP_HISTORYH
WHEREH.EMPNO=E.EMPNOAND
H.HIST_TYPE=T.HIST_TYPE
GROUPBYH.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
高效
FUNCTIONLOOKUP_HIST_TYPE(TYPINNUMBER)RETURNVARCHAR2AS
TDESCVARCHAR2(30);
CURSORC1IS
SELECTTYPE_DESCFROMHISTORY_TYPEWHEREHIST_TYPE=TYP;
BEGIN
OPENC1;
FETCHC1
INTOTDESC;
CLOSEC1;
RETURN(NVL(TDESC,'?
'));
END;
FUNCTIONLOOKUP_EMP(EMPINNUMBER)RETURNVARCHAR2AS
ENAMEVARCHAR2(30);
CURSORC1IS
SELECTENAMEFROMEMPWHEREEMPNO=EMP;
BEGIN
OPENC1;
FETCHC1
INTOENAME;
CLOSEC1;
RETURN(NVL(ENAME,'?
'));
END;
SELECTH.EMPNO,LOOKUP_EMP(H.EMPNO),H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROMEMP_HISTORYHGROUPBYH.EMPNO,H.HIST_TYPE;
注意:
当前公司比较特出的问题是部分开发人员比较喜欢写大而全的SQL,导致单个SQL中关联的表太多。
殊不知复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
16.EXISTS与IN的区别
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.
高效:
SQL>selectcount(*)fromMOB_PPS_CDR_112awherenotexists(
2select*fromtmp_nbrbwherea.billing_nbr=b.billing_nbr);
COUNT(*)
----------
8517819
Elapsed:
00:
00:
08.02
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=9970Card=1Bytes=28)
10SORT(AGGREGATE)
21HASHJOIN(ANTI)(Cost=9970Card=9495285Bytes=265867980)
32INDEX(FASTFULLSCAN)OF'IDX_MPCDR_B_NBR_112'(NON-UNIQUE)(Cost=4Card=9517838Bytes=104696218)
42TABLEACCESS(FULL)OF'TMP_NBR'(Cost=4Card=1089468Bytes=18520956)
底效:
(执行了2个多小时也没有执行完,被中断!
!
)
SQL>selectcount(*)fromMOB_PPS_CDR_112awherebilling_nbrnotin(
2selectbilling_nbrfromtmp_nbrb);
selectcount(*)fromMOB_PPS_CDR_112awherebilling_nbrnotin(
*
ERRORatline1:
ORA-01013:
userrequestedcancelofcurrentoperation
Elapsed:
02:
00:
43.41
SQL>
注意:
这两种方式的本质区别是因为使用in或notin时,基于rule的优化器趋向于使用nestedloop方式关联,而使用exists或notexists时,系统趋向于使用hashjoin方式关联。
但如果是基于COST的优化器时,且统计信息比较准确,则ORACLE会自动转换其执行计划,跟使用in或exists关系不大。
17.避免在索引列上使用ISNULL和ISNOTNULL
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引.对于单列索引,如果列包含空值,索引中将不存在此记录.对于复合索引,如果每个列都为空,索引中同样不存在此记录. 如果至少有一个列不为空,则记录存在于索引中.简单地讲,如果索引字段都为空,则索引中并不存在这条索引信息。
18.总是使用索引的第一个列
如果索引是建立在多个列上,只有在它的第一个列(leadingcolumn)被where子句引用时,优化器才会选择使用该索引.这也是一条简单而重要的规则.
19.用UNION-ALL替换UNION(不等价)
UNION操作将对各结果进行排序并合并,使结果集中没有重复的行.因此,如果用UNIONALL替代UNION,这样排序就不是必要了.效率就会因此得到提高.但请注意,这并不等价。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ORACLE SQL性能优化规范 SQL 性能 优化 规范