此页面上的内容需要较新版本的 Adobe Flash Player。

获取 Adobe Flash Player

    您的位置:首页 >> 企业博客
企业博客

Oracle SQL优化

  发表于2009-05-13 15:22     阅读:10124   评论:38  

 
 
ORACLE数据库调整优化可分为两大部分外部调整内部调整
外部调整一般是硬件环境的调整CPU资源的不足令查询变慢;Oralce的内存数量也会影响SQL的性能;网络中大量的Net8通信令SQL的性能变慢等。外部优化调整不再这里仔细阐述,重点描述内部调整中的Oracle SQL优化
Oracle SQL优化
SQL调优的目标是简单的:改善系统的性能
. 消除不必要的大表全表搜索:不必要的全表搜索导致大量不必要的I/O,从而拖慢整个数据库的性能。在一个有序的表中,如果查询返回少于40%的行,或者在一个无序的表中,返回少于7%的行,那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说,最常见的调优方法是增加索引。可以在表中加入标准的B树索引,也可以加入bitmap和基于函数的索引。要决定是否消除一个全表搜索,可以仔细检查索引搜索的I/O开销和全表搜索的开销,它们的开销和数据块的读取和可能的并行执行有关,并将两者作对比。在一些情况下,一些不必要的全表搜索的消除可以通过强制使用一个index来达到,只需要在SQL语句中加入一个索引的提示就可以了。
. 在全表搜索是一个最快的访问方法时,将小表的全表搜索放到KEEP池中缓冲,你可以使用alter table xxx cache语句,在Oracle8或以上,小表可以被强制为放到KEEP池中缓冲。
. 确保最优的索引使用 :对于改善查询的速度,这是特别重要的。有时Oracle可以选择多个索引来进行查询,必须检查每个索引并且确保Oracle使用正确的索引。它还包括bitmap和基于函数的索引的使用。
. 确保最优的JOIN操作:有些查询使用NESTED LOOP join快一些,有些则是HASH join快一些,另外一些则是sort-merge join更快。
这些规则看来简单,不过它们占SQL调优任务的90%,并且它们也无需完全懂得Oracle SQL的内部运作。简单概览以下Oracle SQL的优化。
调整Oracle的排序操作:排序是SQL语法中一个小的方面,但很重要,在Oracle的调整中,它常常被忽略。当使用create index、ORDER BY或者GROUP BY的语句时,Oracle数据库将会自动执行排序的操作。通常,在以下的情况下Oracle会进行排序的操作:
  使用Order by的SQL语句
  使用Group by的SQL语句
  在创建索引的时候
进行table join时,由于现有索引的不足而导致SQL优化器调用MERGE SORT
当与Oracle建立起一个session时,在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接(dedicated connection),那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个Program Global Area (PGA) 。如果连接是通过多线程服务器建立的,那么排序的空间就在large_pool中分配。对于所有的session,用做排序的内存量都必须是一样的,不能为需要更大排序的操作分配额外的排序区域。因此,必须作出一个平衡,在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序(disk sorts)的同时,对于那些并不需要进行很大排序的任务,就会出现一些浪费。当然,当排序的空间需求超出了sort_area_size的大小时,这时将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14,000倍。
私有排序区域的大小是有init.ora中的sort_area_size参数决定的。每个排序所占用的大小由init.ora中的sort_area_retained_size参数决定。当排序不能在分配的空间中完成时,就会使用磁盘排序的方式,即在Oracle实例中的临时表空间中进行。
磁盘排序的开销是很大的,有几个方面的原因。首先,和内存排序相比较,它们特别慢;而且磁盘排序会消耗临时表空间中的资源。Oracle还必须分配缓冲池块来保持临时表空间中的块。无论什么时候,内存排序都比磁盘排序好,磁盘排序将会令任务变慢,并且会影响Oracle实例的当前任务的执行。还有,过多的磁盘排序将会令free buffer waits的值变高,从而令其它任务的数据块由缓冲中移走。
调整Oracle的竞争: Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理,这样就可以无需懂得Oracle的表和索引的内部运作。不过,但最好需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系统来说,这是非常重要的。
要精通对象的调整,你需要懂得freelists和freelist组的行为,它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划(ERP)的应用是特别重要的,因为在这些应用中,不正确的表设置通常是DML语句执行慢的原因。
对于初学者来说,最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题,否则在设置表的pctfree和pctused参数时,就必须考虑平均的行长和数据库的块大小,这样空的块才会被有效地放到freelists中。当这些设置不正确时,那些得到的freelists也是"dead"块,因为它们没有足够的空间来存储一行,这样将会导致明显的处理延迟。
Freelists对于有效地重新使用Oracle表空间中的空间是很重要的,它和pctfree及pctused这两个存储参数的设置直接相关。pctused设置为一个高的值,这时数据库就会尽快地重新使用块。不过,高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时,需要认真考虑究竟需要高性能还是有效的空间重用,并且据此来设置表的参数。以下来看一下这些freelists是如何影响Oracle的性能的。
当有一个请求需要插入一行到表格中时,Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。freelist串是放在表格或者索引的第一个块中,这个块也被称为段头(segment header)。pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelist link和 unlink是简单的Oracle功能,不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle的性能确实有影响。
pctfree参数是控制freelist un-links的(即将块由freelists中移除)。设置pctfree=10 意味着每个块都保留10%的空间用作行扩展。pctused参数是控制freelist re-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。
一个块重新回到freelists后的处理:一旦由于一个删除的操作而令块被重新加入到freelist中,它将会一直保留在freelist中即使空间的使用超过了60%,只有在到达pctfree时才会将数据块由freelist中移走。
表格和索引存储参数设置的要求总结:以下的一些规则是用来设置freelists, freelist groups, pctfree和pctused存储参数的。pctused和pctfree的值是可以很容易地通过alter table命令修改的。
有效地使用空间和高性能之间是有矛盾的,而表格的存储参数就是控制这个方面的矛盾:
. 对于需要有效地重新使用空间,可以设置一个高的pctused值,不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因此,这些块在再次满之前只可以接受几行记录,从而导致更多的I/O。
. 追求高性能的话,可以将pctused设置为一个低的值,这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行,因此可以减少插入操作的I/O。Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说,扩展一个表比管理freelists消耗更少的资源。
设置对象存储参数的一些常见规则:
.经常将pctused设置为可以接收一条新行。对于不能接受一行的free blocks对于我们来说是没有用的。如果这样做,将会令Oracle的性能变慢,因为Oracle将在扩展表来得到一个空的块之前,企图读取5个"dead"的free block。
.表格中chained rows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下,RAW和LONG RAW列都很巨大,以至超过了Oracle的最大块的大小,这时chained rows是不可以避免的。
.如果一个表有同时插入的SQL语句,那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中,而没有其它包含有任何空闲块的freelists出现。
.freelist参数应该设置为表格同时更新的最大值。例如,如果在任何时候,某个表最多有20个用户执行插入的操作,那么该表的参数应该设置为freelists=20。
freelist groups参数的值只是对于Oracle Parallel Server和Real Application Clusters才是有用的。对于这类Oracle,freelist groups应该设置为访问该表格的Oracle Parallel Server实例的数目。
补充
在使用SQL时有时候会陷入一个误区 , 即太关注于SQL得到的结果是否正确 , 而忽略了不同的实现方法之间的可能存在的性能差异 , 特别是较复杂的报表系统中用到的Select 命令或大型的复杂的数据库环境中(如联机事务处理OLTP)表现得尤为明显 , 不良的SQL Command往往来自于不恰当的索引设计 , 不充分的连接条件和不可优化的where子句 . 在对它们进行适当的优化后 , 其运行速度有了明显的提高 !
下面是总结及摘录的一些优化原则 :
不合理的索引设计:
例子 : 表record中有记录620000行 , 试看在不同的索引下 , 下面几个SQL的运行情况 :
1.在字段date上建有一非个群集索引
Select count(*) from record where date >'19991201' and date < '19991214'and amount >2000 (25秒)
Select date , sum(amount) from record group by date (55秒)
Select count(*) from record where date > '19990901' and place in ('BJ','SH') (27秒)
分析:Record上有大量的重复值,在非群集索引下,数据在物理上随机存放在数据页上,在范围查找时,必须执行一次表扫描才能找到这一范围内的全部行。
2.在字段date上建一个群集索引
Select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(14秒)
Select date , sum(amount) from record group by date (28秒)
Select count(*) from record where date >'19990901' and place in ('BJ','SH')(14秒)
分析:在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。
3.在place,date,amount上的组合索引
Select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 (26秒)
select date,sum(amount) from record group by date(27秒)
select count(*) from record where date >'19990901' and place in ('BJ, 'SH')(< 1秒)
分析:这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的
4.在date,place,amount上的组合索引
select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)
select date , sum(amount) from record group by date(11秒)
select count(*) from record where date >'19990901' and place in ('BJ','SH')(< 1秒)
分析:这是一个合理的组合索引。它将date作为前导列,使每个SQL都可以利用索引,并且在第一和第三个SQL中形成了索引覆盖,因而性能达到了最优。
5.总结:
缺省情况下建立的索引是非群集索引,但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。一般来说:
①.有大量重复值、且经常有范围查询(between, >,< ,>=,< =)和order by、group by发生的列,可考虑建立群集索引;
②.经常同时存取多列,且每列都含有重复值可考虑建立组合索引;
③.组合索引要尽量使关键查询形成索引覆盖,其前导列一定是使用最频繁列。
 
不充分的连接条件:
例子:表card有7896行,在card_no上有一个非聚集索引,表account有191122行,在 account_no上有一个非聚集索引,试看在不同的表连接条件下,两个SQL的执行情况:
select sum(a.amount) from account a, card b where a.card_no = b.card_no(20秒)
将SQL改为:select sum(a.amount) from account a, card b where a.card_no = b.card_no and a.account_no=b.account_no(< 1秒)
分析:----在第一个连接条件下,最佳查询方案是将account作外层表,card作内层表,利用card上的索引,其I/O次数可由以下公式估算为:
---- 外层表account上的22541页+(外层表account的191122行*内层表card上对应外层表第一行所要查找的3页)=595907次I/O
---- 在第二个连接条件下,最佳查询方案是将card作外层表,account作内层表,利用account上的索引,其I/O次数可由以下公式估算为:
---- 外层表card上的1944页+(外层表card的7896行*内层表account上对应外层表每一行所要查找的4页)= 33528次I/O
---- 可见,只有充份的连接条件,真正的最佳方案才会被执行。
总结:
1.多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表;内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。
2. 查看执行方案的方法-- 用set showplanon,打开showplan选项,就可以看到连接顺序、使用何种索引的信息;想看更详细的信息,需用sa角色执行dbcc(3604,310,302)。
 
不可优化的where子句
1.例:下列SQL条件语句中的列都建有恰当的索引,但执行速度却非常慢:
Select * from record where substring(card_no,1,4)='5378'(13秒)
select * from record where amount/30< 1000(11秒)
select * from record where convert(char(10),date,112)='19991201'(10秒)
分析:where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:
select * from record where card_no like'5378%' (< 1秒)
select * from record where amount< 1000*30 (< 1秒)
select * from record where date= '1999/12/01' (< 1秒)
你会发现SQL明显快起来!
2.例:表stuff有200000行,id_no上有非群集索引,请看下面这个SQL:
select count(*) from stuff where id_no in('0','1') (23秒)
分析:where条件中的'in'在逻辑上相当于'or',所以语法分析器会将in ('0','1')转化为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用id_no上的索引;但实际上(根据showplan),它却采用了"OR策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据库性能的影响。
实践证明, 表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到220秒!还不如将or子句分开:
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3 秒,在620000行下,时间也只有4秒。或者,用更好的方法,写一个简单的存储过程直接算出结果,执行时间同上面一样快!
总结:
可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。
---- 1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。
---- 2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。
---- 3.要善于使用存储过程,它使SQL变得更加灵活和高效。
---- 4.Where子句中尽量不要使用is null或is not null的语句,不会使用索引 where子句 尽量不要将通配符(%)放在搜寻词首出现,通配符(%)在搜寻词首出现不会使用索引 where子句尽量少使用not或是<>,应该成< or >来实行 用exists代替in order by 子句中尽量不要使用非索引列 Where子句中不要使用列联接,如:name||‘‘||last_name 通过分析SQL语句执行计划来优化语句 多表连接各条件结果集大小不同,有时用子查询会比表联接有效
---- 从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
以下是总结SFC系统中的SQL优化原则 :
1. 禁止不必要的全表扫描及不必要的查询条件 , 如Select serial_number , mo_number from sfism4.r_wip_tracking_t Where model_name like ‘%’ 与 Select serial_number , mo_number from sfism4.r_wip_tracking_t 语句有同样的结果 , 但是运行的速度后者要快10倍左右(当数据较多时较明显) .
2. 多表的查询,数据量小的表放在前面可以提高速度
例如:select count(*) from r_repair_t a ,r_sn_detail_t b
where a.serial_number=b.serial_number 
这样的统计速度要比 select count(*) from r_sn_detail_t b,r_repair_t a 
where a.serial_number=b.serial_number的统计速的高
3. 多个条件,查出范围小的条件应该紧接在where 的后面.
例如:    
where in_station_time>=sysdate-20 and in_station_time>=sysdate-1
where in_station_time>=sysdate-1 and in_station_time>=sysdate-20
这两条语句查处的统计数量是一样的,但是后面的语句的速度却大大的提高了速度。
4 .在按时闲查询统计的时候,尽量不要使用to_char条件统计,而是使用to_date条件统计
例如:
select * from r_wip_tracking_t
where to_char(‘yyyyymmddhh24’,’in_station_time’) >=’ 20020101000000’
该为in_station_time>=to_date(‘20020101000000’,’yyyymmddhh24miss’)
这样的查询条件利用到in_station_time字段元的索引,明显的提高统计速度,这样的语句有大量的使用,严重影响了效能。
以下是摘录的SQL语句书写的优化原则(供参考) :
1 避免无计划的全表扫描
如下情况进行全表扫描:
- 该表无索引
- 对返回的行无人和限制条件(无Where子句)
- 对于索引主列(索引的第一列)无限制条件
- 对索引主列的条件含在表达式中
- 对索引主列的限制条件是is (not) null或!=
对索引主列的限制条件是like操作且值是一个bind variable或%打头的值
2 只使用选择性索引
索引的选择性是指索引列中不同值得数目和标志中记录数的比,选择性最好的是非空列的唯一索引1.0。
复合索引中列的次序的问题:
1 在限定条件里最频繁使用的列应该是主列
2 最具有选择性的列(即最清晰的列)应该是主列
如果1和2 不一致,可以考虑建立多个索引。
在复合索引和多个单个索引中作选择:
考虑选择性 考虑读取索引的次数 考虑AND-EQUAL操作
3 管理多表连接(Nested Loops, Merge Joins和Hash Joins)优化联接操作
Merge Joins是集合操作 Nested Loops和Hash Joins是记录操作返回第一批记录迅速
Merge Joins的操作适用于批处理操作,巨大表和远程查询
1全表扫描 --〉 2排序 --〉3比较和合并 性能开销主要在前两步
适用全表扫描的情形,都适用Merge Joins操作(比Nested Loops有效)。
改善1的效率: 优化I/O,提高使用ORACLE多块读的能力, 使用并行查询的选项
改善1的效率:提高Sort_Area_Size的值,使用Sort Direct Writes,为临时段提供专用表空间
4 管理包含视图的SQL语句
优化器执行包含视图的SQL语句有两种方法:
- 先执行视图,完成全部的结果集,然后用其余的查询条件作过滤器执行查询
- 将视图文本集成到查询里去
含有group by子句的视图不能被集成到一个大的查询中去。
在视图中使用union,不阻止视图的SQL集成到查询的语法中去。
5 优化子查询
6 使用复合Keys/Star查询
7 恰当地索引Connect By操作
8 限制对远程表的访问
9 管理非常巨大的表的访问
- 管理数据接近(proximity) 记录在表中的存放按对表的范围扫描中最长使用的列排序 按次序存储数据有助于范围扫描,尤其是对大表。
- 避免没有帮助的索引扫描 当返回的数据集合较大时,使用索引对SGA的数据块缓存占用较大,影响其它用户;全表扫描还能从ORACLE的多块读取机制和“一致性获取/每块”特性中受益。
- 创建充分索引的表 使访问索引能够读取较全面的数据建立仅主列不同的多个索引
- 创建hash簇
- 创建分割表和视图
- 使用并行选项
10 使用Union All 而不是Union
UNION ALL操作不包括Sort Unique操作,第一行检索的响应速度快,多数情况下不用临时段完成操作,
UNION ALL建立的视图用在查询里可以集成到查询的语法中去,提高效率
11 避免在SQL里使用PL/SQL功能调用
12 绑定变量(Bind Variable)的使用管理
使用Bind Variable和Execute using方式
将like :name ||’%’ 改写成 between :name and :name || char(225), 已避免进行全表扫描,而是使用索引。
13 回访优化进程
数据变化后,重新考察优化情况
 
 
 
                                                                              谭剑波
                                                                         2009-4-29

文章评论 (38)

发表评论

姓名:  

输入验证码:   如果看不清楚验证码,可以点击更换新的验证码。